0

I've switched from AzureDb to a local SQL Server Express instance for my project. I can connect to the instance using Management Studio and see that the server is the usual .\sqlserver with sa and a dummy password. The actual database isn't created yet but I can access the server and see system databases as well as create logins.

However, trying to perform a migration database update, I get the following error.

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: SNI_PN11, error: 26 - Error Locating Server/Instance Specified)

Naturally, I suspected something with my connection string (which I still do).

"ConnectionStrings": { ...
   "SqlServer": "Server=.\\sqlserver;Database=Blopp;Uid=sa;Pwd=Abc123();"
}

I've tried all the usual variations - single backslash, quadrupple backslash, forwardslash, at-char in the beginning, (localhost) instead of dot, computer name instead of dot etc. I'm out of ideas what else it might be. I've tried creating the DB manually. I tried using caps (although I know it's case insensitive). Nothing helped...

Proof of the usual effort: here (SO), here (MSDN), here (SO), here (SO), here (Hanselman), here (client protocol/service running).

What on earth am I missing?

Konrad Viltersten
  • 36,151
  • 76
  • 250
  • 438
  • 1
    The usual instance name is .\SQLEXPRESS – ErikEJ Aug 01 '21 at 17:52
  • @ErikEJ Yes, that's correct. As shown in my example in the question as well as in the linked examples. I wish my problem was that easy. (It would be embarrassingly sloppy but at least simply solvable.) – Konrad Viltersten Aug 01 '21 at 19:06
  • 2
    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) Have you been through *every* step to enable remote access? Server remote access allowed; TCP on; firewall open; SQL Server and Browser service started; correct instance name? And if you are actually using LocalDB then you need `(LocalDb)\MSSQLLocalDB` as the host\instance name – Charlieface Aug 01 '21 at 19:28
  • @marc_s I referred to the planet named *Earth* so according to [grammar](https://www.dictionary.com/e/earth/) it should be capitalized (as opposed to the dirt, surface or soil, which should not). Also, appreciate the corrections - better formulation, in my opinion. – Konrad Viltersten Aug 02 '21 at 08:35
  • @Charlieface It doesn't answer my question straight out (I've seen that post during my trouble-shooting). However, something you pointed out made me think and re-check the services. One of the suckers got poofed (server/browser). **Then**, your comment made me realize that I never restarted after the new installation of SQL Server. (It's embarrassing. Please don't laugh... I can hear you laughing from here! OK, I deserved that...) Once I hrmp... rebooted the junk, it worked. I get a new computer once every three years. And being old, I forget infrequent routines. Thanks. Post it as an answer. – Konrad Viltersten Aug 02 '21 at 08:44
  • @KonradViltersten: to continue the nitpicking: actually, in this term "what on earth", you're **NOT** specifically referring to "earth" as a celestial body - thus in this case, according to the article you've linked to, it would be a *common noun* and thus I'd say it should *not* be capitalized ...... the article specifically lists "down to earth" and "what on earth" as samples of this ... – marc_s Aug 02 '21 at 11:07
  • 1
    @marc_s Well, your logic is only valid if one reads the **whole** of the article and not just glances at the first paragraph (like I did, while bias-confirming what I already "knew"). Pfff... On a serious note, though, yes - you are right and I stand corrected. I regret my failure to investigate more thoroughly. However, I'm not sorry, because I just learned something new. And that's almost always a good thing! – Konrad Viltersten Aug 02 '21 at 13:33
  • @KonradViltersten: totally agree - we all learn something new (and I did, too!) every single day - and that's a good thing! – marc_s Aug 02 '21 at 14:13

1 Answers1

1

Base on the error, you have either a Network or a SQL configuration problem.

To further confirm this, you can do a UDL test (or the most basic non SSMS test you can do), Ref: How to perform a UDL test to check the SQL Server Connectivity.

The first answer to Why am I getting “Cannot Connect to Server - A network-related or instance-specific error”? has a good troubleshooting checklist for the kind of error you mention.

If your UDL test works, but you still cannot connect. Check your connection string. An easy way to do so is: Go to Visual Studio (Visual Studio not SQL Server Management Studio) and do:

  1. Go to Tools -> Connect to Database.
  2. Under Server Name Select your Database Server Name (Let the list Populate if it's taking time).
  3. Under Connect to a Database, Select Select or enter a database name.
  4. Select your Database from Dropdown.
  5. After selecting Database try Test Connection.
  6. If Test Connection Succeeds, Click Ok.
  7. In Visual Studio go to View -> Server Explorer.
  8. In the "Server Explorer" window, Under Data Connections, Select your Database. Right Click your Database -> Click Properties.
  9. In Properties window you will see your Connection String.

Taken from: Juned Khan Momin answer to this question: How to get the connection string from a database

Guilder
  • 444
  • 4
  • 12
  • That would produce the proper connection string, which, if you check the links in my list of researched sources, isn't the problem. NB the question isn't *how to get connection string*. It's *can't connect to DB (despite apparently correct connection string)*. – Konrad Viltersten Aug 02 '21 at 08:37
  • Hello @KonradViltersten, thank you for commenting. Given that you stated: Naturally, I suspected something with my connection string (which I still do) And in your list of things researched, I did not see anything that made it clear how you got the connection string, I assumed that we was not confident on the connection string syntax. I have added a UDL test to the answer, with the intention of further confirming if the misconfiguration is with the application, or with the Network/SQL server – Guilder Aug 03 '21 at 15:13
  • @KonradViltersten If you do not mind, I will like to keep the answer. As I think it's still a good troubleshooting step. first: Confirming that we have a properly formatted connection string and then if the problem persists: do a simple UDL test to confirm if it is a misconfiguration with the application, or with the Network/SQL server However, given how long you have been using StackOverflow, if you feel that it's distracting or misleading, I will remove it. Have a nice day. – Guilder Aug 03 '21 at 15:21
  • In fact, if you snatch the comment by @Charlieface (who seems not to re-post it as an answer) and add it to the answer of yours, I'll gladly accept it as the correct one. After all, it was precisely that comment that tipped me over. And I'm sure said user won't mind (and if they do, I apologize and take the blame). – Konrad Viltersten Aug 03 '21 at 20:03
  • I am happy that in the end, you were able to solve your problem. If @Charlieface does not post his answer, then to "close" the question, you can choose my answer if someone else has not posted a better one. If later on, he posts his answer you can uncheck my question and select his. Enjoy your day. – Guilder Aug 04 '21 at 00:33