SQL Server: Cannot open database requested by the login

Question

While debugging an ASP.Net MVC application running under IIS Express, I get the following error when attempting to use the membership provider:

Cannot open database "MyDB" requested by the login. The login failed.

Login failed for user 'MY-PC\MyName'.

I attempted to troubleshoot using the steps outlined in

https://stackoverflow.com/a/2577854/141172

SELECT SUSER_ID('MY-PC\MyName')

returns an ID.

SELECT USER_ID('MY-PC\MyName')

returns NULL

CREATE USER [MY-PC\MyName] FROM LOGIN [MY-PC\MyName]

returns the error message

The login already has an account under a different user name.

Indeed, there is a login account, automatically created when I created the database, called

dbo

which maps to MY-PC\MyName.

My connection string is

Data Source=.\SQLEXPRESS;Initial Catalog=MyDB;Persist Security Info=True;Integrated Security=SSPI;

Running in a production environment I would know how to create appropriate users and logons. I'm stuck on how to resolve the fact that IIS Express is using my Windows account name to try and log into MyDB when that Windows account is already associated with dbo.

Solution

It turned out to be a typo in the Initial Catalog portion of the connection string.

Looking in the event log, I saw an error like

Login failed for user 'MY-PC\MyName'. Reason: Failed to open the explicitly specified database. [CLIENT: ]

Then looking on the details tab of the same event, I saw that the Binary Data In Bytes information contained

0000: 18 48 00 00 0E 00 00 00   .H......
0008: 17 00 00 00 45 00 52 00   ....E.R.
0010: 49 00 43 00 2D 00 4F 00   I.C.-.O.
0018: 52 00 49 00 47 00 49 00   R.I.G.I.
0020: 4E 00 5C 00 53 00 51 00   N.\.S.Q.
0028: 4C 00 45 00 58 00 50 00   L.E.X.P.
0030: 52 00 45 00 53 00 53 00   R.E.S.S.
0038: 00 00 07 00 00 00 6D 00   ......m.
0040: 61 00 73 00 74 00 65 00   a.s.t.e.
0048: 72 00 00 00               r...

Notice how the last portion of that is UTF-16 encoded the word "master". That got me on the track that the connection is being made to the master catalog rather than the requested one, which in turn lead to noticing the typo in the catalog name in the connection string.

It would have been so much nicer if Microsoft would have just show an error like "No such catalog: MyCatalog".

Comments

[Jason Kleban]

HOLY $HIT What a crappy error message! It was in fact a misspelled database name for me too. I ended up removing my login which locked me out of SQL Server and I could not recover it since my administrator group member user account apparently isn't enough of an administrator and the -m/-f tricks weren't working either. So I reinstalled my sql instance three times!