How to use Windows authentication to connect to MS SQL server from a Windows workstation in another domain using Python

Question

I am trying to connect to SQL server 2000 installed on Windows server 2003 from Windows Server 2008 R2 using the Python 3.4 module and pyodbc . These servers are in different AD domains. Windows only authentication is enabled on the SQL server and I cannot change it.

 drv = '{SQL server}' svr = 'sql.my-domain.local' usr = 'my-domain.local\testuser' pwd = 'password' db = 'testdb' pyodbc.connect(driver=drv, server=svr, user=usr, password=pwd, database=db) 

The above connection fails with the following error:

 pyodbc.Error: ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQLServer] Login failed for user 'svx-iroot.local\\sqlexecutive'. Reason: Not associated with a trusted SQL Server connection. (18452) (SQLDriverConnect)") 

There are several questions, for example this one , suggesting adding the trusted_connection='yes' argument in pyodbc to support Windows authentication, but in this case it does not help, because local credentials are used with this option, and I need to provide credentials explicitly, because that the source workstation is in a different AD domain.

Creating a User DSN in the ODBC Data Source Administrator with the SQL Server driver with the error above.

Is there any way to make this work?

Meanwhile, I installed the FreeTDS driver for Windows from http://sourceforge.net/projects/freetdswindows/ , and checking the connection using the tsql utility works:

 tsql -S sql.my-domain.local -U my-domain.local\testuser -P password 

But the FreeTDS driver is not available in the ODBC Data Source Administrator . The FreeTDS driver FreeTDS traditionally used with unixODBC . Is it possible to use this driver in a Windows environment using pyodbc ?

Update:

Turns out the FreeTDS binaries mentioned above include unixODBC . The configuration of freetds.conf , odbc.ini and odbcinst.ini was done as described, for example, here . But at the moment I have no understanding of how pyodbc should know that there is a FreeTDS driver. Indeed, an attempt to connect with the FreeTDS driver fails with the following error:

 pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') 

pyodbc only knows about the drivers available in the ODBC Data Source Administrator :

There are two ways to move forward. The first option is to make the ODBC Data Source Administrator aware of the FreeTDS driver. To do this, create a new value in the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers with the name FreeTDS and the value Installed . Then, in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI , a new FreeTDS key is FreeTDS , and the settings for the FreeTDS driver FreeTDS set as string values ​​in this registry key.

After completing this procedure, the FreeTDS driver became available in the ODBC Data Source Administrator , but the connection still failed. An attempt to create a User DSN in the ODBC Data Source Administrator with FreeTDS failed with error code 193 , which is caused by the incompatibility of 64 bits of the ODBC Data Source Administrator and the 32-bit version of FreeTDS . I do not have a 64-bit version of FreeTDS . Potentially, it could be compiled from the source.

Another option is to make pyodbc different driver manager ( unixODBC ) instead of the ODBC Data Source Administrator . I don’t know how to approach this.

Answer 1

In the end, I used pymssql version 2.1.3 installed with the wheel obtained from http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql . It includes FreeTDS , and it worked right out of the box:

 import pymssql conn = pymssql.connect( host=r'sql.my-domain.local', user=r'my-domain.local\testuser', password='password', database='testdb' ) cursor = conn.cursor() cursor.execute('SELECT * FROM testtable')