CODEWITHSUNDEEP
sql-serveriisasp.net-web-apiasp.net-core
rejy11
rejy11

Reputation: 752

ASP.NET Core Web Api publishing to IIS producing an Sql Server connection error

I've created a simple web api in asp.net core 2.0 which uses localdb as the backing datastore. It works completely as expected in debug - I'm able to send requests from postman (or browser) and step through my code. Here is my connection string:

"Server=(localdb)\\MSSQLLocalDB;Database=UniversityDatabase;Trusted_Connection=True;MultipleActiveResultSets=true"

I now want this to be running constantly so that I can consume it from a Xamarin.Forms project so I have tried publishing the web app using the Visual studio wizard.

1.

Screen Shot

2. Screen Shot

UniversityApi then appears in IIS under Default Web Site but not as an application. I choose to 'Convert to Application' which shows me this dialog:

3.

Screen Shot

The NetCoreApps Application Pool uses No Managed Code.

Error: 

{
"error": [
    "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.\r\n)"
]}

The error seems to describe a problem with connecting to my Sql Server instance which is strange because it works perfectly while debugging in VS. Any ideas?

Upvotes: 1

Views: 5866

Answers (3)

rpocfemia
rpocfemia

Reputation: 66

Thanks a lot for the answers. I have similar problem. My .net core web api is hosted in AWS. It was working before when I was just accessing the api via ip address (sample https://xx.xx.xxxx/swagger) but when I integrated it to domain (https://xx.sampledomain.com/swagger) I ran into the same problem. These two steps solved the error.

  1. 1st I used the LocalService account for IIS Application Pool, then added the NT Authority/Local Service account in the MS SQL Server 2016.

enter image description here

  1. On the Publish screen > Database > DefaultConnection, I ticked on the 'Use this connection string at runtime' checkbox.

enter image description here

Upvotes: 0

AlbertLim
AlbertLim

Reputation: 1

  1. Add the "LOCAL SERVICE"(Windows account) to SQL Server.
  2. Change from "ApplicationPoolIdentity" to "LocalService" in (IIS Application Pools Advanced Settings)

NOTE. ADD "LOCAL SERVICE"(Windows account) need Permission into SQL Server.

Upvotes: 0

rejy11
rejy11

Reputation: 752

After searching for a while I stumbled onto this question:

SQL won't connect after deploying

One of the answers did the trick for me, credit to Robotron.

  1. IIS Manager
  2. Application Pools
  3. Find the pool your app belongs to (for me it was .NET v4.5)
  4. Right click -> Advanced Settings
  5. Scroll down to Identity
  6. Change from whatever (for me it was ApplicationPoolIdentity, same as you have) into LocalSystem.

Upvotes: 2

Related Questions