Error 500 or 404 when trying to connect localhost to the SQL Server DB by string in appsettings.json

Question

I have SQL Server on the server. It has a username/password and a mixed auth. If I open port 1433 on the server and connect to the database via IP:Port, site works as it should.

A string in appsettings.json looks like this:

"ConnectionStrings": {
"DefaultConnection": "Data Source=IP,Port; User ID='sa'; Password='password'; Database=DBName;"
}

But I want to turn off external database connection.
I need to write it in appsettings.json connectiong to the database as localhost.
Maybe I need some kind of permissions somewhere else?
How do I do this?

This line works on the home computer for the project in localhost. But it does not work on the server.

"DefaultConnection": "Data Source=(localdb)\\mssqllocaldb;Database=DBName;Trusted_Connection=True;MultipleActiveResultSets=true;"

I've tried different variants. Either it doesn't work or it works, but somehow partially with errors.

I see such error in log:

2024-07-28T09:24:50.2366385+03:00 INFO [Project.Utility.NotFoundMiddleware] [0] Handling 404 error for request /Home/Error

I see such errors in browser: 404 or 500

Error 500 just means "internal server error" and could mean anything; get the actual error from the logs, debugging, etc. — Thom A
– Thom A ♦, Commented Jul 28, 2024 at 9:58
What confuses me is that you want to turn of remote connecties, but it looks like you're trying to connect from a remote. — Peter
– Peter, Commented Jul 28, 2024 at 10:22
LocalDB is minimum installation feature targeted for developers. You might have full SQL Server version installed at server and connection string would be different. Try generating connection string from Visual Studio and test it. If you can't connect remotely due to security configuration, you might need Remote Desktop. — Daniel Manta
– Daniel Manta, Commented Jul 28, 2024 at 10:45
I have a project on the server that I am publishing. I just wanted to connect it locally (localhost). There is the same database on the server, but locally the project cannot connect to it for some reason. It is connected only by IP,Port (from any location). And I need that, if it is on the server, it connects to the same database there via localhost. I didn't even think that there might be a problem, but there is. — Volk
– Volk, Commented Jul 28, 2024 at 11:08
In Win Event Viewer, I see (Source: MSSQLSERVER): Login failed for user 'cw0090005'. Reason: Could not find a login matching the name provided. [CLIENT: 45.135.232.28] if I try to connect locally to the database using the line specified above. — Volk
– Volk, Commented Jul 28, 2024 at 11:13

Volk · Accepted Answer · 2024-07-28 16:29:01Z

0

The problem was solved based on this post:

Login failed for user 'IIS APPPOOL\ASP.NET v4.0' Login failed for user 'IIS APPPOOL\ASP.NET v4.0'

Only I did it for the site.

IIS APPPOOL\site.com

IIS APPPOOL\www.site.com

And everything worked. :)

The string should look like this:

"DefaultConnection": "Server=[SERVER_NAME];Database=[DB_Name];Trusted_Connection=True;MultipleActiveResultSets=true;"

...And you can safely close port 1433.

Good luck!

answered Jul 28, 2024 at 16:29

Volk

497 bronze badges

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

Collectives™ on Stack Overflow

Error 500 or 404 when trying to connect localhost to the SQL Server DB by string in appsettings.json

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related