pyodbc error when connecting to SQL database “Cannot open database ”database3“ requested by the login.”

1
votes

I have a database that I connect to that has previously worked and come back to the script a few months later and now its stopped working and im getting the error below :

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + DB['servername'] + ';DATABASE=' + DB['database'] + ';Trusted_Connection=yes' + ';UID=' + DB['user'] + ';PWD=' + DB['password'])

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "database3" requested by the login. The login failed. (4060) (SQLDriverConnect); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "database3" requested by the login. The login failed. (4060)')

I can connect using the same credentials using SQL server server management studio version 18 and query the database in question. I'm using the following version numbers: 

Python: 3.6.7 
Pandas: 1.0.3
pyODBC: 4.0.27 also tried upgrading this to 4.0.30.

The code im using to connect is :

# parameters for connecting to SQL databse 
DB = {'servername': 'server\sqlexpress',
      'database': 'database3',
     'user':'sa',
     'password' : 'Password'}

# create the connection
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + DB['servername'] + ';DATABASE=' + DB['database'] + ';Trusted_Connection=yes' + ';UID=' + DB['user'] + ';PWD=' + DB['password'])

Can some help me solve this problem?

1
sql-serverpython-3.xpyodbc
You should be providing UID=...;PWD=... OR Trusted_Connection=yes, not both. If you want to use SQL Authentication (as opposed to Windows Authentication) then use Trusted_Connection=no (or just omit it altogether). – Gord Thompson
thanks that solved my problem. not sure how that mistake creaped into my jupyter notebook. Did you want to write it as a answer? so i can assign you as giving the correct answer. – resolver101

1 Answers

1
votes

You should be providing UID=...;PWD=... OR Trusted_Connection=yes, not both. If you want to use SQL Authentication (as opposed to Windows Authentication) then use Trusted_Connection=no (or just omit it altogether).