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.

• Tiny.D: What's the error message?
• Student of the Digital World: login failed for the user.
• Tiny.D: can you try other user, it seems that you are using SID.
• Student of the Digital World: Actually I login into SSMS using Windows auth and those are my credentials.

Answer 1

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.

• Student of the Digital World: Thanks sir ! Exactly what I wanted to know.

Answer 2

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)

Answer 3

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)