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]')
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.)