Login failed for user 'sa' in ASP.NET with SQL Server 2019

Question

I am trying to connect my .NET project to SQL Server developer edition 2019 (named instance), but it always throw the error:

Login failed for user 'sa'.

It however connects perfectly through SSMS without any issues.

I tried all possible things as suggested over internet but could not make my website run, is it actually the named instance which causes this issue, or it's issue at large.

I am able to connect my project if i connect to my database located on the remote server. but the one on my local machine just does not works.

Here's how my connection string looks like

server=[machine]\\[instance]; Integrated Security=SSPI; initial catalog=[database];Connection Timeout=1000

I also tried the same with SQL Server authentication

server=[machine]\\[instance]; uid=sa; pwd=[password]; initial catalog=[database];Connection Timeout=1000

None of the above worked out.

Answer 1

Ok, SSMS can connect using direct. However, from asp.net, you in most cases have to turn on networking.

For reasons of secuirty, by default, your local instance of SQL server express has this feature turned off by default.

so, you need to do two things - and this was NOT required with older versions of sql server, but for recent versions - starting around 2012, you need to:

Turn on the browser service. This "translates" the sql "instance" to your IP address. (on startup, sql server assigns a IP address to that instance, but it will NOT be the same IP as your computer). But, the NAT translator thus has to be running.

That IP translator? It called the SQL browser service, and as noted, you need to have that running now, where as in the past you did not. That is this setting - launch the sql configuration, and you see this:

So, make sure the SQL Server Browser service is running - it is NOW required and as noted was not in the past.

Next up:

You need to turn on networking for SQL server. SSMS (sql studio) is able to connect using memory pipes - applications using the sql server provider in .net cannot!!!

So, once you ensured and checked the above browser service is running?

Then click on SQL server Networking Configuration, and turn on network connections (again, they are not turned on by default). That is this:

Now, after doing above, you probably should re-start sql server, and then the browser service. (just right click on sql server again, and choose this:

So, while SQL studio can connect using shared memory, .net and asp.net sites can't.

So, start the sql browser service - it is required to "resolve" the instance of sql server. in most cases, that will be SQLEXPRESS.

And consider using the built in connection builder in Visual Studio - it will not only build the connection string for you, but will also test the connection - and this can be done long before you even run any code.