2

First off, I'm pretty sure I've found every related post on the entire internet, so please read carefully before marking as a duplicate.

I have an ASP.NET site, hosted with IIS, using SQL Server Express. It's set to use Windows Authentication, and I'd prefer it to stay that way (although, at this point, I'm not picky). All three programs/services are on the same machine.

There are two databases: one for site logins, another for site data. The logins one works fine, yet both are set up identically.

The user in question has db_owner membership under the database in question. This is the Windows user that corresponds to the ApplicationPoolIdentity option in IIS.

Error:

Cannot open database "EDB-Content" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\EDB'.

Connection strings:

name="LoginDB" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=aspnetdb;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"

name="ExcDbContext" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=EDB-Content;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"

Stack trace:

https://pastebin.com/embed_iframe/RQN3uwUM


I added the login/user via SSMS, which had no problems with the space. I don't even know how I'd add quotes/brackets.

I have checked the SQL Server error log before, but this time I dug deeper. I restarted SQL Server and checked to see if everything is starting up correctly. It seems the EDB-Content database never gets started:

log extract

Any idea what's going wrong there?

In sys.databases the state_desc of that database is ONLINE.

The article Allowing IIS 7.5 Applications to communicate with SQL server via Windows Authentication by Giles Middleton details exactly as I've already done.

IIS APPPOOL\EDB is the Windows user.


Non-exhaustive list of things I've tried: (will update as I recall/try things)

  • Confirmed that the login can connect to the server (GRANT CONNECT TO SQL <login>)
  • Added and removed the login from database and global levels
  • Restarted just about everything that can be restarted (including
  • Wrapped the DB name in quotes in connection string (&quot;)
  • See update below
  • Confirmed that "LoginDB" (aspnetdb.mdf) is indeed being read/written to

Update

I've narrowed down the problem, but not why it's a problem. If I detach and delete the database and change the Initial Catalog and AttachDbFilename values in the connection string, a new database is generated next time I run the website that works fine, even down to the database file being in App_Data as it's supposed to. However, oddly, it is not attached to SQL Server. If I attach it myself, it does so without complaint, but subsequent runs of the website generate the above error. If I detach the DB, the website still throws an exception. The only fix I've found is to create a whole new database by changing Initial Catalog and AttachDbFilename.

At this point my connection strings are as follows:

name="LoginDB" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=aspnetdb;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"

name="ExcDbContext" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=contentdb;AttachDbFilename=|DataDirectory|\contentdb.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.