Python - Pyodbc Connection error

Question

I am trying to connect to the SQL Server database using Python3.4

This is the code that works for me

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=DESKTOP-GDM2HQ17\SQLEXPRESS;DATABASE=pyconnect;Trusted_Connection=yes')

and I login into my Management studio - database using Windows connection.

Here is the code, which is not working for me :

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=DESKTOP-GDM2HQ17\SQLEXPRESS;DATABASE=pyconnect;UID=DESKTOP-GDM2HQ17\sid;PWD=123')

Kindly share your thoughts on where I am going wrong.

Actually I login into SSMS using Windows auth and those are my credentials. — Student of the Digital World, May 06 '17 at 10:46

score 5 · Accepted Answer · answered May 06 '17 at 11:42

There are two SQL Server Authentication modes:

1, Connecting Through Windows Authentication:

When a user connects through a Windows user account, SQL Server validates the account name and password using the Windows principal token in the operating system.

2, Connecting Through SQL Server Authentication:

When using SQL Server Authentication, logins are created in SQL Server that are not based on Windows user accounts. Both the user name and the password are created by using SQL Server and stored in SQL Server.

Your first code is working as it is Connecting Through Windows Authentication.

Your second code is not working as it is trying to find the credentials (login and password) which are stored in SQL Server, but the credentials is not created in SQL server.

Moreover, you could refer official doc to know how to Change Server Authentication Mode. Hope it will help you.

Thanks sir ! Exactly what I wanted to know. – Student of the Digital World May 06 '17 at 11:54 — Student of the Digital World, May 06 '17 at 11:54

score 0 · Answer 2 · answered Jan 09 '19 at 06:50

DRIVER='{SQL Server}' works

below code is tested.....

import pyodbc
con = pyodbc.connect('Driver={SQL Server};'
                  'Server=LAPTOP-PPDS6BPG;'
                  'Database=training;'
                  'Trusted_Connection=yes;')

cursor = con.cursor()
sql_query =  'SELECT * FROM Students'
cursor.execute(sql_query)

for row in cursor:
    print(row)

score 0 · Answer 3 · answered Jun 24 '21 at 13:12

This works fine for me better than any other I could find

import pyodbc 
import pandas as pd

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=10.****;'
                      'Database=Ma**;'
                      'UID=sql**;'
                      'PWD=sql**;')

cursor = conn.cursor()

sql = """\
EXEC [dbo].[GetNewPayment] @Login=?, @PasswordMD5=?, @RevokeTimeFrom=?, @RevokeTimeTo=? Status=?
"""
params = ('a***', 'c2ca***', '2021-05-01','2021-05-02', '3')
cursor.execute(sql, params)

Python - Pyodbc Connection error

3 Answers3

Linked