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

Gord Thompson · Accepted Answer · 2020-12-30T18:16:51.087

97

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

edited Dec 30 '20 at 18:16

answered May 13 '13 at 06:14

Gord Thompson

116,920
32
215
418

Thanks, that `.\ ` syntax did the trick. Note that: `pyodbc.connect('Trusted_Connection=yes', driver='{SQL Server}', server='.\[server_name]', database='[database_name]')` also worked. – stackoverflowuser95 May 13 '13 at 06:20
1

how to connect with userid and password – Nandha Kumar Jan 07 '15 at 16:38
This did no work for me. The comment below worked though. – CyprUS Jun 20 '16 at 15:23
2

is there any way to pass a different set of windows auth credentials? e.g. 'Trusted_Connection=yes;UID=blah;PWD=blah'? – mdegges Sep 28 '17 at 22:21
8

@mdegges - Not as such; Microsoft's ODBC driver treats `Trusted_Connection` and `UID`/`PWD` as mutually exclusive. The "preferred" solution on Windows clients would be to run the app as the other user via [runas](https://technet.microsoft.com/en-us/library/cc771525(v=ws.11).aspx) (command line) or [Shift-Right_click] > "Run as different user" (GUI). The corresponding workaround on Linux would be to use the FreeTDS ODBC driver which still supports the older NTLM authentication scheme via the `DOMAIN=` connection string parameter. – Gord Thompson Sep 28 '17 at 22:40

score 28 · Answer 2 · answered May 13 '13 at 13:05

28

Windows Authentication can also be specified using a keyword. Nothing functionally different from the accepted answer, I think it makes code formatting a bit easier:

cnxn = connect(driver='{SQL Server}', server='localhost', database='test',               
               trusted_connection='yes')

answered May 13 '13 at 13:05

Bryan

17,112
7
57
80

You mention a keyword, yet your string is not using it? – Zizzipupp Jan 14 '22 at 16:39
`trusted_connection` is the authentication-related keyword – Bryan Jan 14 '22 at 18:09

score 9 · Answer 3 · edited Dec 26 '17 at 10:09

9

Just wanted to add something as I see the solutions here using localhost; in my experience, SQL Server has issues with this, not sure if its the ODBC driver or the service itse, and prefers the use of (local) if you don't want to specify the local machines name.

cnxn = connect(driver='{SQL Server}', server='(local)', database='test',               
               trusted_connection='yes')

edited Dec 26 '17 at 10:09

Alexei - check Codidact

22,016
16
145
164

answered Jul 28 '17 at 15:03

Allan Elder

4,052
17
19

3

This needs to be higher. `localhost` didn't work for me. `(local)` did. – Denziloe Jul 19 '19 at 12:10
1

This works for me because I forgot my password and wanted to use windows authentication. Connected successfully from Jupyter Notebook to local sql server – Farhan Javed Jul 09 '22 at 18:43

Connecting to MS SQL Server with Windows Authentication using Python?

3 Answers3

Linked

Related