Error 28000: Login failed for user DOMAIN\\user with pyodbc

Question

I am trying to use Python to connect to a SQL database by using Window authentication. I looked at some of the posts here (e.g., here), but the suggested methods didn't seem to work.

For example, I used the following code:

cnxn = pyodbc.connect(driver='{SQL Server Native Client 11.0}',                       server='SERVERNAME',                        database='DATABASENAME',                                      trusted_connection='yes') 

But I got the following error:

Error: ('28000', "[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server] Login failed for user 'DOMAIN\\username'. (18456) (SQLDriverConnect); [28000] [Microsoft] [SQL Server Native Client 11.0][SQL Server]Login failed for user 'DOMAIN\\username'.  (18456)")  

(Note that I replaced the actual domain name and user name with DOMAIN and username respectively, in the error message above.)

I also tried using my UID and PWD, which led to the same error.

Lastly, I tried to change the service account by following the suggestion from the link above, but on my computer, there was no Log On tab when I went to the Properties of services.msc.

I wonder what I did wrong and how I can fix the problem.

Answer 1

Connecting from a Windows machine:

With Microsoft's ODBC drivers for SQL Server, Trusted_connection=yes tells the driver to use "Windows Authentication" and your script will attempt to log in to the SQL Server using the Windows credentials of the user running the script. UID and PWD cannot be used to supply alternative Windows credentials in the connection string, so if you need to connect as some other Windows user you will need to use Windows' RUNAS command to run the Python script as that other user..

If you want to use "SQL Server Authentication" with a specific SQL Server login specified by UID and PWD then use Trusted_connection=no.

Connecting from a non-Windows machine:

If you need to connect from a non-Windows machine and the SQL Server is configured to only use "Windows authentication" then Microsoft's ODBC drivers for SQL Server will require you to use Kerberos. Alternatively, you can use FreeTDS ODBC, specifying UID, PWD, and DOMAIN in the connection string, provided that the SQL Server instance is configured to support the older NTLM authentication protocol.