Connecting to MS SQL Server using python on Linux using "Windows credentials"

Question

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

I can connect perfectly to my Windows machine using Windows credentials, but trying to do the same with Linux python with pyodbs + freetds + unixodbc

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

leads to this error:

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

I'm sure the password is spelled correctly, but I have tried many different username combinations:

 DOMAIN\username DOMAIN\\username 

or even

 UID=username;DOMAIN=domain 

to no avail. Any ideas?

Answer 1

As indicated in one of the comments, this answer is pretty outdated. I regularly and regularly use GSSAPI to authenticate with Linux on SQL Server 2008 R2, but mostly with the EasySoft ODBC manager and the (commercial) EasySoft ODBC SQL Server driver.

In early 2009, my colleague and I were able to connect to an instance of SQL Server 2005 from Solaris 10 using GSSAPI credentials (Kerberos) using DBB :: Perl on top of the FreeTDS assembly associated with a specific version of the kerberos MIT libraries. The trick was - and it's a little hard to believe, but I checked it, looking at the FreeTDS source code - to specify a username with zero length. If the length of the user_name string is 0, then the FreeTDS code will try to use GSSAPI (if this support was compiled). I could not do this through Python and pyodbc, because I could not figure out how to get ODBC to pass a username with zero length.

Here, in the Perl code, there are several possibilities for breaking configuration files, such as .freetds.conf, etc. I seem to remember that the main one should have been uppercase, but my notes do not seem to agree with this.

  $ serverprincipal = 'MSSQLSvc / foo.bar.yourdomain.com: [e-mail] ';
 $ dbh = DBI-> connect ("dbi: Sybase: server = THESERVERNAME; kerberos = $ serverprincipal", '', '');

You will need to know how to use setspn so that SQL Server uses the appropriate security principal name.

I don’t have knowledge about the side of the keberos, because our environment was created by the Kerberos guru and has fancy things, such as mutual trust, configured between the AD domain in which SQL Server is running and the Kerberos domain in which my client was working.

There is some code http://code.google.com/p/libsqljdbc-auth/ that does GSSAPI authentication from Linux to SQL Server, but it is only Java. The author (who seems to know his stuff) also introduced a similar patch into the jTDS project, which works with newer versions of Java that have GSSAPI embedded.

So all of this is, it's just a big messy mess that is trying to get them all to work together. I found pyodbc for unixODBC for FreeTDS odbc for integration with TDS is quite difficult to track / debug. Perl material, because it was a pretty thin shell on top of CT-Lib, was a lot easier to get started.

Answer 2

At least in March 2013, this seems to work out of the box with FreeTDS. I have indicated the version of the TDS protocol for a good evaluation - not sure if it matters:

 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 is also supported in the official Microsoft driver for Linux: http://msdn.microsoft.com/en-us/library/hh568450.aspx . I'm not sure how many Linux distributions actually work or how much the source is available. They explicitly mention RHEL 5 and 6 and some page dependencies.

Answer 3

I have not done this for a while, but I remember that all unixodbc + FreeTDS + pyobbc was a bit complicated. However, this can be done, and after installation it is not so difficult.

This site contains very good instructions: http://www.pauldeden.com/2008/12/how-to-setup-pyodbc-to-connect-to-mssql.html (archived copy in the web archive)

Also, in my experience, pyodbc had problems compiling / running on 64-bit Linux machines. Because of this, we ended up using ceODBC. ceODBC is not as stable as pyodbc (encountered more unexpected errors than pyodbc when running in python prorgram), but it is very easy to get up and running on a 64-bit version of Linux.

Answer 4

It may be too late to help you, but I ran into the same problem. At the time of writing the latest version, pyobbc allows me to log in with Windows credentials. Just leave the UID field blank in the connection string as follows:

 cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=myserverinstance;DATABASE=mydatabase;UID=;PWD=mypassword') 

Now this is using your existing Windows credentials when you are logged in ... not sure how to specify any credentials for the arb windows domain ...

Answer 5

I do not believe that you can log in to your Windows domain account this way. You need to configure the user in sql directly for this method of transferring credentials.