Connecting to MS SQL Server using python on linux with 'Windows Credentials'
Question: 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: **As pointed out in one of the comments, this answer is quite stale by now. I
regularly and routinely use GSSAPI to authenticate from Linux to SQL Server
2008 R2 but mostly with the EasySoft ODBC manager and the (commercial)
EasySoft ODBC SQL Server driver.**

In early 2009, a colleague and I managed to connect to a SQL Server 2005
instance from Solaris 10 using GSSAPI (Kerberos credentials) using DBB::Perl
over a FreeTDS build linked against a particular version of the MIT kerberos
libraries. The trick was -- and this is a little bit difficult to believe but
I have verified it by looking through the FreeTDS source code -- to specify a
_zero-length_ user_name. If the length of the user_name string is 0 then the
FreeTDS code will attempt to use GSSAPI (if that support has been compiled
in). I have not been able to do this via Python and pyodbc as I could not
figure out a way of getting ODBC to pass down a zero-length user_name.

Here in the perl code .. there are multiple opportunities for breakage wrt
configuration files such as .freetds.conf etc. I seem to recall that the
principal had to be in uppercase but my notes seem to be in disagreement with
that.

    
    
    $serverprincipal = 'MSSQLSvc/foo.bar.yourdomain.com:1433@YOURDOMAIN.COM';
    $dbh = DBI->connect("dbi:Sybase:server=THESERVERNAME;kerberos=$serverprincipal", '', '');
    

You will have to know how to use the setspn utility in order to get the SQL
Server server to use the appropriate security principal name.

I do not have any knowledge of the kerberos side of things because our
environment was set up by an out and out Kerberos guru and has fancy stuff
like mutual trust set up between the AD domain that the SQL Server is running
in and the Kerberos domain that my client was running in.

There is some code <http://code.google.com/p/libsqljdbc-auth/> which does
GSSAPI authentication from Linux to SQL Server but it is Java only. The author
(who seems to know his stuff) also has contributed a similar patch to the jTDS
project which works with more recent versions of Java that have GSSAPI built
in.

So the pieces are all there, it is just a big tangled mess trying to get them
all to work together. I found the pyodbc to unixODBC to FreeTDS odbc to TDS
integration pretty hard to trace/debug. The perl stuff because it was a pretty
thin wrapper on top to CT-Lib was much easier to get going.

