Authenticate from Linux to Windows SQL Server with pyodbc

Question

I am trying to connect from a linux machine to a windows SQL Server with pyodbc.

I do have a couple of constraints:

• Need to log on with a windows domain account
• Need to use python3
• Need to do it from Linux to Windows
• Need to connect to a specific instance

I set up the environment as described by microsoft and have it working (I can import pyodbc and use the configured mussel driver).

I am not familiar with Windows domain authentication and what not, so there is where my problem is.

My connection string:

DRIVER={ODBC Driver 17 for SQL Server};SERVER=myserver.mydomain.com;PORT=1433;DATABASE=MyDatabase;Domain=MyCompanyDomain;Instance=MyInstance;UID=myDomainUser;PWD=XXXXXXXX;Trusted_Connection=yes;Integrated_Security=SSPI

Supposedly one should use "Trusted_Connection" to use the Windows domain authentication instead of directly authenticating with the SQL server.

The error I get when running pyodbc.connect(connString):

pyodbc.Error: ('HY000', '[HY000] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (851968) (SQLDriverConnect)')

From other sources I read this should work on Windows as this code would use the credentials of the currently logged in user.

My question is how can I connect to a Windows SQL Server instance from Linux using Windows Domain credentials.

Answer 1

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.

Answer 2

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