pyodbc.connect() to SQL Server database fails-CodePudding

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.

CodePudding user response：

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. "