I have SQL Server 2000 installed on the server. I installed SQL Server 2008 R2 express edition as required for new application. The application runs on the same server which is Windows Server 2003 with IIS 6. SQL Express was installed as named instance. I can connect to SQLExpress via Management Studio.
The web application runs without any issues but as soon as it performs an operation which requires db it throws the following error.
Cannot open database requested in login 'dbname'. Login fails. Login failed for user 'NT AUTHORITY\SYSTEM'.
I have searched online for the solution but did not succeed in resolving it.
Here is what I tried.
Set connection string to use windows authentication.
<add name="conn" connectionString="data source=ServerName\SQLEXPRESS; Initial Catalog=dbName; Integrated Security=SSPI; Instance=true" providerName="System.Data.SqlClient"/>
Set connection string to use verified credentials.
<add name="conn" connectionString="data source=ServerName\SQLEXPRESS; Initial Catalog=dbName; User ID=uname; Password=passw" providerName="System.Data.SqlClient"/>
Tested that valid connection string is reflected in IIS => ASP setting
Added user 'NT AUTHORITY\SYSTEM' as db_owner to my database
Checked that NT AUTHORITY\NETWORK SERVICE also has all permissions to my database
Checked that SQLEXPRESS service is actually running