python connection with SQL server

Question

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?

Answer 1

You can add Trusted_Connection=NO; in your connection string after the password

Answer 2

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.

Answer 3

I think problem because of driver definition in your connection string. You may try with below.

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

Answer 4

I see you have no port defined in your script.

The general way to connect to a server using pyodbc ${DBName} ${DBUser} ${DBPass} ${DBHost} ${DBPort}, where DBName is your database name, DBUser is the username used to connect to it, DBPass is the password, DBHost is the URL of your database, and DBPort is the port you use to connect to your DB.

I use MS SQL so my port is 1433, yours might be different.

I've had this issue just today, and that fixed it.

Answer 5

I applied your connection string and updated it with my server connections details and it worked fine. Are you sure your are passing correct user name and password ? Login failed implies connection was established successfully, but authentication didn't pass.