Search code examples
pythonpyodbcsqlconnection

python connection with SQL server

I used the pyodbc and pypyodbc python package to connect to SQL server.

Drivers used anyone of these ['SQL Server', 'SQL Server Native Client 10.0', 'ODBC Driver 11 for SQL Server', 'ODBC Driver 13 for SQL Server'].

connection string :

connection = pyodbc.connect('DRIVER={SQL Server};'
'Server=aaa.database.windows.net;'
'DATABASE=DB_NAME;'
'UID=User_name;'
'PWD=password')

now I am getting error message like

DatabaseError: (u'28000', u"[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user

But I can connect to the server through the SQL server management studio.

its on SQL Server Authentication, Not Windows Authentication.

Is this about python package and driver issue or DB issue??? how to solve?

Solution

  • The problem is not driver issue, you can see the error message is DatabaseError: Login failed for user, it means this problem occurs if the user tries to log in with credentials that cannot be validated. I suspect you are login with your windows Authentication, if so, use Trusted_Connection=yes instead:

    connection = pyodbc.connect('DRIVER={SQL Server};Server=aaa.database.windows.net;DATABASE=DB_NAME;Trusted_Connection=yes')

    For more details, please refer to my old answer about the difference of SQL Server Authentication modes.