Sql-server – How to configure IIS 10 to read from local SQL database file

asp.net-mvcconnection-stringsiis-10sql server

I have been trying to deploy original ASP .NET Web Application using MVC template with authentication via a local SQL database file. So far, I am able to get it to work on my computer. When I publish it to a folder on a remote server, I can't seem to figure out how to configure IIS 10 to read from that local SQL database file saved to its App_Data subdirectory. I have been getting the following error as a result:

SQL Network Interfaces error 52 – Unable to locate a Local Database Runtime installation. Verify that SQL Server Express is properly installed and that the Local Database Runtime feature is enabled.

I attempt to fix the problem by installing SQL Server 2012 Express on the remote server, but the problem is still there. So I figure the problem might be coming from the connection string:

Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-WebApplication1-20180923034052.mdf;Initial Catalog=aspnet-WebApplication1-20180923034052;Integrated Security=True" providerName="System.Data.SqlClient

The above is what I have on my computer. Since I configure the SQL server as default on the remote server, I figure that I should match up the name of SQL server database engine, which is MSSQLSERVER by default as shown below. It didn't work neither.

Data Source=.\MSSQLSERVER;AttachDbFilename=|DataDirectory|\aspnet-WebApplication1-20180923034052.mdf;Initial Catalog=aspnet-WebApplication1-20180923034052;Integrated Security=True" providerName="System.Data.SqlClient

What configuration am I missing to get this simple ASP .NET web application to work?

Best Answer

In order to get the default connection string that is created with the project to work, you would need to install SQL Server LocalDB on the remote server. It is a separate installation from the main one. Here is the link to where you can download Microsoft SQL Server 2012 LocalDB.

Afterward, setup the directory to where the project is published to use the Local System as its application pool via IIS Manager. To do this as referenced here:

  1. Click on Application Pools in IIS Manager.
  2. Click on Add Application Pool... to create a new application pool. Use whatever setting and name you desire.
  3. With the new application pool selected, click on Advanced Settings... and change the Identity to Local System by clicking on .... It is found in the pull-down menu under Built-in account.
  4. Click OK to save and close all pop up windows and return back to IIS Manager.
  5. Select your directory to where the project is published to and click on Advanced Settings....
  6. Change Application Pool to the newly created application pool and click OK to finish.

Please note that it is not practical to keep the application pool running as Local System as explained here, but it is sufficient for testing purposes.

Related Topic