pyodbc - what username/password is passed here?

Question

I have the following code:

import pyodbc

cnxn = pyodbc.connect("DRIVER={SQL Server};"
                      +"SERVER=somesqlserver2008.example.com;"
                      +"DATABASE=exampledatabase;")
cursor = cnxn.cursor()
#do stuff...

The above code runs just fine. I have reason to believe, though, that this code is actually passing some form of credentials 'behind my back' (so to speak). For example, this code:

cnxn = pyodbc.connect("DRIVER={SQL Server};"
                      +"SERVER=someOTHERsqlserver2008.example.com;"
                      +"DATABASE=exampledatabase;")
cursor = cnxn.cursor()

returns:

Traceback (most recent call last):
  File "C:\Users\<my username>\Documents\sql_connect_test1.py", line 27, in <module>
    +"SERVER=someOTHERsqlserver2008.example.com;")
Error: ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '<user name appears here>'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '<user name appears here>'. (18456)")

Even though I didn't specify a username/password in the second connection string, it appears that a username and password were used by pyodbc. How can I find out what username and password were used?

score 2 · Answer 1 · answered Aug 23 '13 at 20:57

2

It must be passing your WINDOWS domain credentials for you, if you don't specify. On the server where it fails, your Windows account does not have access (or the other machine is in a different, untrusted domain). Your program probably shouldn't rely on Windows auth if this is a common scenario - a different domain or different users with different rights will try to run it. You should consider using SQL authentication and putting the connection string in a config file (not inside the code).

answered Aug 23 '13 at 20:57

Aaron Bertrand

272,866
37
466
490

Hmm... I have actually tried adding a `"UID=;PWD=")` to the first connection string, but it didn't work. When I leave them out of the connection string, it works fine. Not sure what's going on. – SheerSt Aug 23 '13 at 21:00
Did you try UID = Windows username, or UID = SQL auth login? Anyway, this is just a total guess and suggestion, I have zero experience with pyodbc. What do their docs say about this? – Aaron Bertrand Aug 23 '13 at 21:01
yes, I did try a `UID=`. I'm not sure what you mean by "SQL auth login". – SheerSt Aug 23 '13 at 21:03
SQL auth login is a login you create in SQL Server using `CREATE LOGIN` (the one you may be most familiar with is called `sa`), and of course without specifying `FROM WINDOWS`. Your Windows login is passed for you when using Windows authentication - you can't hard-code the name into a connection string, and you absolutely should never put your Windows password there. – Aaron Bertrand Aug 23 '13 at 21:04
@Evan please read http://stackoverflow.com/questions/750303/what-is-the-differences-between-sql-server-authentication-and-windows-authentica, http://securitythoughts.wordpress.com/2009/12/24/trusted-vs-mixed-mode-authentication/, http://www.serverintellect.com/support/sqlserver/sql-auth-types.aspx, etc. – Aaron Bertrand Aug 23 '13 at 21:12

score 0 · Answer 2 · answered Dec 04 '20 at 00:45

This is quite an old answer, but ranks highly for modern searches along these lines so I thought I'd add a few thoughts that arrive here and wonder what still applies.

Aaron Bertrand's post is an excellent summary. I discovered a similar issue a while back when I shared a utility I wrote originally for myself. When run by another user the utility would silently fail in the background as their domain account had no access to the database.

Below is some simplified sample code that works for for talking to an SQL Server instance. The following code prompts the user for the password at runtime, far from an ideal way to do it in production, but still better than putting the credential in your code directly.

db_user_auth = input("Please enter the db password:")

conn_str = "Driver={SQL Server Native Client 11.0}"
conn_str = f'{conn_str};Server={db_svr_name}'
conn_str = f'{conn_str};Database={db_name}'
conn_str = f'{conn_str};UID={db_user_name}'
conn_str = f'{conn_str};PWD={db_user_auth}'

db_conn = pyodbc.connect(conn_str)

FWIW I use a very similar technique for connecting to a some legacy Microsoft Access databases.

pyodbc - what username/password is passed here?

2 Answers2