次の方法で共有

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

  • Anonymous
    March 29, 2012
    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 ?

  • Anonymous
    March 30, 2012
    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.

  • Anonymous
    April 01, 2012
    Hello Ward Beattie, I am sorry for my late reply : i have nothing to reply except many thanks....

  • Anonymous
    June 11, 2012
    Am I correct in assuming that this means that LocalDB cannot be started as a service running under the LocalSystem account?

  • Anonymous
    June 11, 2012
    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.

  • Anonymous
    June 11, 2012
    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?

  • Anonymous
    June 11, 2012
    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.

  • Anonymous
    June 20, 2012
    If you're looking to script this.. appcmd set apppool "AppPoolName" /autoStart:true /processModel.loadUserProfile:true /processModel.setProfileEnvironment:true

  • Anonymous
    October 18, 2012
    Just want to say thanks to Josh there for including this as a script.

  • Anonymous
    February 21, 2013
    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?

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

  • Anonymous
    June 15, 2013
    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.

  • Anonymous
    June 20, 2013
    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?

  • Anonymous
    October 08, 2013
    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

  • Anonymous
    December 03, 2013
    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

  • Anonymous
    December 06, 2013
    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."

  • Anonymous
    January 26, 2014
    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 ...

  • Anonymous
    February 09, 2014
    doing this worked for me, i didn' even have to follow the 2nd part www.aspneter.com/.../error-50-local-database-runtime-error-occurred

  • Anonymous
    April 13, 2014
    Perfect, works like a charm! (MVC 5, EF 6.1, Web API 2.0)

  • Anonymous
    June 23, 2014
    The comment has been removed

  • Anonymous
    July 22, 2014
    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.

  • Anonymous
    June 16, 2015
    I spent about 12 hours, before find this post. Thank you very much/

  • Anonymous
    June 27, 2015
    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.

  • Anonymous
    June 29, 2015
    This worked for me and I'm an experienced developer. Thanks mate

  • Anonymous
    July 29, 2015
    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 (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?

  • Anonymous
    November 22, 2015
    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