I had an interesting case a while back where a customer’s web site which used SQL Express would sometimes experience this exception:
“System.Data.SqlClient.SqlException: Cannot open database "MyDatabase" requested by the login. The login failed. Login failed for user 'NT AUTHORITYNETWORK SERVICE' ”
But it only happened intermittently which made things all the more strange.
Even worse, in some cases the exception was causing the process to terminate because it happened on a non-request thread. The fact that the unhandled exception caused the process to terminate is well understood and expected:
But the reason the exception was happening in the first place was not.
We enabled extended login logging (only do this if you feel totally comfortable with editing the registry):
Edit the registry and find the key
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServerParameters
In there, create two named value of type REG_SZ named SQLArg3 and SQLArg4. Give them the values -T4029 and -T3689 respectively (including the '-').
If you already have values SQLArg3 or SQLArg4 you will have to name them accordingly. These values specify command line arguments for the SQL instance.
After creating the registry value you will need to restart the SQL Express instance.
We then took a look at the SQL Server Express log files within the "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLog" folder.
It turned out that these login failures were occurring because the database instance had shutdown and the login attempt gave up before it had started up again.
SQL Express is designed primarily for desktop/laptop scenarios so "out of the box" database instances are configured to shut down when the last client connection is closed. (option AUTO_CLOSE is set to ON).
There is a good blog post that describes this behaviour:
You can change this by just executing the following query:
ALTER DATABASE MyDatabase
SET AUTO_CLOSE OFF