Connect to SQL Server using pyodbc

Question

I am trying to connect to SQL Server using pyodbc. I am facing the following problem: My username is: DESKTOP-B3DDLU2\maria

Login failed for user 'DESKTOP-B3DDLU2\\maria'

Any idea how to overcome the \\ instead of \ problem? I using the following code:

conn = pyodbc.connect( r'DRIVER={SQL Server};' r'SERVER=DESKTOP-B3DDLU2\SQLEXPRESS;' r'DATABASE=[xxx];' r'UID=DESKTOP-B3DDLU2'+('\\')+r'maria;' r'PWD=xxxxx')

This isn't a solution to your particular problem, but a work-around: I always create dedicated SQL Server username and passwords for connecting from pyodbc. If you have the access level to do those, this is considered a better practice from a security standpoint as well (using a dedicated account for the service). — FlipperPA
– FlipperPA, Commented Apr 28, 2017 at 22:30
Are you trying to connect from the same machine on which SQL Server is running? (It appears so, given that SERVER and UID both start with DESKTOP-B3DDLU2.) If so, are you logged into Windows as DESKTOP-B3DDLU2\maria when you are running the Python code? — Gord Thompson
– Gord Thompson, Commented Apr 28, 2017 at 22:30
@FlipperPA re: "I always create dedicated SQL Server username and passwords for connecting from pyodbc" - Do you mean SQL Authentication username/password credentials? If so, do you create one for each potential user of the Python app? Also, the account that runs the MSSQL$SQLEXPRESS service is something else entirely, no? — Gord Thompson
– Gord Thompson, Commented Apr 28, 2017 at 22:45
@GordThompson Yes, I create individual SQL Auth usernames and passwords by service. I have a dedicated user for scheduled tasks, another for each web site, ones for each vendor doing data loads, etc. I'm not familiar with SQL Express, and we don't typically have users connecting outside of a service. — FlipperPA
– FlipperPA, Commented Apr 28, 2017 at 22:52
@FlipperPA - Ah, okay, so when you say "a dedicated account for the service" you're referring to some other service that connects to the SQL Server instance, not the service running the SQL Server instance. Right? — Gord Thompson
– Gord Thompson, Commented Apr 28, 2017 at 22:59

cco · Accepted Answer · 2017-04-28 21:37:53Z

1

Don't use \\ in a raw string when you only want a single \. e.g.

conn = pyodbc.connect( r'DRIVER={SQL Server};' r'SERVER=DESKTOP-B3DDLU2\SQLEXPRESS;' r'DATABASE=[xxx];' r'UID=DESKTOP-B3DDLU2\maria;' r'PWD=xxxxx')

answered Apr 28, 2017 at 21:37

cco

6,3712 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mariano Over a year ago

Hi CCO thank you for your help. I tried that route because if I just use single \ ,ie. copy/paste my user, I still get the same error with the duplicated \

Hamlet · Accepted Answer · 2017-07-10 09:10:32Z

0

This worked for me:

Trusted_Connection=yes

example:

import pyodbc connection_string ='DRIVER={ODBC Driver 11 for SQL Server};Server=<server>;Database=<DB>;Uid=<UID>;Trusted_Connection=yes' connection = pyodbc.connect(connection_string)

answered Jul 10, 2017 at 9:10

Hamlet

1

2 Comments

DaveyDaveDave Over a year ago

This answer would be much better if you explain what this additional parameter does, and what its potential limitations are. Knowing nothing about it, I can't add anything constructive, but purely from the name, it sounds like it could be introducing some sort of security vulnerability.

matt123788 Over a year ago

Adding the Trusted_Connection=yes parameter overrides the entered Uid=<UID> and Pwd=<PWD> parameters and uses the current user's credentials to login. See this link for further explanation. If the OP wants to use different credentials, this solution will not work.

Collectives™ on Stack Overflow

Connect to SQL Server using pyodbc

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related