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 auth
import sys
import re
import pyodbc

driver='{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-ver15

try:
    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 query
cursor.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 tickets
kdestroy: No credentials cache found while destroying cache

user@localhost:~# python pyodbc_sql_server_test.py tcp:dbserver.example.com mydatabase
You must login using kinit before using this script.

user@localhost:~# kinit
Password for [email protected]:

user@localhost:~# python pyodbc_sql_server_test.py tcp:dbserver.example.com mydatabase
Microsoft 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: )

success

user@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..