Strange Connection Issue

Question

I have two instances of SQL Server Express running on a computer which I will call S. This is on a home network, and is wireless. Also on the network are computers A and B. A is connected to the router using an ethernet cable, while B is also wireless.

I have a program written in VS2019, which attempts to connect to the database on S using some credentials it has stored. If this doesn't work, it waits for some other computer on the network to tell it the credentials, then it tests them. This application is on both A and B. Also, SSMS is on both A and B.

All was running well as of last week. I didn't use the system for a few days, so the last time I know I used it was a few days back. On Tuesday, I attempted to do something and found that A would see the databases in the application, but wouldn't connect. SSMS on A wouldn't connect either. B will connect via either the application or SSMS.

I have also attempted a telnet connection from A to S on port 1433, and that worked fine. Also, I know that the last time I used the system A was connecting, because A was the one that gave the credentials to B, which B is now using successfully to connect.

There have been no changes made to A between the time it was working and the time it failed. I have some slight suspicion that the router may be involved in this by some means, since I had an internet outage, and S lost the ability to connect to the network at that time. This was eventually solved by changing the network key, which the router appears to have corrupted. This is by no means definitive, but it's the only time that S was affected from the time the program and SSMS were connecting till the time that neither would connect.

Any suggestions?

Answer 1

The problem went away as mysteriously as it arrived.

I tried all kinds of things, yesterday. I checked everything I could think to check. I pinged and telneted. I systematically stopped and started different parts and services while carefully checking the results. None of that showed anything unexpected or particularly interesting.

This morning, I fired up A and had no internet. I rebooted and had internet, but the keyboard wasn't working. Now, while I've had A for a few years, the keyboard has had an issue or two over the years, so I had seen that at least one time in the past, so I rebooted again and got both internet, keyboard....and SSMS connected to S quickly and without issue.

So, the problem has vanished, and thinking back on it, the only thing that fits is that rebooting A solved the problem, but why was the problem there? It could be A, it could be the router (which is new), or it could be some interaction between the two. At this point, I don't have enough data to be certain, nor is there anything that needs to be fixed, at this time, but it's still a mystery.

Answer 2

Sure, there was the typical error about SSMS being unable to find the server. I forget one of the details (whether it was trying via TCP or named pipes), but otherwise, I think those are all the same. Since the issue went away, it's hard to say for sure, now.

Answer 3

Hi ShaggyH-6648,

I attempted to do something and found that A would see the databases in the application, but wouldn't connect. SSMS on A wouldn't connect either.

Are there any error messages when using SSMS to connect to S? And please check Application and System event logs from SQL Server and Client systems.

Please refer to Solving Connectivity errors to SQL Server which might help.

Best Regards,
Amelia