Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

Please stop using the user instance=true setting. Not only has this feature been deprecated, but what is actually happening here is that Management Studio is firing up a different user instance of SQL Server than the one your application is using, so it is not surprising that you can't see the same thing in both cases.

(Usually, the symptom of this is that someone runs an update from their app, then checks the table in Management Studio, thinking there is a bug but not realizing that the update affected a different copy of the database.)

Create the database on your running SQL Server instance (or attach it there). Then connect to that server the same way, without the User Instance setting, from both SSMS and your app. Lots more information here. Don't create the database every time the application starts - that just sounds horrendously wrong.

share|improve this answer
1  
Thanks for the info. I've stopped using User Instance=true and you helped me to better understand what it was doing. The database isn't created each time the application starts, it only happens when the app starts and sees that the database does not exist (typically only the first time it starts). –  elevine Dec 13 '13 at 17:19
add comment

If you are logged on to the server can you see how many sql services there are in the services windows. Perhaps there are more than one instance.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.