Using LocalDB with Full IIS, Part 1: User Profile

  • Article

This is the first post in two-post mini-series on using LocalDB with Full IIS. If you found it useful make sure to continue to the second post.

LocalDB and Full IIS

In this recent post I described how to use SQL Server Express LocalDB with Visual Studio 2010. The approach I proposed works for all kind of projects, including Web Applications. However, when it comes to Web Application, there is a catch. An application hosted in Cassini or IIS Express will work as expected, but as soon as we try running it in full IIS (the regular IIS that comes with Windows and runs as Windows service) we are facing the following error:

What Went Wrong?

There are two properties of LocalDB that cause problems when running under full IIS:

  • LocalDB needs user profile to be loaded
  • LocalDB instance is owned by a single user and private (by default)

We will focus the rest of this post on the user profile, leaving the issue of LocalDB instance ownership for the next post.

Loading User Profile

Let's take another look at the error:

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 0 - [x89C50120])

The error message is not very helpful but LocalDB stores additional information in Windows Event Log. Looking in the Application section under Windows Logs we find the following message:

Windows API call SHGetKnownFolderPath returned error code: 5. Windows system error message is: Access is denied.
Reported at line: 400.

Followed by this one:

Cannot get a local application data path. Most probably a user profile is not loaded. If LocalDB is executed under IIS, make sure that profile loading is enabled for the current user.

Most likely there will be several copies of these two errors, as ADO.NET connection logic attempts to connect multiple times at different intervals:

The second message is clear, the problem we're facing is that the user profile needs to be loaded. That shouldn't be hard since each IIS Application Pool has an option called Load User Profile that can be found in Advanced Settings section. Unfortunately things got slightly more complicated in Service Pack 1 for Windows 7. As described in KB 2547655 enabling loadUserProfile is not enough to fully load user profile, we also need to enable setProfileEnvironment. This requires editing applicationHost.config file which is usually located in C:\Windows\System32\inetsrv\config. Following the instructions from KB 2547655 we should enable both flags for Application Pool ASP.NET v4.0, like this:

<add name="ASP.NET v4.0" autoStart="true" managedRuntimeVersion="v4.0" managedPipelineMode="Integrated">
<processModel identityType="ApplicationPoolIdentity" loadUserProfile="true" setProfileEnvironment="true" />
</add>

Having completed that we restart the Application Pool to make sure the new settings are applied and run our Web Application again. If everything went as expected we should be ... faced by a new error:

There's no reason to panic, as this error is fully expected. After all we haven't dealt with the second problem with running LocalDB under full IIS. We still need to address the fact that by default LocalDB instances are private, which means that IIS, running as ApplicationPoolIdentity, will not have access to our LocalDB instance, running as our Windows account. We will explore different ways to address this problem in the second post from this mini-series on using LocalDB under full IIS.

- Krzysztof Kozielczyk

Share Your Feedback

Please share your feedback with us! Just start a thread on SQL Express Forum, hit the "Email Author" button on this post, or file a Connect item!