I have an ASP.NET MVC web application deployed to IIS on a Windows Server 2012 machine. When the application first starts, it will create the database and tables it needs if necessary through Entity Framework. This all works fine, and now I want to do some administration to the database through SQL Server Management Studio. However, when I open up that tool I only see the System Databases. If I remove "User Instance=true" from the connection string and let the application create the database again, I can the see the database.
Why couldn't I view the database created by the web application when User Instance=true was in the connection string? If I am viewing the server instance through Management Studio with an account that has all roles, I would expect it to be able to see any and all databases.
Some details that may help:
The application uses the following connection string in Web.config:
<add name="DefaultConnection" connectionString="Data Source=.\SqlExpress; Initial Catalog=Foo.Dashboard; Integrated Security=SSPI;User Instance=true" providerName="System.Data.SqlClient" />
In IIS, the application pool is set to run with the identity
LocalService and the
Load User Profile setting is
True. On SQL Server Express, we added a login for the
Local Service account and gave it the
dbcreator role. When the application started up, it was then able to create the database and tables, and everything appears to be functioning.
I then connect to the server with remote desktop and open SQL Server Management Studio. To connect to the database, I enter
.\SQLEXPRESS as the server name and authenticate with
Windows Authentication. I then connect and only see the system databases. I’ve added all the possible database roles to my login, and I see that I have the
VIEW ANY DATABASE permission in the effective tab.