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.
Best Answer
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.