0

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?

Ice Drake
  • 25
  • 1
  • 1
  • 9
  • You simply wrote the wrong connection string, https://msdn.microsoft.com/en-us/library/jj653752%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396 LocalDB and SQL Server Express have their own patterns, while `.\MSSQLSERVER` is for something else. – Lex Li Sep 25 '18 at 12:00
  • Thanks. That leads me onto the right path. Apparently, installing SQL Server 2012 is pointless. I just need to install SQL Server LocalDB, a separate installation from the main one. Afterward, I need to configure the Application Pool to use the Local System as referenced [here](https://stackoverflow.com/questions/22737373/how-to-deploy-asp-net-mvc-4-application-using-localdb-to-local-iis-on-windows-7). After that, it works fine with the original connection string. – Ice Drake Sep 26 '18 at 07:12
  • Post what you learned as an answer and accept it. – Lex Li Sep 26 '18 at 15:57

2 Answers2

1

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.

Ice Drake
  • 25
  • 1
  • 1
  • 9
1

What i did was to add the appool as a user to SQL Server. Namely, at the SQL Server level, go to security. Add new login. Add IIS APPPOOL\APPPOOLNAME as a user. Do not do check user or anything. Accept defaults and add the appool user to SQL Server. Give this user the read/write permissions on the database and that's it. MSDN BLOG reference

sam
  • 111
  • 2