How to start an SQL Server LocalDB via IIS

Question

I'm using SQL Server LocalDB for a website.

SQL Server LocalDB is installed and a database is created during Setup. A shared instance is created via LocalDBCreateInstance, StartLocalDBInstance and LocalDBShareInstance.

The website can successfully connect to the database if the instance is started.

However, if the instance is stopped, it is not automatically started. Accessing the database (when instance is stopped) from a console application causes the instance to be started.

So this seems to be some sort of permission Problem.

The application pool runs under the AppPool identity. Switching it to Network Service or Local System does not solve the issue, though.

The application pool is configured to load its user Profile and the "setProfileEnvironment" Attribute is also set to true. (Both Settings are required as described here and here).

I've monitored the database access with the Sysinternal Process Monitor but failed to find any "Access Denied" entries.

I've also tried to call StartLocalDBInstance from the Website itself (in the Global.asax). The call returns successfully (return value is S_OK), but the instance is still stopped.

Answer 1

That's the drawback of using LocalDB under IIS - the instance is owned by your interactive login, and the instance process can only be started by this login. Since IIS runs as some service account it cannot start LocalDB instance that belongs to your interactive login account.

You might use a LocalDB instance owned by IIS service account, but be aware that you might run into user profile issues - LocalDB requires the user profile to be created and loaded, while IIS uses lightweight user profiles that sometimes may not be fully created and loaded.

See these two blog posts for more detailed discussion on running LocalDB in full IIS. I would only recommend it for development environment, and use service-based SQL Express for production.

1. Part 1: User Profile
2. Part 2: Instance Ownership