Connect to Microsoft SQL Server On Ubuntu Linux with Windows Authentication (for pyodbc)

How to connect to Microsoft SQL Server from a Ubuntu Linux environment using the Microsoft supplied ODBC Driver for SQL Server using Windows Authentication.

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

When 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.

[libdefaults]
default_realm = TECH.LOCAL

[realms]
TECH.LOCAL = {
	kdc = TECH-DC01.TECH.LOCAL
	admin_server = TECH-DC01.TECH.LOCAL
}
[domain_realm]
	.tech.local = TECH.LOCAL

/etc/krb5.conf

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 username@domain.net would not work if your configuration was setup to use DOMAIN.NET.

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.

odbcinst -q -d

Setup a DSN (optional)

Add an entry similar to below in your user ODBC file at ~/.odbc.ini

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:

[08001][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746
[08001][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 /etc/ssl/openssl.cnf

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

Refer https://askubuntu.com/questions/1233186/ubuntu-20-04-how-to-set-lower-ssl-security-level

Troubleshooting Errors

If during pip install pyodbc you get an error like this:

error: command 'x86_64-linux-gnu-gcc' failed with exit status 1

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

Appendix

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.

cat /etc/odbcinst.ini

In the example above, you can see the driver version I have is 17.7.