SQL Server 2008 login problem with ASP.NET application: Failed to open the explicitly specified database

Question

I am running SQL Server 2008 Express Edition on Windows Server 2008 with an ASP.NET application which must access the server. The ASP.NET application is associated with an application pool that runs on the NetworkService account. This account in turn has a Login and User record on SQL Server in the required database. When I attempt to run the ASP.NET website I get a blank page and when viewed in the error log, I seem to be getting this information event record:

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: myLocalMachine]

The connection string has Trusted_Connection=True; and the required database specified.

When I explicitly specify the user name and password I get another login error stating the password is incorrect, even though the same un/pw combination works through SQL Server Management studio. The NETWORK SERVICE account seems to have all the required privileges for the database. Also, I made a test ASP.NET website project which does a simple select from a table in that database, and using the same config file I am not getting the error and it seems to work.

Is it something to do with trust levels then, because the original ASP.NET web app references various DLLs including open source libraries. Also, the application does not seem to be able to write to the event log itself, throwing a security exception, even though everything in the config files, including machine.config states the app is in full trust.

Solution 1

I chased this error around for an hour or more. I tried many things including Sql Server configuration, adding the Network Service user and others.

You need to make sure that the database specified in the connection string matches the one that you have granted permissions to the user for.

However, in the end the issue I had was that my database in my connection string had a typo - a l and an i were transposed, which was hard to pick up in the resolution I was using.

So my suggestion here is : find another application that allows you to test a connection string, and test the connection string.

Solution 2

If the database instance is on a different server than the web application then you will need to add a SQL login for DOMAIN\WEBSERVER$ and then grant it access to the proper database.

Solution 3

I would test with an explicit account that I set up on the local computer and put into the IIS app as the anonymous user. That way you can test it from SSMS or VS and be sure that you can connect to the server and database without issue.

Once that is working, or if it's not, you can debug where in the ASP side it might be failing. The SQL side is simple, though I've not tried the Network Service account. Should be no reason it doesn't work, but I haven't seen anyone use it.

Solution 4

For the security acct in question on SQL Server, is the "default database" set to the appropriate database or is it still listed as the default "Master" database?o

Solution 5

Just off the top of my head, would the in the web.config file possibly be a trust level problem? Also, is the SQL Server in Windows authentication mode or Mixed authentication mode? Is SQL Server configured for TCP/IP? I think in SQL Server 2008 TCP/IP is disabled and uses Shared Memory or Named Pipes by default.

View more solutions

Comments

[user1124702]

Can you paste in your connection string?

[mmdemirbas]

SQL Login Failures and SQL Server Default Trace pages are very helpful for me.

[Murph]

I prefer integrated auth, but a not dissimilar pattern - create a computer/AD user for web applications (or per set of applications if appropriate), set the app pool to run with that user and then explicitly auth that within SQL Server. I'd also choose to have an explicit role in each database to add the user too rather than just generically assigning rights.