How to use windows authentication to connect to MS SQL server from windows workstation in another domain with Python
Question: I'm trying to connect to `SQL server 2000` installed on `Windows server 2003`
from `Windows Server 2008 R2` using `Python 3.4` and `pyodbc` module. Those
servers are in different AD domains. `Windows only` authentication is enabled
on SQL server and I can't change that.

    
    
    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 connection above 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 some questions, for example [this
one](http://stackoverflow.com/questions/16515420/connecting-to-ms-sql-server-
with-windows-authentication-using-python), suggesting to add
`trusted_connection='yes'` argument to `pyodbc` connection for support of
windows authentication but in this case it does not help because with this
option local credentials are used and I need to provide credentials explicitly
because originating workstation is in a different AD domain.

Creation of `User DSN` in `ODBC Data Source Administrator` with `SQL Server`
driver fails with the same error mentioned above.

Is there a way to make this work?

Meanwhile I installed `FreeTDS`driver for Windows from
<http://sourceforge.net/projects/freetdswindows/> and connection test using
`tsql` utility does work:

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

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

Update:

It turns out `FreeTDS` binaries mentioned above include `unixODBC` as well.
Configuration of `freetds.conf`, `odbc.ini` and `odbcinst.ini` was made like
described, for example,
[here](http://stackoverflow.com/questions/16925825/having-troubles-with-
unixodbc-freetds-and-pyodbc). But at this point I don't have understanding how
`pyodbc` is supposed to know that `FreeTDS` driver exists. And indeed
connection attempt with `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 drivers available in `ODBC Data Source
Administrator`:

[![ODBC Data Source Administrator
screenshot](http://i.stack.imgur.com/9zSJo.png)](http://i.stack.imgur.com/9zSJo.png)

There are 2 ways to move forward. First option is to make `ODBC Data Source
Administrator` aware of `FreeTDS` driver. To achieve that a new value needs to
be created in registry key `HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC
Drivers` with name `FreeTDS` and value `Installed`. Then a new key `FreeTDS`
is created in `HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI` and settings for
`FreeTDS` driver are set as string values in this registry key.

[![FreeTDS registry
settings](http://i.stack.imgur.com/O4B9F.png)](http://i.stack.imgur.com/O4B9F.png)

After completion of this procedure `FreeTDS` driver became available in `ODBC
Data Source Administrator` but connection still failed. Attempt to create
`User DSN` in `ODBC Data Source Administrator` with `FreeTDS` fails with error
code `193` which is caused by incompatibility of 64 bit `ODBC Data Source
Administrator` and 32 bit version `FreeTDS`. I don't have 64 bit version of
`FreeTDS` available. Potentially it could be possible to compile it from
source.

Another option is to make `pyodbc` use another driver manager (`unixODBC`)
instead of `ODBC Data Source Administrator`. Don't know how to approach that
yet.


Answer: I ended up using [pymssql](https://github.com/pymssql/pymssql "pymssql")
version 2.1.3 installed with a wheel obtained from
<http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql>. It has `FreeTDS`
included 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')
    

