Authenticate from Linux to Windows SQL Server with pyodbc
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
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..