Deployment of SQL Express LocalDB on IIS

Artigo
12/21/2013

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.

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

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

Compartilhar via

Deployment of SQL Express LocalDB on IIS

Comments

Recursos adicionais