Peculiar SQL Server Connection Issue

SQL Server Administration
1

My network has one computer (A) connected via ethernet cable to a router. There are two other computers on the network (B and S), both of which are connected via WiFi. On S, there is both SQL Server Express 2012 and 2017, hence the S for server.

I have a program that is found on both A and B. The first thing it does when it starts up is attempt to connect to the 2017 SQL Server on S. If that fails, it waits a little while to see whether any other computer will send it credentials. If it gets credentials, then it attempts to use them to connect. If it isn't able to connect in time, then a form pops up showing all SQL Server instances visible on the network, and allows the user to select one, add credentials, test connections, and so forth.

Also, both A and B have SQL Server Management Studio (SSMS) installed.

Up until last week, all was well. A and B could connect via the application or via SSMS. Yesterday, B could connect, but A cannot. B can connect using either the application or SSMS. A can't connect by either one. The application is getting credentials from B and trying them. I can see that happening, but the connection attempts fail. This eventually leads to the form showing up, at which point I can see both SQL Server instances on S.

I have also tried telnet from A to S on port 1433, and it works. So, A can more or less see the SQL Server, and the SQL Server is responding, since B has no issues, but A can't connect.

I've tried rebooting...well, by now, everything including the router, and done so a couple times over for most parts. A will not connect. B always connects. The last time the system was used, which was last week, all parts were working as they should.

I'm at a loss as to what to look at next.

2

sounds more of a network/firewall issues than SQL server but for fun connect A to router via ethernet cable and see what happens.

1 Like
3

A is the computer that IS connected via ethernet, it is B and S that are wireless.

It DOES sounds like some kind of firewall issue, but where, and how did it arise spontaneously between yesterday and the time it last worked (which was sometime last week, though I'm not sure of the exact day)?

I felt it might be a router issue, since I lost internet on Sunday for an hour, or so, and when it came back, S couldn't connect to the network. That was fixed by rebooting S. Since then, I've had to change the network password, but A, B, and S were all connecting to the network and the web both before and after the network password change, while A can't connect to SQL Server on S, even though the application (though not SSMS) does enumerate both the 2012 and 2017 SQL Server instances.

4

Is your network setup as a workgroup - and are all PC's in the same workgroup?
Are you connecting to SQL Server using a SQL account or Windows Authentication?
Are you using fixed IP addresses - or are they dynamic?
Can you connect the server (S) directly to the router?

It really isn't clear what you mean when you state that your program waits for any other computer to send it credentials. Why would you have B sending anything to A to connect to the server S?

1 Like
5

I had an issue a few years back that was due to workgroup membership, but all systems are Windows10, and the homegroup was removed from Win10. If there is any other kind of workgroup for a home network, I'm not aware of it, and certainly didn't knowingly connect B to any such thing. If there IS an option here, I'd need to know how to look.

SSMS on S connects using Windows Authentication, but both A and B are using SQL accounts. The IP addresses are dynamic. I can't physically connect S to the router. S is a Surface Pro 2, which has no ethernet port. There was a way, using a docking station, but I never got a docking station for that system.

The details of that program are probably not relevant, but just for clarity, I'm a coder. The intention is that a person can install the program and not need to know about the credentials set up for the database. Each running instance will periodically contain a heartbeat, which is a UDP message that includes a few things, including a connection string (not in plain text). So, when the application launches, it checks first to see whether or not it already has credentials (A does, because B got them from A). If it has credentials, then it attempts a connection. If the connection fails, then it waits a little while in case there is another instance of the application running on the network. If so, that other instance will be sending out the credentials.

The communication isn't FOR the credentials, that's kind of a bonus. The communication is for something similar to a UDP-based LAN game in that each system can announce to the others that they need to update a part of their screen.

6

your server is on a surface Pro? why

7

The real reason is because I wanted to work on the program while on a thousand mile camping bike ride. A Surface Pro is fully capable of running multiple simultaneous instances of Visual Studio, SSMS, and SQL Server. The screen size is a bit cramped on the 2, the keyboard is a bit TOO cramped on the 2, but the size and weight were excellent, and I could recharge from a solar panel.

System B is a Surface Pro 7, because I have a somewhat weirder bike ride coming up in a few months and want to take my work with me, again. The 7 has a bigger and better screen, larger and easier to use keyboard, yet it is lighter and more powerful with a longer battery life. Therefore, while I will have to also put SQL Server onto B for that trip, I am just using the older 2 for the server, currently.

The short answer is: Whim.

Works well in that role, though.

1 Like
8

what kind of device is A. and can you ping ip.of.S from A?

9

A is a pretty standard desktop. I was able to ping S from A, and was even able to telnet to port 1433 (SQL Server port) without any issue, so A is seeing S, and sees something listening on the SQL Server port.

10

The only thing I can think of is a stale name in the application - since you must be connecting by name and not by IP address. If the IP changed on S but the name registration did not update on A - it would try to connect to the old IP address and fail while B is able to connect because it was updated.

Since you are using SQL authentication from the clients - it isn't related to using the same username/password on each machine, which is a requirement for workgroup membership and windows authentication.

You also stated you have Express on the server (S) - is that setup with a fixed IP address or the default configuration using dynamic ports? If dynamic - you would need the SQL Browser running, but if you are not connecting to that instance it wouldn't matter.

11

S is using the default configuration and the SQL Browser is running.

The stale name doesn't sound possible, unless I'm misunderstanding. Frankly, the easiest answer is a typo, but I believe I have ruled that out over and over. I have three different logins with different levels of access. I've typed in the passwords, and gotten them from B. I've copied and pasted the server name, and it does show up in the application, so I believe that can't be wrong, by this point.

One thing is that that application enumerates servers using this code:

Dim dSourceInstanct = SqlDataSourceEnumerator.Instance
mDTServers = dSourceInstanct.GetDataSources

My understanding is that this enumeration uses a different mechanism from the connection, though that's just a tip I got. This lists all running datasources, and in this case, does show both instances of SQL Server on S, complete with server name.

12

And now it's gone.

After numerous reboots, testing this, testing that, changing things systematically and testing the results, nothing changed anything. I could block B from connecting by doing things that should block connections, but I couldn't get A to connect, or find any reason why it was not. Over the course of that, I rebooted A a few times and B and S MANY times.

This morning, I started up A and didn't have internet. Rebooted A and had internet, but no keyboard. Rebooted A again...and everything was working, including the connection to S. SSMS is connecting without issue.

System A is one that I've had for a couple years, by now. I have had an issue where the keyboard didn't work on startup one other time over those years, but it was just the one time, so it could have been the keyboard or something else. Internet issues have been somewhat more common, of late, but I was having no internet issues, yesterday.

Now, all is well, but why? Was it something about the router, which is pretty new, or something about A, which is what got rebooted this morning, and prior to the disruption, yesterday? Or some interaction between the two?

Perhaps I'll never know, but at least I kind of have something to try.