Connecting to MS SQL Server using python on linux with 'Windows Credentials'

python sql-server pyodbc freetds unixodbc
Samer Atiani picture Samer Atiani · Feb 12, 2009 · Viewed 21.6k times · Source

Is there any way to connect to an MS SQL Server database with python on linux using Windows Domain Credentials?

I can connect perfectly fine from my windows machine using Windows Credentials, but attempting to do the same from a linux python with pyodbs + freetds + unixodbc 

>>import pyodbc
>>conn = pyodbc.connect("DRIVER={FreeTDS};SERVER=servername;UID=username;PWD=password;DATABASE=dbname")

results in this error:

class 'pyodbc.Error'>: ('28000', '[28000] [unixODBC][FreeTDS][SQL Server]Login incorrect. (20014) (SQLDriverConnectW)')

I'm sure the password is written correctly, but I've tried many different combinations of username:

DOMAIN\username
DOMAIN\\username

or even

UID=username;DOMAIN=domain

to no avail. Any ideas?

Answer

steamer25 picture steamer25 · Mar 21, 2013

As of at least March 2013, this seems to work out of the box with FreeTDS. I specified the TDS protocol version for good measure--not sure if that makes the difference:

connStr = "DRIVER={{FreeTDS}};SERVER={0};PORT=1433;TDS_Version=7.2;UID={1}\\{2};PWD={3}".format(hostname, active_directory_domain, username, password)

Integrated authentication also appears to be supported in Microsoft's official driver for linux: http://msdn.microsoft.com/en-us/library/hh568450.aspx . I'm not sure how many Linux distributions it actually works on or how much of the source is available. They explicitly mention RHEL 5 and 6 and some dependencies on the download page.

Related questions

SqlAlchemy equivalent of pyodbc connect string using FreeTDS

The following works: import pyodbc pyodbc.connect('DRIVER={FreeTDS};Server=my.db.server;Database=mydb;UID=myuser;PWD=mypwd;TDS_Version=8.0;Port=1433;') The following fails: import sqlalchemy sqlalchemy.create_engine("mssql://myuser:[email protected]:1433/mydb?driver=FreeTDS&…

Read More
"Invalid parameter type" (numpy.int64) when inserting rows with executemany()

I try to insert bunch of data to database insert_list = [(1,1,1,1,1,1),(2,2,2,2,2,2),(3,3,3,3,3,3),....] #up to 10000 tuples in this list conn = pyodbc.connect('DRIVER={FreeTDS};SERVER=xxxxx;DATABASE=xxxx;UID=xx;PWD=xx;TDS_Version=7.0') cursor = conn.cursor() sql = "insert into ScanEMAxEMAHistoryDay(…

Read More
Retrieving Data from SQL Using pyodbc

I am trying to retrieve data from an SQL server using pyodbc and print it in a table using Python. However, I can only seem to retrieve the column name and the data type and stuff like that, not the …

Read More