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.
![](https://shuaib.org/content/images/2021/06/image-26.png)
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
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:
![](https://shuaib.org/content/images/2021/06/image-27.png)
odbcinst -q -d
will give you installed ODBC driver. Make sure the 'ODBC Driver 17 for SQL Server] or equivalent is installed.
![](https://shuaib.org/content/images/2021/06/image-28.png)
odbcinst -q -d
Setup a DSN (optional)
Add an entry similar to below in your user ODBC file at ~/.odbc.ini
![](https://shuaib.org/content/images/2021/06/image-29.png)
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
![](https://shuaib.org/content/images/2021/06/image-33.png)
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:
![](https://shuaib.org/content/images/2021/06/image-30.png)
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
![](https://shuaib.org/content/images/2021/06/image-35.png)
In the example above, you can see the driver version I have is 17.7.
References:
- https://github.com/mkleehammer/pyodbc/issues/610#issuecomment-686282639
- https://askubuntu.com/questions/1233186/ubuntu-20-04-how-to-set-lower-ssl-security-level
- https://jensd.be/255/linux/execute-queries-on-a-microsoft-sql-server-from-the-linux-cli-with-odbc-and-kerberos-authentication
- https://www.atlantic.net/dedicated-server-hosting/how-to-setup-kerberos-server-and-client-on-ubuntu-20-04/
- https://www.varonis.com/blog/kerberos-authentication-explained/
- https://techexpert.tips/ubuntu/ubuntu-kerberos-authentication-active-directory/
- https://help.ubuntu.com/community/Kerberos
- https://inbo.github.io/tutorials/installation/user/user_install_kerberos/
- http://www.itadmintools.com/2011/07/creating-kerberos-keytab-files.html