0

I have a Web API hosted on IIS.

In one environment (Windows Server 2012 R2 and SQL Server 2008 R2), the Web API can access the SQL Server database on the same machine.

But in another environment (Windows Server 2008 R2 and SQL Server 2008 R2), I get this exception

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: 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.)

Why it is complaining about local database runtime installation and SQL Server Express - I have Microsoft SQL Server Standard (64-bit) installed. I suppose IIS doesn't see SQL Server and so expects that there should be SQL Server Express?

When I debug this API on my development machine, then it can access that SQL Server, so connection data is not wrong.

Had a though that might be IIS is executing in different security context than SQL Server

I checked SQL Server Configuration manager and set there Log on as NT Authority\NetworkService.

And did same in IIS:

Application pool (*entity to which my API is assigned*) -> Advanced settings -> Process Model -> Identity = NetworkService,

and added NT AUTHORITY\NETWORK SERVICE login to SQL Server and user for the database I need to access from the Web API, but that didn't help.

Why is my code trying to access a local database, if connection string is to a regular SQL Server database?

Data Source=ip-address;Initial Catalog=database;PersistSecurityInfo=True;User ID=sa;Password=...;

From suggestions I've found there were

  1. About accept remote connections: I've checked - database engine is configured to accept remote connections (I wouldn't be able to connect to it from my development machine then)

  2. About firewall exception: I guess that one also is fine since I can access DB from my dev machine

  3. Enable TCP/IP in SQL server configuration; checked - it is enabled

Checked solutions, ideas from here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Prokurors
  • 2,458
  • 3
  • 40
  • 65
  • 2
    Side note: You may consider updating your OS and DBMS to supported versions... – sticky bit Nov 07 '21 at 16:36
  • Are you sure it's SQL Server 2008 R2 on Windows Server 2008 R2? By default SQL Server would have been using a virtual account, `NT SERVICE\MSSQLSERVER`, did you change that? Likely the SQL Server service isn't able to start (because it's now unable to access the system database files) so it's not accepting incoming connections. – AlwaysLearning Nov 07 '21 at 21:45

0 Answers0