sql-serversql-server-2008-r2odbcwindows-authenticationpyodbc

Connecting to MS SQL Server with Windows Authentication using Python?


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

Solution

  • 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.)