SQL Server: Cannot open the database requested at login

Question

When debugging an ASP.Net MVC application running under IIS Express and trying to use the membership provider, I get the following error:

Cannot open the database "MyDB" requested at login. Login failed.

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

I tried to troubleshoot using the steps described in

stack overflow

SELECT SUSER_ID('MY-PC\MyName') 

returns identifier.

 SELECT USER_ID('MY-PC\MyName') 

returns null

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

returns an error message

The account already has an account with a different username.

Indeed, there is a login account automatically created when you create a database called

DBO

which is displayed on MY-PC\MyName .

My connection string

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

Running in a production environment, I would know how to create the appropriate users and logins. I am stuck on how to decide that IIS Express uses my Windows account name to try to enter MyDB when this Windows account is already associated with dbo .

Answer 1

It turned out that this is a typo in the part of the source directory of the connection string.

In the event log I saw an error, for example

Login failed for user "MY-PC \ MyName". Reason: Failed to open explicit database. [CLIENT:]

Then, looking at the details tab for the same event, I saw that the binary data in bytes contains

 0000: 18 48 00 00 0E 00 00 00 .H...... 0008: 17 00 00 00 45 00 52 00 ....ER 0010: 49 00 43 00 2D 00 4F 00 IC-.O. 0018: 52 00 49 00 47 00 49 00 RIGI 0020: 4E 00 5C 00 53 00 51 00 N.\.SQ 0028: 4C 00 45 00 58 00 50 00 LEXP 0030: 52 00 45 00 53 00 53 00 RESS 0038: 00 00 07 00 00 00 6D 00 ......m. 0040: 61 00 73 00 74 00 65 00 aste 0048: 72 00 00 00 r... 

Notice how the last part of UTF-16 encodes the word "master". This led me to make the connection in the main directory, not the requested one, which in turn would lead to a typo in the directory name in the connection string.

It would be much nicer if Microsoft just showed an error, for example, "There is no such directory: MyCatalog."