CODEWITHSUNDEEP
pythonsql-serverstringpyodbc
David Arias
David Arias

Reputation: 79

Double backslash in connection string when connecting to SQL Server with pyodbc in Jupyter

Background:

I am running Windows 10 Enterprise, using Jupyter Notebook to connect to SQL Server instance with Pyodbc

I'm able to connect successfully with trusted_connection=yes

where server = '.\SQLEXPRESS' (my local instance)

con_string = f'DRIVER={driver};SERVER={server};DATABASE={db};trusted_connection=yes;'

However, when I try to pass in credentials, which I will need when connecting to Prod/Test servers, I get an error that my username is not valid.

where server = '.\SQLEXPRESS' (my local instance) (which should still work with creds)

`username = '<domain>\<user>'`

con_string = `f'DRIVER={driver};SERVER={server};DATABASE{db};UID={username};PWD={password}'

Here is where the problem arises: I'm initiating the username as follow, to escape backslash:

username='domain\\user'

If I run a print(username), I get 'domain\user' as expected.

However when I run the same connection string as before, this time with the username and password, I get an error message from pyodbc that says:

'domain\user' does not exist.

Why is SQL Server treating the variable username as containing 'domain\\user' instead of the 'domain\user' that I'm expecting.

I have tried everything that I can think of to get a single backslash in there, nothing seems to work. From loading the values in as environment variables to "raw" strings, replacing, etc

Interestingly whenever I just get output for username aka without printing, I get 'domain\\user'

Even more confounding is why does SQL Server not kick back an error for the server, I'm not even escaping the backslash there, i am simply passing in '.\SQLEXPRESS'

I appreciate any insight into this issue.

Upvotes: 0

Views: 3119

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

In this connection string:

con_string = f'DRIVER={driver};SERVER={server};DATABASE{db};UID={username};PWD={password}'

UID is a SQL Auth login or database user. Not a Windows user. To use a Windows/AD identity you must use trusted_connection=yes. And run the program as the target user or install a credential to use that user's credentials in NTLM authentication. Or run your program with runas or runas /netonly.

Upvotes: 2

Related Questions