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
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
sqllocaldb info MSSQLLocalDB