Can't Find SQL Server-REVIVED

[Shaggy Hiker]

Checking stuff...

0) What happens when I try to connect with the credentials from the connection string, it works fine on A, but on B, there is a lengthy pause (the timeout), followed by the lengthy message to the effect that SSMS could not connect, with the phrase "the server was not found or was not accessible."

1) I found that A and B both had firewall rules for HomeGroup (two of them). They were enabled on A, disabled on B. I disabled them on A...and the connection still worked fine. That suggests that those were not related, nor was there any real reason to think that they would be, considering I didn't think I had a homegroup.

2) Checked that SQL Server on S is using the default port. It is. Checked firewall rules on both A and B, neither one has any mention of SQL Server, nor specifically the default port for SQL Server (1433). Most rules deal with Any for ports, though.

3) Ping from both A and B to S. Both had no issues and identical response times, as I would expect.

4) One possible clue is that the error message mentions the Named Pipes Provider.

And that appears to have been the key. I'm not sure what I just learned, though. Sure seems like I could give a name to THAT pipe.

I looked at the server and noticed that Named Pipes and one other was enabled, but TCP was disabled. This is the way I had the old, 2012 DB configured, so I had matched that with the newer DB. However, the mention of the named pipes provider got me thinking (along with a bit or research on the web) that I could turn on the TCP and try to force the use of the TCP protocol for the connection. I did the first part of that, which was enabling the TCP protocol on S, but before doing the second part, I decided to just see whether or not the connection would work using the default protocol (which might have still been named pipes, for all I knew).

When I tried that, it connected right away without issue. I had read that I could specify tcp by prepending tcp: onto the front of the server name, but that wasn't necessary.

So...what did I learn? The one thing that seems clear is that I should enable TCP connections on S, and I suppose I could go further and try disabling other protocols, but I'm not sure if that would be right.