0

I'm trying to connect to a SQL Server database with via pyodbc.connect().

My connection string is the following:

connection = pyodbc.connect(
     "DRIVER={SQL Server};SERVER=server;DATABASE=databaseP;UID=user;PWD=password"
)

but it fails over and over again, and I get the following error:

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

I tried to escape anything, what appeared to me as a possible problem (e.g. _ or @), but to no avail.

After I entered the very same data via DSN, and used the following connection string, it worked:

connection = pyodbc.connect(
    dsn="my_dsn",
    uid=user,
    pwd=password
)

What is wrong with the first connection string? I would rather (for personal reasons) use the first one, but still cannot find out, why it's not working.

Improve this question
9
  • 1
    Please show the full error message from the SQL Server log (it contains more info than what you get from the client app). Is this a SQL Server Authentication login or a Windows Authentication login that is failing?
    – Charlieface
    Commented Nov 14, 2021 at 21:54
  • Use SSMS and attempt to connect to the same instance with those same credentials and access the same database. Does it work?
    – SMor
    Commented Nov 14, 2021 at 21:55
  • @Charlieface: Here is the full message I get after running the script: Exception has occurred: ProgrammingError ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "XXXXX" requested by the login. The login failed. (4060) (SQLDriverConnect); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "XXXXX" requested by the login. The login failed. (4060)'). This is a SQL Server Authentification login, I assume, as the trusted_connection is set to "no"
    – Viont
    Commented Nov 14, 2021 at 22:13
  • @Smor: Unfortunately, I'm not able to, as I'm doing it on my working laptop, and do not have any permissions to install SSMS
    – Viont
    Commented Nov 14, 2021 at 22:16
  • 1
    Sounds like you need to speak to your DBA for connection troubleshooting first.
    – Thom A
    Commented Nov 14, 2021 at 22:22

1 Answer 1

Reset to default
2

If you connect without specifying a database you connect to the login's DEFAULT_DATABASE, which is typically Master.

If you specify a database in your connection string you will connect directly to that database. And if the requested database doesn't exist, or you don't have access to it you get the error: "Cannot open database "XXXXX" requested by the login. The login failed. "

Improve this answer
1
  • Hi @David Browne - thanks a lot for the hint. After commenting out the line with the database name, I was able to connect via the connection string
    – Viont
    Commented Nov 15, 2021 at 10:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.