Saturday 11 June 2016

[LocalDB] Connecting to LocalDB failed for Web Application Hosted in IIS [Error Code 50]

In my previous blog post http://jaryl-lan.blogspot.my/2014/08/localdb-connection-to-localdb-failed.html. If you deploy your application to a client machine or server without installing the local database runtime, you will hit with an error of not able to access your local database file. But what if your application is a web based application that are hosted in IIS, you might hit with another error as shown below,

Unable to access SQLLocalDB: System.Data.SqlClient.SqlException (0x80131904): 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. )

Based on the above error message, it will be tough to identify what is going wrong but at the bottom of the message specifies that you can get more information in the Windows Application event log. So open up the event viewer and navigate to Application log, you will notice a few duplicate events shown as follows.

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

Event 2:
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.

Event 3:
Unexpected error occurred while trying to access the LocalDB instance registry configuration. See the Windows Application event log for error details.

From the events, the keyword here is "user profile is not loaded". So to get the user profile loaded, do the following:
(1) Open IIS Manager.
(2) On the left pane, navigate to Application Pools.

IIS Manager - Application Pools


(3) Select the Application Pool used by your application, right click on it and click "Advanced Settings...". A window titled "Advanced Settings" will be prompted.

IIS Manager - Application Pools - Advanced Settings


(4) Look for Load User Profile under Process Model section and set its value to True.

IIS Manager - Application Pools - Advanced Settings - Load User Profile

(5) Recycle your Application Pool.

IIS Manager - Application Pools - Recycle

Once done, rerun your web application and it will be able to access the localdb. If you noticed that your application loads very slow on the first load, it is due to the localdb instance has not fully started yet. To identity which instance launched by your web application, navigate to Details tab in Task Manager, locate the instance sqlservr.exe with the username is same as your application pool's name (That is if your application pool identity is set to ApplicationPoolIdentity).

Source: https://blogs.msdn.microsoft.com/sqlexpress/2011/12/08/using-localdb-with-full-iis-part-1-user-profile/




4 comments:

  1. I was under the impression that LocalDB is not the db to use for remote applications and that SQL Server/Express may be the choice for small applications - It surprises me that it is used for a web application (unless it is a single user web application, which would be another strange architecture)!

    ReplyDelete
  2. LocalDB normally is used for development and testing purposes. Since it is very fast to install/setup in just a couple of seconds.

    It is still advisable to use SQL Server Enterprise / Express edition for production environment depending on the type of application you have developed.

    ReplyDelete
  3. Thanks this helped me a lot

    ReplyDelete