CODEWITHSUNDEEP
pythonsql-serverauthenticationpyodbc
Student of the Digital World
Student of the Digital World

Reputation: 3041

Python - Pyodbc Connection error

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.

Upvotes: 2

Views: 12700

Answers (3)

Elvin Aghammadzada
Elvin Aghammadzada

Reputation: 881

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)

Upvotes: 0

Rishab Kumar
Rishab Kumar

Reputation: 9

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)

Upvotes: 0

Tiny.D
Tiny.D

Reputation: 6556

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.

Upvotes: 5

Related Questions