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
.
If you are receiving the error message “Cannot open database. It may not be a database that your application recognizes, or the file may be corrupt.”, check if the database has reached or exceeded the 2 GB limit. If so, running MS Access built-in 'compact and repair' utility may help.
Right click on the database server and go to properties. Choose the security option and check "SQL Server and Windows authentication mode". Enable TCP/IP connection in SQL Configuration Manager. Restart your SQL server.
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".
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With