Using SQLObject with Microsoft SQL Server and Windows Authentication

Here is how to use Windows Authentication to connect to Microsoft SQL Server with SQLObject.

from sqlobject import connectionForURI

# Replace hostname and db with your values.  Notice SSPI in the URI.
connection_string= 'mssql://hostname/db?sspi=1' 

# connect
connection = connectionForURI(connection_string) 

# simple query
x=connection.queryAll('select 1 as foo') 

# show the results of query (first row)
x[0] 

The SQLObject documentation is frustratingly silent on the issue, so I had to discover the solution by reading the source. If the SSPI parameter is omitted, then SQLObject builds a connection string using SQL Server authentication. With the parameter, you get a SQL connection string like this:

Provider=SQLOLEDB;Data Source=hostname;Initial Catalog=db;Integrated Security=SSPI;Persist Security Info=False

By the way, if you are using Microsoft SQL Server Expression Edition, try adding ncli=1.

Also if you are getting an InterfaceError, make sure to install pywin32.

This was tested with Python 2.7.3, SQLObject 1.3.2, adodbapi 2.4.2.2, pywin32 build 218.

Advertisement