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.
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.
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.
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.
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
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.
Not an answer, but possibly a workaround, when I'm connecting to SQL Server from a web application I always use SQL authentication not integrated. I also use different SQL logins for reading data and writing data i.e. when pages have to write data I use an account with write access and for all other pages I use an account with only read access. This seems to me more secure, and because I can use different logins for different apps I can keep track of what app is using the database.
JR