Authenticate from Linux to Windows SQL Server with pyodbc

You must obtain a Kerberos ticket for this to work. Your example doesn't specify whether your Linux system is set up to authenticate via Kerberos or whether you have previously obtained a Kerberos ticket before your code hits your connection string.

If your Linux system is set up to authenticate via Kerberos, then as a proof of concept you can obtain a Kerberos ticket using kinit from the command line. Here's what works for me in python3 running in Ubuntu on Windows via the WSL. The python code:

#!/usr/bin/env python# minimal example using Kerberos authimport sysimport reimport pyodbcdriver='{ODBC Driver 17 for SQL Server}'server = sys.argv[1]database = sys.argv[2]# trusted_connection uses kerberos ticket and ignores UID and PASSWORD in connection string# https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication?view=sql-server-ver15try:    cnxn = pyodbc.connect(driver=driver, server=server, database=database, trusted_connection='yes')    cursor = cnxn.cursor()except pyodbc.Error as ex:    msg = ex.args[1]    if re.search('No Kerberos', msg):        print('You must login using kinit before using this script.')        exit(1)    else:        raise# Sample select querycursor.execute("SELECT @@version;")row = cursor.fetchone()while row:    print(row[0])    row = cursor.fetchone()print('success')

This tells you if you don't have a ticket. Since it uses a ticket you don't have to specify a user or password in the script. It will ignore both.

Now we run it:

user@localhost:~# kdestroy # make sure there are no active ticketskdestroy: No credentials cache found while destroying cacheuser@localhost:~# python pyodbc_sql_server_test.py tcp:dbserver.example.com mydatabaseYou must login using kinit before using this script.user@localhost:~# kinitPassword for user@DOMAIN.LOCAL:user@localhost:~# python pyodbc_sql_server_test.py tcp:dbserver.example.com mydatabaseMicrosoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64)        Jun 15 2019 23:15:58        Copyright (c) Microsoft Corporation        Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: )successuser@localhost:~#

You may also have success obtaining a Kerberos ticket from python code that runs before you make this connection but that is beyond the scope of this answer. A search for python Kerberos modules might point you toward a solution.

It also appears possible to set up the Linux system so that as soon as a user logs in it automatically obtains a Kerberos ticket that can be passed to other processes. That is also outside of the scope of this answer but a search for automatic Kerberos ticket upon Linux login may yield some clues.

I ended up using the pymssql library which basically is pyodbc on top of the FreeTDS driver. It worked out of the box.

Weird how I had such a hard time discovering this library..

I find two ways for same task. I have MSSQL server with AD auth.

You can use JVM.Load and install JAVA https://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html. Also install JPype1 version 0.6.3 pip install JPype==0.6.3. Version above 0.6.3 won't work correct

import jaydebeapiimport pandas as pddriver_name = "net.sourceforge.jtds.jdbc.Driver"connection_url="jdbc:jtds:sqlserver://<server>:<port>/<database name>"connection_properties = {"domain": "<domain name>","user": "<username>","password": "<pwd>"}jar_path =  <path to jsds>"/jtds-1.3.1.jar"CONN = jaydebeapi.connect(driver_name, connection_url, connection_properties, jar_path)sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS"df = pd.read_sql(sql, CONN)

This version was too slow for me.

Also You can use pyodbc via FreeTDS. To create a FreeTDS connection Install FreeTDS on your Linux apt-get install tdsodbc freetds-bin, configure FreeTDS /etc/odbcinst.ini like this:

[FreeTDS]Description=FreeTDSDriver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.soSetup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

and turn it on odbcinst -i -d -f /etc/odbcinst.ini

After that, you can use pyodbc

import pandas as pdimport pyodbc    CONN =pyodbc.connect('DRIVER={FreeTDS};'                                  'Server=<server>;'                                  'Database=<database>;'                                  'UID=<domain name>\\<username>;'                                  'PWD=<password>;'                                  'TDS_Version=8.0;'                                  'Port=1433;')sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS"df = pd.read_sql(sql, CONN)

It's works much faster