Deployment of SQL Express LocalDB on IIS

This blog might be helpful if you are planning to deploy or have deployed LocalDB on the IIS server and running into issues. In recent times, we have been using SQL Express with ASP.NET websites as a light weight version of SQL Server when we don’t need enterprise level features. If you noticed the title of the blog i have written SQL Express LocalDB, so questions are what is SQL Express LocalDB? and how is it different from SQL Express?

 

What is LocalDB?

Let’s first talk about LocalDB. SQL Server Express LocalDB is a lightweight version of SQL Server that you can think of in many of the same ways as a SQL Server database. SQL Server Express LocalDB runs in user mode and you can install it more quickly since it requires no configuration. LocalDB is created specifically for developers and It is very easy to install and requires no management. It offers the same T-SQL language, programming surface and client-side providers as the regular SQL Server Express. Developers don’t want to concentrate on the management part of the SQL server rather they just want to concentrate on programming part and business logic. So If there is no need of other features of full version of SQL Server, you can continue using SQL Server Express or LocalDB in the production environment as well.

We can connect to localDB by specifying (localdb)\v11.0 in server name. Here is how it would look in Sql Server Management Studio after connecting to the LocalDB database.

 image          image 

Now there is a confusion. What is difference between SQL Express and SQL Express LocalDB? LocalDB is not a replacement for SQL Express rather it is just an addition to SQL Server Express lineup. While LocalDB is meant for developers, SQL Server Express is still a free SQL Server edition and is easily upgradeable to higher SQL Server editions.

If we talk about the differences at the core level than as such there is no difference. LocalDB is yet another instance of the same SQL server executable sqlservr.exe and it can be found at following path C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\ . This path will be different depending on the version of the SQL Express installed. 

You can think of that localDB is launched by running the following given command.

 sqlservr.exe -m -s LocalDB
  
 For that matter, we can use same command to run the sqlservr.exe as a different instance name like  “SQLExpress”  or “localDB” or  “GauravDB”  etc. In the above command, “–m” switch makes sure that this instance in running in single user mode. For more understanding 
 on this command, please refer to this article How to: Start an Instance of SQL Server (sqlservr.exe) 
  
 For using LocalDB in ASP.NET application, like a normal SQL Server connection string, we just need to specify 4 basic required settings, ServerName, DatabaseName, UserName  and Password. I used following connection string in my application. For understanding the difference 
 I have specified connection strings for LocalDB and SQLExpress respectively with that of LocalDB in bold. So we can see that there is as such no difference in both the connection strings except the instance name. 
  <connectionStrings>
 
    < add name="LocalDBConnection" connectionString="Data Source=(LocalDB)\V11.0;Initial Catalog=SampleLocalDB;Integrated Security=SSPI; 
     AttachDbFilename=|DataDirectory|\SampleLocalDB.mdf" providerName="System.Data.SqlClient"   />  
   
     <add name="SQLExpressConnection" connectionString="Data Source.\SQLEXPRESS;Initial Catalog=SampleEXPRESSDB;Integrated Security=SSPI;
     AttachDbFilename=|DataDirectory|\SampleLocalDB.mdf" providerName="System.Data.SqlClient" />
    
    </connectionStrings>

 

Deploying LocalDB on IIS Server 

We can use simply localDB in our web application just like any other database but when we deploy our application on the IIS Server, we may see following error message when using LocalDB on IIS web server.

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.
)

image

 

Actually, we see above error when application is not able to find the database i.e. database is not reachable. So what is happening here? You would have noticed one thing while discussing about LocalDB database. I said LocalDB runs in user mode and here is the key in this statement. Now if you will check Windows application event logs, we can see following logs there.

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

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.

Second event log is self explanatory that we require to load the profile settings for the IIS worker process. Now only setting loadUserProfile="true" will not help and we will also require to set setProfileEnvironment="true". So our application pool settings should look like following.

 

  <add name="DefaultAppPool">
        <processModel identityType="ApplicationPoolIdentity" loadUserProfile="true" setProfileEnvironment="true" />
 </add>
 If you need more details about this issue you would like to go through these blogs which provides much more details about this particular issue. 
 https://blogs.msdn.com/b/sqlexpress/archive/2011/12/09/using-localdb-with-full-iis-part-1-user-profile.aspx
 https://blogs.msdn.com/b/sqlexpress/archive/2011/12/09/using-localdb-with-full-iis-part-2-instance-ownership.aspx
  
 Here are some other related blogs which can help you in understanding more about LocalDB.

Local Data Overview https://msdn.microsoft.com/en-us/library/ms233817.aspx

SQL Server 2012 Express LocalDB https://technet.microsoft.com/en-us/library/hh510202.aspx

Introducing LocalDB, an improved SQL Express https://blogs.msdn.com/b/sqlexpress/archive/2011/07/12/introducing-localdb-a-better-sql-express.aspx

LocalDB: Where is My Database? https://blogs.msdn.com/b/sqlexpress/archive/2011/10/28/localdb-where-is-my-database.aspx

 

 Hope it helps. Please let me know the feedback. 
 Thanks