Deploying an ASP.NET Core application that uses LocalDB to IIS on Windows 10

 LocalDB is intended as a light-weigh development version SQL Server and is not supposed to be used in production.  Similarly, it is not the best version of SQL Server to be used with a web app hosted on IIS. 

Having said that, there are circumstances when a developer may wish to run an ASP.NET Core web app that works with LocalDB on IIS. This post explains one way I got this to work. 

Assumptions: It is assumed the following is running on your Windows 10 computer:

• git
• Visual Studio 2019 (or later)
• LocalDB
• .NET Core 3.1
•  dotnet-ef tool 
• Internet Information Services (IIS)

In this tutorial we will deploy an ASP.NET 3.1 Web API application that uses LocalDB to IIS . As a starting point, you can clone a simple ASP.NET Core 3.1 web application from https://github.com/medhatelmasry/SchoolAPI-3.1.git.

To clone the above application, go to a suitable working directory in a terminal window and run the following command:

git clone https://github.com/medhatelmasry/SchoolAPI-3.1.git

You can then go into the newly created SchoolAPI-3.1 directory with:

cd SchoolAPI-3.1

Let us quickly test this application to make sure that it works. Type the following in a terminal window:

dotnet-ef database update

dotnet run

You should see the following when you point your browser to http://localhost:5000/api/students:

Hit CTRL C to stop the server.

There is a solution (.sln) file in the root directory of the web app. Open this solution in Visual Studio 2019. This can easily be done by typing the following in the terminal window:

SchoolAPI.sln

Once in Visual Studio, edit the appsettings.json file and have a peek at the connection string, which looks like this:

"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=School;Trusted_Connection=True;MultipleActiveResultSets=true"

We will need to do a minor change to this connection string to make it work when our web app is hosted by IIS.

Since we are in Visual Studio 2019, let us run the web application by hitting CTRL F5. We should see the same result as we saw previously when we started the app from a terminal window. 

Install ASP.NET Core Hosting Bundle

For IIS to support ASP.NET Core, you will need to install the “ASP.NET Core Hosting Bundle”. Point your browser to https://dotnet.microsoft.com/download/dotnet-core/3.1. Click on the “Hosting Bundle” link.

A file named dotnet-hosting-3.1.6-win.exe gets downloaded to your computer. Go ahead and run this installer.

Publish web app to IIS

Let us now publish our application to IIS. Create a folder named SchoolAPI under c:\inetpub\wwwroot, which is the the root directory of IIS. 

Back in Visual Studio, right click on the SchoolAPI node in Solution Explorer and select Publish...

On the Publish dialog, select Folder, then click Next.

Use the Browse... button to select the c:\inetpub\wwwroot/SchoolAPI directory, then click on Finish.

 

Click on Publish.

Let us create a website in IIS. Start "Internet Information Services (IIS) Manager". Right-click on the Sites node then select "Add Website..."

Add a website as shown below.

Select the "Application Pools" node then double-click on the SchoolAPI pool in the center.

Select "No Managed Code" in the ".NET CLR version" selection list, then click OK.

Point your browser to http://localhost:8088/api/students. The web application will fail with an HTTP ERROR 500. Unfortunately, there is not enough information to tell us what the problem is. I will describe to you two simple ways of finding out what the problem is:

1) Start the Windows "Event Viewer". Select "Windows Logs" >> Application, then read the first couple of errors.

You will come across this error, which suggests that the app is unable to connect to SQL 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. 

2) To see a more descriptive error message in your browser, you can also set the environment variable ASPNETCORE_ENVIRONMENT to Development. Add this to the C:\inetpub\wwwroot\SchoolAPI\web.config file inside the <aspNetCore … > section. Once you do so, your < aspNetCore…> block will look like this:

<aspNetCore processPath="dotnet" arguments=".\SchoolAPI.dll" 

  stdoutLogEnabled="false" stdoutLogFile=".\logs\stdout" hostingModel="inprocess" >

  <environmentVariables>

    <environmentVariable name="ASPNETCORE_ENVIRONMENT" value="Development" />

  </environmentVariables>

</aspNetCore>

Save the web.config file, then refresh the page in your browser. A more detailed error message will display.

One way to fix the database connectivity problem

I have one solution to this problem so that IIS can communicate with LocalDB. I welcome any other suggestions. 

From within a terminal window, execute the following commands:

sqllocaldb start MSSQLLocalDB

sqllocaldb info MSSQLLocalDB

You should experience output similar to the following:

C:\>sqllocaldb start MSSQLLocalDB

LocalDB instance "mssqllocaldb" started.

C:\>sqllocaldb info MSSQLLocalDB

Name:               mssqllocaldb

Version:            13.1.4001.0

Shared name:

Owner:              bingo

Auto-create:        Yes

State:              Running

Last start time:    2020-08-13 11:33:00 AM

Instance pipe name: np:\\.\pipe\LOCALDB#1961D8A7\tsql\query

We can use the "Instance pipe name" as the name of the server in the database connection string. Copy the value of the "Instance pipe name" without 'np:'. In the above example it would be '\\.\pipe\LOCALDB#1961D8A7\tsql\query'. Go to Visual Studio 2019, open appsettings.json and paste contents of your clipboard instead of '(localdb)\\mssqllocaldb' in the connection string. In my case, the connection string looked like this:

"DefaultConnection": "Server=\\\\.\\pipe\\LOCALDB#1961D8A7\\tsql\\query;Database=School;Trusted_Connection=True;MultipleActiveResultSets=true"

NOTE: When you pasted the name pipe, all \ are escaped to \\. This is OK and do not be alarmed.

If you run the web application in Visual Studio, it should be working as expected:

Now, let us do the same change to C:\inetpub\wwwroot\SchoolAPI\appsettings.json. You can simply replace the connection string in C:\inetpub\wwwroot\SchoolAPI\appsettings.json with the connection string from appsettings.json in Visual Studio 2019.

Thereafter, point your browser to http://localhost:8088/api/students. This time, we will get a different error:

SqlException: Login failed for user 'IIS APPPOOL\SchoolAPI'.

This error suggests that, although the database was found, IIS failed to login to the database using account 'IIS APPPOOL\SchoolAPI'. To fix this problem, we will get IIS to use a different account. Select the SchoolAPI application pool, then click on 'Advanced Settings..." on the right-side.

Click on the ... beside Identity.

Change the "Built-in account:" to LocalSystem, then click on OK followed by OK again.

By changing the account to LocalSystem, IIS will try and login to our database using a built-in account named 'NT AUTHORITY\SYSTEM'. We need to give this account access to our School database in LocalDB. 

Download and install SQL Server Management Studio (SSMS) from https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.

Start SSMS. Use "Windows Authentication" to login into server name "(localdb)\MSSQLLocalDB".

Right-click on Security >> Logins and select "New Login...".

Click on Search...

Enter 'system'.

Click on "Check Names". When the account is found, you will see SYSTEM in uppercase and underlined.

Click on OK. Next, select the "User Mapping" node on the left side, check the School database, then check db_owner at the bottom.

Click on OK to close the Login - New dialog.

Browser to http://localhost:8088/api/students. The page should display data as expected.

NOTE: If you receive an error suggesting that the database cannot be found, then run these command again in a terminal window and update the "Instance pipe name" in the appsettings.json connection string.

sqllocaldb start MSSQLLocalDB
sqllocaldb info MSSQLLocalDB

As I said before, this is really an academic exercise and that LocalDB should never be used in production. SQL Express is a much better option.