Authenticate from Linux to Windows SQL Server with pyodbc

Esser420 picture Esser420 · Apr 6, 2018 · Viewed 14.9k times · Source

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

Esser420 picture Esser420 · Apr 13, 2018

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