-1

Firstly there's a similar question on Stack overflow, and I have tried suggestions from the responses, none of which have worked.

I have SQL Server and IIS running on my development machine and have an issue when sending a request to an API running on IIS.

My machine is a newly built Windows 11 box, and I am running the latest version of... everything!

The connection string is fairly basic:

Server=[machine name]\\[instance name];Database=[DB name];User Id=[user name];Password=[pw];TrustServerCertificate=True;

I can log into SSMS with the credentials for the user in the connection string, so I cant see an issue with these.

SQL Server is set to "SQL Server and Windows authentication mode".

I have made changes in the SQL Server Configuration manager snap in regarding TCP/IP to use the static port (1433).

I have a rule set in my firewall (I saw that suggested) to open 1433 - although I can't see why that would be a thing when IIS and SQL Server are on the same machine.

I have uninstalled and reinstalled a couple of times, yet get the same response in Postman.

{
    "title": "Error",
    "status": 400,
    "detail": "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: 26 - Error Locating Server/Instance Specified)"
}

The instance name is correct, and SQL server is, as far as I know, configured to allow remote connections, as the box is checked in its properties.

My IIS websites directory setup is a little unorthodox, in that I just created a folder on my D: drive called websites, however I haven't experienced an issue accessing my UI website there, I don't think this is an issue.

I'm a little lost to be honest, although I previously used IIS locally a lot, it's been a few years - I have never had issues connecting to a local database previously.

Improve this question
8
  • 1
    Are you sure SQL Server is actually running? Server=[machine name]\[instance name] means you need SQL Browser running, is it running? Instead of specifying machine name, just use .\InstanceName In the Configuration Manager, is Shared Memory switched on as a protocol? Commented Sep 30 at 10:32
  • What's the actual connection string? Fairly basic means nothing and [machine name]\[instance name]; may hide an attempt to connect to the wrong server instance. If you use SQL Server Express, Server=localhost\SQLEXPRESS or .\... etc should work. If you don't use Express and haven't installed SQL Server in a named instance, just Server=. or Server=(local) or Server=localhost would work. You only need to specify an instance name in the very rare case where SQL Server was installed on a non-default instance Commented Sep 30 at 11:21
  • Can you connect to the database with SSMS using the same connection string? Are you sure you're connecting to the correct database or server? What does the web app's log show? What you posted isn't a standard response - server failures should be 500. Perhaps that custom code is reading the wrong config file? Commented Sep 30 at 11:25
  • if you're using static port = 1433, you can just use localhost as address without the fancy instance stuff. Does that work? Commented Sep 30 at 11:39
  • @Charlieface - SQL browser is indeed running, Based on this comment I did try just using the instance name, just in case, but to no avail. Shared memory is switched on, yes. Commented Sep 30 at 12:10

1 Answer 1

Reset to default
0

Well this is a cautionary tale.
All my settings were correct, the missing piece was publishing from Visual Studio. When I set up the publish settings I either checked "Use this connection string art runtime" or left it checked and this was holding my original connection string before I add the SQL server user and saving it in appsettings.production in the published files which is not a part of my application.
With that being said, and having now noticed this I can now finally get on with my project as it can now connect to SQL.
I don't mind putting this out there if it helps someone else in the future!

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

It also reminds future readers if they test by hardcoding the connection string then they might figure this out faster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.