Using LocalDB with Full IIS, Part 1: User Profile


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!


Comments (26)

  1. Papy Normand says:

    I have used IIS so rarely that i am sure i will write an enormeous error : when you are writing "full IIS", do you mean that it is not working with IS 7.5 ?

  2. wardbeattie@hotmail.com says:

    Thanks for your question. By "Full IIS", Krzysztof is referring to the IIS production-capable feature of Windows Server. For Windows 7 and Windows Server 2008 R2, this is IIS 7.5. We call it "Full IIS" to distinguish it from IIS Express, which is a light-weight developer version of IIS that runs in the user's security context.

  3. Papy Normand says:

    Hello Ward Beattie,

    I am sorry for my late reply : i have nothing to reply except many thanks….

  4. Sergio says:

    Am I correct in assuming that this means that LocalDB cannot be started as a service running under the LocalSystem account?

  5. wardbeattie@hotmail.com says:

    LocalDB does not run as a service itself. It runs as a background process in the context of the user that starts it. That said, you could write an application that itself runs as a service and uses LocalDB ….

    Ward.

  6. Sergio says:

    I have the following scenario:

    A WCF Service Application runing as service under the LocalSystem account, and accessing a "Customers" database.

    At the same time I have other applications running under the current user access the "Customers" database, some through the WCF service, and some access the database directly.

    Would LocalDB handle this type of scenario?

  7. wardbeattie@hotmail.com says:

    Hi Sergio,

    You can use LocalDB in this scenario (although running any application as LocalSystem is discouraged … but this is another subject).

    In this design, your WCF service is responsible for keeping its LocalDB instance running. It can do this by maintaining a connection, or by configuring LocalDB (sp_configure) to run forever.

    To allow a local user to access the database directly, you can use LocalDB's "shared instance" feature (see blogs.msdn.com/…/using-localdb-with-full-iis-part-2-instance-ownership.aspx).

    Note that LocalDB does not support remote users for direct access to the database. You can, of course, support remote users via your WCF application service.

    Ward.

  8. Josh says:

    If you're looking to script this..

    appcmd set apppool "AppPoolName" /autoStart:true /processModel.loadUserProfile:true /processModel.setProfileEnvironment:true

  9. Matthew Steeples says:

    Just want to say thanks to Josh there for including this as a script.

  10. Nate says:

    Any idea what I should do next if after making the specified changes to the applicationHost.config file, my error message is:

    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: 50 – Local Database Runtime error occurred. Error occurred during LocalDB instance startup: SQL Server process failed to start.)

    Instead of the 'Cannot open database' bit that you told me to expect?

  11. Valentin says:

    I've got the same problem as Nate here. After making the changes, the error is still the same. Any idea?

  12. Jack Bond says:

    So the entire point of LocalDb was to make it EASY for developers and yet issues like this come up. I'm having a similar problem trying to use LocalDb in an Azure role. Here's EASY…

    1) Give logical connection string

    2) Call Open

    3) It works

    Forget all the sharing nonsense, automatic instances, and security. They should provide a mode where it just works. MASSIVE FAIL.

  13. cestes001@msn.com says:

    If anyone is still listening at MSFT, this DOES NOT work for Windows Server 8 SP2 with IIS 7.  The attribute setProfileEnvironment is "unrecognized".  

    What gives?

  14. taylor says:

    I also wasn't able to get past the first error with these steps. the solution was to follow these steps included in 3-13. Setting Up a Shared Instance of LocalDB, in the ebook link below.

    In summary the steps were

    1. Create a shared LocalDB Instance.

    2. Modify connection string in your web.config.

    3. create a login for IIS in the localDB instance.

    books.google.com/books

  15. Michael Dereje says:

    Hey Krzysztof,

    This was a good informative post thank you! However it worked on my lap top which is windows 7, 32 bit but when i try the solution on a windows 64 desktop that uses windows 7 professional sp1. The error will not go away. Any ideas to why that is?

    Thanks,

    Michael

  16. Rafael says:

    Ive enabled the "loadUserProfile" and enabled "setProfileEnvironment." but the application still not working.

    Always the same error:

    "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: 50 – Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details."

  17. Lyne says:

    Any news on that issue ?? I have the same stoopid error as you, despite doing what is suggested above.  This only works if I start a new MVC project. As soon as I try to create another database and connect on it, it won't work.  I cannot even try to connect the project on a simple SQL SERVER instance. -_-  Easy they said …

  18. themien says:

    doing this worked for me, i didn' even have to follow the 2nd part

    http://www.aspneter.com/…/error-50-local-database-runtime-error-occurred

  19. Andrej says:

    Perfect, works like a charm! (MVC 5, EF 6.1, Web API 2.0)

  20. Luke Ballantine says:

    Excellent article – and the follow up! Saved me some head scratching.

  21. Helobis Lukasz Jaskiewicz says:

    The trick is to use the code first approach and let Entity Framework create a new database on the first run on the production server where the full version of IIS is running.

    If you deploy your web application including an already created LocalDB from your development server, the LocalDB will still have the logon and user settings from your development server and therefore the described exceptions will come up.

    There is no need to change the user identity of the AppPool.

  22. Anonymous says:

    I spent about 12 hours, before find this post. Thank you very much/

  23. Anonymous says:

    I believe I have found a reason why the loadUserProfile and setProfileEnvironment does not work on some systems.

    I did some digging, and found that the problem lies with the C:WindowsSystem32inetsrvconfig directory. It seems that Windows keeps two versions of this directory. One for 64bit processes, and the other for 32bit.

    That means if you tried to edit the applicationHost.config file using a third party text editor, which happens to be 32bit, the 64bit IIS will never see the changes that you made. Try making the edits using regular Notepad.

    I also found that I had to fully restart the computer, not just Service / IIS / App Pool, for the changes to manifest.

  24. Anonymous says:

    This worked for me and I'm an experienced developer. Thanks mate

  25. Anonymous says:

    I kept getting the error even after changing the settings on for "loadUserProfile" and "setProfileEnvironment."

    "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: 50 – Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details."

    I had to follow the second part (http://www.aspneter.com/…/error-50-local-database-runtime-error-occurred) to get it working!

    Not sure why changing –  identityType="NetworkService" works for some and is not needed for others, any thoughts? Will there be issues with changing this?

  26. vishal says:

    hey thank you for posting this..

    but i need your help .

    When i m try to find configuration folder at system32/inetsrv this path, I count find it.

    please reply me as fast as possible way to solve this problem