This post documents the steps required to connect to Microsoft SQL Server from a Ubuntu Linux environment using the Microsoft supplied ODBC Driver for SQL Server using Windows Authentication.
The primary objective for me was to use
pyodbc but a simple
pip install pyodbc didn't work seamlessly for me. In addition I needed to authenticate using Windows credentials. There are other alternatives to use
pyodbc with MS SQL but the performance of those drivers was not optimal for me. Hence, I went through the trouble of using the Microsoft drivers. I also tried the FreeTDS driver which worked, but I didn't like the performance.
Install Driver and Linux Dependencies
The instructions on the Microsoft website are pretty straight forward, reproduced here:
sudo su curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - #Download appropriate package for the OS version #Choose only ONE of the following, corresponding to your OS version #Ubuntu 16.04 curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list #Ubuntu 18.04 curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list #Ubuntu 20.04 curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list #Ubuntu 20.10 curl https://packages.microsoft.com/config/ubuntu/20.10/prod.list > /etc/apt/sources.list.d/mssql-release.list exit sudo apt-get update sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17 # optional: for bcp and sqlcmd sudo ACCEPT_EULA=Y apt-get install -y mssql-tools echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc # optional: for unixODBC development headers sudo apt-get install -y unixodbc-dev
Install Linux Dependencies
These are required not only for UNIX ODBC but also for Kerberos client that is required to authenticate with Windows Active Directory.
sudo apt-get update sudo apt-get install unixodbc unixodbc-dev -y sudo apt-get install krb5-user -y #You may need the following before doing pip install pyodbc sudo apt-get install build-essential python3-dev python3-pip
Setup Kerberos Configuration
Determine your Kerberos/Active Directory authentication server.
You can type
nslookup in your PowerShell or Command Prompt to discover the default AD server name and IP.
Add these as an entry on your
/etc/hosts file via
sudo nano /etc/hosts
krb5-user was installed, it created a file
/etc/krb5.conf. Configure the name of your domain and the address for Kerberos server as depicted below. Replace with actual values for your environment.
With all this setup, you should be able to get a ticket for kerberos. Type in
kinit username@DOMAIN.NET replacing the username and DOMAIN.NET with your domain name. Also make sure you use the right case. This is case sensitive so
email@example.com would not work if your configuration was setup to use
Enter the password and you should be authenticated.
Validate that ODBC driver is installed
odbcinst -j shows your OBDC configuration data:
odbcinst -q -d will give you installed ODBC driver. Make sure the 'ODBC Driver 17 for SQL Server] or equivalent is installed.
Setup a DSN (optional)
Add an entry similar to below in your user ODBC file at
Next check that you are able to connect via SQL.
isql -v -k "DSN=MY_DSN_NAME"
Side note: Instead of defining the DSN in ~/.odbc.ini, you can also specify the entire connection string and pass it directly to -k parameter.
If you get the following error:
[Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection [ISQL]ERROR: Could not SQLDriverConnect
This could be due to TLS configuration. I resolved this by adding this to the beginning of your config file at
openssl_conf = default_conf
And then this to the end:
[ default_conf ] ssl_conf = ssl_sect [ssl_sect] system_default = system_default_sect [system_default_sect] MinProtocol = TLSv1.2 CipherString = DEFAULT:@SECLEVEL=1
pip install pyodbc you get an error like this:
Then, you're likely missing the build dependencies in Linux. Make sure you install this:
sudo apt-get install unixodbc unixodbc-dev -y
If the error persists, try installing these as well:
sudo apt-get install build-essential python3-dev python3-pip
How to check the version of an ODBC driver installed in Ubuntu Linux
The version of the ODBC driver installed on your Linux system is stored in the
/etc/odbcinst.ini file. You can simply inspect the file to learn the version number. You may need to identify the version number to check what features the specific version of the ODBC driver providers.
In the example above, you can see the driver version I have is 17.7.