VS2019 sql connection issue running asp.net project

Question

I have sqlexpress installed on my dev laptop along with vs2019. I can access the sql instance in both SSMS and from the Server Explorer. When I go to debug and asp.net project, the sql connection fails with:

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)

This same configuration works on my desktop - same connection strings - no problem. And laptop sql is configured to allow remote connections.

Not sure what to do next?

Thanks Abbott

Additional Info ... I made sure the named pipes and tcpip were enabled and browser was working. Still had same error. Here are the connection strings. The desktop connection works on my desktop:

<!--Local DB For Desktop Testing-->
<!--<add name="DBConn" connectionString="data source=DESKTOP-QQOLG5N\SQLEXPRESS;initial catalog=kidsbookoutlet;integrated security=SSPI;persist security info=True;packet size=4096" />
<add name="KBODataEntities" connectionString="metadata=res://*/Context.KBODataEntities.csdl|res://*/Context.KBODataEntities.ssdl|res://*/Context.KBODataEntities.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=DESKTOP-QQOLG5N\SQLEXPRESS;initial catalog=kidsbookoutlet;integrated security=True;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient" />-->

<!--Local DB For Laptop Testing-->
<add name="DBConn" connectionString="data source=LAPTOPDELL\SQLEXPRESS;initial catalog=kidsbookoutlet;integrated security=SSPI;persist security info=True;packet size=4096"/>
<add name="KBODataEntities" connectionString="metadata=res://*/Context.KBODataEntities.csdl|res://*/Context.KBODataEntities.ssdl|res://*/Context.KBODataEntities.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=LAPTOPDELL\SQLEXPRESS;initial catalog=kidsbookoutlet;integrated security=True;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient"/>

Here's the ssms connection that works.

In your Visual Studio check your connection string. You might have mis-spelled a word in it. If that's okay, open Run command and type services.msc. Find SQLSERVER\EXPRESS and double check if it is running. — Rohan Rao, Aug 31 '20 at 14:15
That's a pretty clear message - the server isn't accessible, perhaps because it's not running or the server name/address is wrong. Or perhaps it's not configured to accept remote calls, or TCP calls. There are several duplicate questions for this. — Panagiotis Kanavos, Aug 31 '20 at 14:15
At the very least post the connection string. The server name should be something like `.\SQLEXPRESS` or `localhost\SQLEXPRESS` or `127.0.0.1\SQLEXPRESS`. When you connect from SSMS which name do you use? — Panagiotis Kanavos, Aug 31 '20 at 14:15
Does this answer your question? [Why am I getting "Cannot Connect to Server - A network-related or instance-specific error"?](https://stackoverflow.com/questions/18060667/why-am-i-getting-cannot-connect-to-server-a-network-related-or-instance-speci) — Panagiotis Kanavos, Aug 31 '20 at 14:18
`This same configuration works on my desktop` but you aren't connecting to your desktop, you're connecting to a different machine, with a different domain name — Panagiotis Kanavos, Aug 31 '20 at 14:19

Albert D. Kallal · Answer 1 · 2020-08-31T19:08:02.327

Do you have the sql server browser service running? Usually SSMS will complain, and since that is working, then this is a long shot. But I would ensure that not only is sql server service running, but also sql browser service.

This one:

Next up? Which provider in .net are you using to connect? You have and can choose:

oleDB

ODBC

sqlProvider <- recommend this one

So, I would in the project settings, create a connection string here:

And for above, did you try the test connection button when you use the connection string builder:

This one:

I mean, why try running a bunch of code and all kinds of stuff. Now maybe you ARE talking about the conneciton builder in VS, and you can't connect.

However, if SSMS can, then VS should be able to.

So, does the test in above not work?

You can also try (and should) in the sql confi check the above screen cap if the browser service is running. And while you doing that, check the connections. Expand the SQL Server Network Configuration and look at these settings:

So before you write any code? You check if browser service is running. Click/expand Protocols for sql express. Then in VS you launch the connection builder and use the test connection. All of the above is done in aobut 40 seconds tops of your time. And these steps are quite much the first thing you will do.

Don't try and write code in VS just yet. Use the connection string builder.

You also failed to mention which provider you are using? (this is VERY troublsome, since you supposly dealing with conneciton problems, and you FAILED to mention what provider you are attempting to use. As noted, you have 3 choices:

oleDB provider

ODBC provder

sqlProvider (this is the recommend .net provider - often called ado.net (but I NEVER know for what reason - that term should have been banished long ago for the poverty and confusing it caused over the years).

Anyway? Spend 45 seconds checking the above, and then an additional 35 seconds creating a connection in VS. Just remember to choose the SAME provider in that connection builder as you using in code (1 of the above 3), and thus when you use test connection, you at least have a known working connection string, and a known working connection string for the given provider you are using here.

I started both browser and named pipes. SSMS still connects but asp.net application still fails. Here are the connection strings I'm using (on both the desktop and the laptop. — BedfordNYGuy, Sep 02 '20 at 12:01
I wasn't sure how to respond with all the info so I updated the original request to include my additional information ... thanks — BedfordNYGuy, Sep 02 '20 at 12:17
And does/did the test connection in VS work before you try running any code? — Albert D. Kallal, Sep 02 '20 at 12:56
In other words, when you build a connection string in the settings of vs and use test connection, does the connection show ok? You came back with a screen shot from SSMS - that not what I asked. use the connection string builder in settings of VS like my screen shots show - does test connection button work? We not using the sql server explorer from VS - but a simple test of the connection string builder in VS. — Albert D. Kallal, Sep 02 '20 at 14:09
I am managing an older WebSite project, not a web application. My string was built by EF. I've attached the connection image from VS Server Explorer ... which works in VS.Might there there be an IIS setting that affects this? Tanks - Abbott — BedfordNYGuy, Sep 07 '20 at 12:33
Only tip was to check VS connection builder works. The connection builder under tools->"connect to database" if this is not a web project as opposed to just a web site. You suggest the connection builder works. (so, web site or a full project both have use of the connection builder). If EF works and shows the tables, then I would have to guess the connection is ok. So, this might be debug config vs deployed one. I would check if the release settings for web config are not different. I would check if builder connect string created matches your existing connection string used by EF. — Albert D. Kallal, Sep 07 '20 at 16:09

VS2019 sql connection issue running asp.net project

1 Answers1