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

Comments

  • Anonymous
    December 28, 2013
    Very interesting and useful

  • Anonymous
    December 28, 2013
    Excellent article. So you deserve a big "Cheers!". A little reproach with your link about How to start an instance of SQL Server (sqlsrv.exe) : it is not related neither to SQL Server 2012 or 2014 ( but it is very useful at least for me ). There is no real equivalent for the current version, but it is not your problem , only the problem of the SQL Server Documentation Team who has not "recreated" or added a page related to the 2012 version ( I think I will post only the SQL Server Documentation Forum about this omission )

  • Anonymous
    January 03, 2014
    Thanks for the feedback Leonardo and Papy. Hope SQL Server Documentation Team releases the SQL Server 2012 documentation soon.

  • Anonymous
    February 26, 2014
    setProfileEnvironment is an Unrecognized attribute in windows server 2008

  • Anonymous
    April 12, 2014
    Thanks for your directions! It works like a charm.

  • Anonymous
    April 15, 2014
    Steve, Yes, setProfileEnvironment is an Unrecognized attribute in windows server 2008 and this solution may not work there. Thanks Gaurav

  • Anonymous
    August 12, 2014
    What to do  if I get the following error Error 28 Web deployment task failed. (Failed to create type 'Microsot.SqlServer.Dac.DacServices'.  Learn more at: go.microsoft.com/fwlink.) 0 0 MuruganEngineering

  • Anonymous
    September 03, 2014
    Thanks for posting it. I followed the same steps in order to run my ASP.NET MVC application but I had to do an extra step : The Application Pool Identity was changed to use LocalSystem. After changing the identity and recycling the pool, it worked like a charm.

  • Anonymous
    November 05, 2014
    loadUserProfile=true did it for me! thanks!

  • Anonymous
    October 24, 2015
    The setting has to be modified in  applicationHost.config, which normally is C:WindowsSystem32inetsrvconfig. This vital information is never mentioned. Not every user will know this.

  • Anonymous
    April 21, 2017
    I have followed these step on my development machine (windows 8.1), every thing works fine but when I deploy on the customer machine (windows 7), i got the same error again and again. Please suggest to resolve this issue. thanks in advance