So I had a good fight today with a SQL Server 2008 connection that would not work. The error was:
Cannot open database requested in login myDbName. Login fails. Login failed for user 'myDbUserName'
This was dumbfounding. Troubleshooting the usual suspects, the following were attempted:
- check the database server to ensure that the database exists
- check the user login to ensure it exists, check the permissions for the user for this database
- try to connect using SQL Server authentication using this username and password from the Enterprise Manager - this works perfectly fine - but the ASP.NET application still gives this error
- double, triple and quadruple check the connection string for the ASP.NET application - no problems found
- drop the database and the login, recreate and try again - no difference
- reboot the database server - no difference
- check the event log for error messages - only the one displayed above is shown - nothing else out of the ordinary
I finally figured out, after a couple hours of head-scratching, from going through some event logs within SQL Server Enterprise Manager that the database name had 6 space characters appended to the name. Therefore what appeared in Enterprise manager and everywhere else as "myDbName" was actually "myDbName ". Something is obviously wrong with our MSBuild script which is autogenerating the database for us during the build process... I still have to figure out how it happened, but at least now I know why it would work in Enterprise Manager and not from the application. The app defines the connection string explicitly (with incorrect name missing spaces), while EntMgr connects and gives you a list of databases to choose from, so you never need to define the database name.
Once I renamed the database to remove the blank spaces, everything worked again.
I confirmed my theory by running this query, which highlights the extra spaces:
SELECT '"' + name + '"' FROM sys.databases
Ahhhh, the world makes sense again.