Connecting to MS SQL Server with Windows Authentication using Python?

Question

How do I connect MS SQL Server using Windows Authentication, with the pyodbc library?

I can connect via MS Access and SQL Server Management Studio, but cannot get a working connection ODBC string for Python.

Here's what I've tried (also without 'Trusted_Connection=yes'):

pyodbc.connect('Trusted_Connection=yes',                driver='{SQL Server}', server='[system_name]',                database='[databasename]')  pyodbc.connect('Trusted_Connection=yes', uid='me',                driver='{SQL Server}', server='localhost',                database='[databasename]')  pyodbc.connect('Trusted_Connection=yes',                driver='{SQL Server}', server='localhost',                uid='me', pwd='[windows_pass]', database='[database_name]')  pyodbc.connect('Trusted_Connection=yes',                driver='{SQL Server}', server='localhost',                database='[server_name]\\[database_name]')  pyodbc.connect('Trusted_Connection=yes',                driver='{SQL Server}', server='localhost',                database='[server_name]\[database_name]')  pyodbc.connect('Trusted_Connection=yes',                driver='{SQL Server}',                database='[server_name]\[database_name]') 

Answer 1

You can specify the connection string as one long string that uses semi-colons (;) as the argument separator.

Working example:

import pyodbc cnxn = pyodbc.connect(r'Driver=SQL Server;Server=.\SQLEXPRESS;Database=myDB;Trusted_Connection=yes;') cursor = cnxn.cursor() cursor.execute("SELECT LastName FROM myContacts") while 1:     row = cursor.fetchone()     if not row:         break     print(row.LastName) cnxn.close() 

For connection strings with lots of parameters, the following will accomplish the same thing but in a somewhat more readable way:

conn_str = (     r'Driver=SQL Server;'     r'Server=.\SQLEXPRESS;'     r'Database=myDB;'     r'Trusted_Connection=yes;'     ) cnxn = pyodbc.connect(conn_str) 

(Note that there are no commas between the individual string components.)