2

I have a .NET Core web application that fetches some data from a SQL Server. I connect to the SQL Server over the internet.

When I run the application with IIS Express or Kestrel, I can connect to the database server and retrieve data. I can also successfully connect to the database server using SQL Server Management Studio.

Here is the connection string I'm using:

"ConnectionStrings": {
    "DefaultConnection": "Server=my-sql-server.someurl.com; Initial Catalog=my-sql-database; User ID=my-user-id; Password=my-password;"
}

But when I try to run the same website, on the same machine, in IIS, I get the following error message:

SqlException: 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: TCP Provider, error: 0 - No such host is known.)

I'm suspecting this has something to do with the dotnet core/IIS combination, and not a firewall/network/authentication issue, but I might be wrong.

Bjarte Aune Olsen
  • 3,230
  • 4
  • 24
  • 36
  • Please check If your IP is restricted by the server or not? – Abdul Qayyum Apr 10 '18 at 17:35
  • Does it work on the same machine where it fails with full IIS? is `my-sql-server.somedomain.com` an intranet/active directory machine or are you trying to access it over the public internet? – Alex K. Apr 10 '18 at 17:36
  • I'm testing on the same machine: I can connect when using IIS Express/Kestrel, I cannot connect using IIS. I'm connecting the SQL server over public internet, I'm not on the same network. I cannot check if the SQL server is restricting my IP, but there is no reason to believe that, since I can connect to it any other way than through IIS. – Bjarte Aune Olsen Apr 10 '18 at 17:43
  • Perhaps switch off the windows firewall & see if that makes a difference. (Having a publicly accessible SQL Server seems like a really bad idea, a VPN is usually used instead) – Alex K. Apr 10 '18 at 17:58
  • I tried to turn off the firewall, that didn't help. Thanks a lot for the suggestions, by the way, I really appreciate your help. – Bjarte Aune Olsen Apr 10 '18 at 20:07

3 Answers3

2

The issue was simply that I was looking at the wrong connection string.

I have appsettings.json containing one connection string and appsettings.Production.json containing another.

When running the application with IIS Express or Kestrel, the first one is used, but when I publish the app and then run it under IIS, the environment is set to Production, and the production settings are used instead.

The problem was that the production connection string pointed to a database server that didn't exist anymore.

Bjarte Aune Olsen
  • 3,230
  • 4
  • 24
  • 36
1

I solved this issue by adding LocalSystem to Identity in Web site Application pool.

Steps:

  1. Select website application Pool
  2. Right click and click Advanced Setting
  3. Click on Identity, and change it to LocalSystem
Faraz Ahmed
  • 1,467
  • 2
  • 18
  • 33
  • take hint from this URL https://www.syncfusion.com/kb/6897/how-to-add-permission-for-iis-application-pool-to-access-sql-server-database – Faraz Ahmed Apr 17 '19 at 08:11
0

You need to enable TCP/IP protocol on sql server. Please use following link: Why am I getting "Cannot Connect to Server - A network-related or instance-specific error"?

mukesh joshi
  • 584
  • 5
  • 19
  • Thanks for the tip, but I don't have that kind of access to the SQL server. Since I can connect to the SQL server using IIS Express and not using IIS, I suspect the issue has something to do with the combination dotnet core, EF core and IIS. – Bjarte Aune Olsen Apr 18 '18 at 10:15