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-30 18:16:51Z

105

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, 2020 at 18:16

answered May 13, 2013 at 6:14

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

stackoverflowuser95 Over a year ago

Thanks, that .\ syntax did the trick. Note that: pyodbc.connect('Trusted_Connection=yes', driver='{SQL Server}', server='.\[server_name]', database='[database_name]') also worked.

Nandha Kumar Over a year ago

how to connect with userid and password

CyprUS Over a year ago

This did no work for me. The comment below worked though.

mdegges Over a year ago

is there any way to pass a different set of windows auth credentials? e.g. 'Trusted_Connection=yes;UID=blah;PWD=blah'?

Gord Thompson Over a year ago

@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 (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.

Bryan · Accepted Answer · 2013-05-13 13:05:06Z

29

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, 2013 at 13:05

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

2 Comments

Zizzipupp Over a year ago

You mention a keyword, yet your string is not using it?

Bryan Over a year ago

trusted_connection is the authentication-related keyword

Alexei - check Codidact · Accepted Answer · 2017-12-26 10:09:57Z

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, 2017 at 10:09

Alexei - check Codidact

23.2k22 gold badges159 silver badges179 bronze badges

answered Jul 28, 2017 at 15:03

Allan Elder

4,11421 silver badges20 bronze badges

2 Comments

Denziloe Over a year ago

This needs to be higher. localhost didn't work for me. (local) did.

Farhan Javed Over a year ago

This works for me because I forgot my password and wanted to use windows authentication. Connected successfully from Jupyter Notebook to local sql server

Ajay Anil Zori · Accepted Answer · 2024-10-27 16:31:57Z

HI Use below clode to connect you data & export this data into excel

please use pip command to install dist in command line.

pip install pypyodbc
pip install pandas pip install xlsxwriter pip install requests

CODE__________________

import pypyodbc as odbc 
import pandas as pd
import xlsxwriter
import requests
import datetime

conn_str = (
    r'Driver=SQL Server;'
    r'Server=AJAYZORI\SQLEXPRESS;'
    r'Database=Production;'
    r'Trusted_Connection=yes;'
    )

try:
    conn =  odbc.connect(conn_str)
except:
    print("Error")
    exit()


try:
    with conn.cursor() as cursor:
        # Read data from database
        sql = "SELECT * FROM MAT_LISTING_MUMBAI_DATABASE_TBL"
        cursor.execute(sql)

        # Fetch all rows

        columns = [desc[0] for desc in cursor.description]
        rows = cursor.fetchall()
        #print(rows[0])
        # Print results
        #for row in rows:
        #    print(row)
        
        df = pd.DataFrame(list(rows), columns=columns)
        now = datetime.datetime.now()
        print(now)
        writer = pd.ExcelWriter( now.strftime("%B  %G HH_%I MM_%M  SS_%S %p") + " Output.xlsx", engine="xlsxwriter",)
        df.to_excel(writer, sheet_name='data')
        writer.close()

finally:
    conn.close()

Collectives™ on Stack Overflow

Connecting to MS SQL Server with Windows Authentication using Python?

4 Answers 4

5 Comments

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related