Can't Find SQL Server-REVIVED

I have something set wrong, but I'm not sure what.

I have two computers that connect to a database located on the third computer. For one of them, all is well. I had to open a port on the computer that hosts the SQL Server such that others could discover it, but I don't recall doing anything at all on the computer that is having no issues.

On the other computer, SQL Server Management Studio doesn't see the server at all. However, in the program, I look for all instances of SQL Server and it shows both of the versions on that DB server (it has SQL Server 2012 and 2017...or whatever is most recent). However, it won't connect using the proper credentials.

The way I am looking for SQL Server is with this code:

Code:

---

Dim dSourceInstanct = SqlDataSourceEnumerator.Instance
mDTServers = dSourceInstanct.GetDataSources

---

again, this does find the SQL Server instances, I just can't connect to them using correct credentials, and SSMS doesn't even see them.

What setting do I have incorrect?

Hi.Any error messages ? Any error messages on event viewer?Is it a timeout or connection fail?

Per an old answer on this forum, a member had an issue on viewing the servers, NOTconnecting to them, https://www.vbforums.com/showthread....rator-Instance
And another issue for general PC connections from a same computer maybe? :P https://www.vbforums.com/showthread....nnection-Issue
:D

But, since the problem of connection can be something similar, can you verify the member steps and framework changes. I think it has some similar computers with you.

2) Can you check if the Server Browser Service is started? Also that your firewall isn't blocking anything?

3) Saw another example that may work (haven't tried) if you can't solve this
https://www.codeproject.com/Articles...n-C-Using-ODBC

:lol::lol::lol:

Well, seems like I've walked this path before, though I can't say that I remember doing so. It WAS four years back, so I'm not surprised that I've forgotten. Still, that guy did give me some things to look at. Not at the moment, though, I have to take pictures of shattered tablets.

If I recall correctly, the SQL Server Enumerator isn't 100% accurate. It doesn't always find every instance of SQL Server running on every server it finds. It relies on it being broadcasted, which isn't a constant ping. And if there's multiple instances on a single server, it's going to report the first one it finds at that server. Often it's the primary/default instance. Which means if you want one of the off-instances, you need to know about it, and connect to it manually. Finding it automatically will be a crapshot. So, even though SSMS doesn't see it, can you connect to it? Ah... probably a stupid question, but I'm going to ask anyways, has it been setup to allow for remote connections, is there a firewall in the way, and lastly, can you get on that machine locally, and make sure that the creds are setup correctly?

-tg

Yes to all that.

I don't have a named instance running on the server computer (feels wrong to call it a server, because it's actually a Surface Pro 2), but I have both SQL Server 2012 and 2017. Both of those show up in the enumerator. Back when I wrote those other threads, I really was counting on that enumerator to work. That's no longer the case. It's still there, but just for convenience, so it isn't nearly as important as it used to be.

Computer A and computer B both see the server (computer S). Computer A connects to S using some credentials. A then sends out a periodic heartbeat that contains the connection string that it used successfully (in a packed fashion). B doesn't know about S and doesn't have any credentials. It tries to connect, but since it doesn't know about S and doesn't have any credentials....that doesn't get anywhere. Heck, it doesn't even know who it's trying to connect TO.

As it is trying to connect, it's also listening, and as it listens, it hears the heartbeat from A. B then takes that heartbeat, unpacks the credentials and tries to connect to S. That's where the failure happens. The very same connection string that A uses to connect to S will not work for B to connect to S. A has already tested the connection string (three of them, actually, for reasons that may not be totally valid), so by the time B gets the strings, I know that they work on A. Unfortunately, they don't work for B, so B continues to wait. It may try repeatedly, since it will get heartbeats periodically. What is failing is that the first connection string times out while attempting to connect, which will cause it to pause and try again, since there are times when the server is still waking up. This isn't one of those times, of course, because A is already talking to S, but B waits a couple seconds and tries again anyways.

From all that, I think you can see that S is working, the credentials are valid (A uses them without issue), S is visible and accessible to A, and yet B is not.

In one of the other threads, I was talking about the need for a computer to be on the Homegroup, at which point the problem went away. My understanding is that the whole concept of the Homegroup has now gone away. I believe that was a way that Windows 7 allowed people to set up little home LAN networks that could share drives and stuff between one another, and that the whole thing was removed in Windows 10. I certainly haven't attempted to connect B to any homegroup, but that's largely because I don't think I have A or S connected to a homegroup, either. Still, that's probably a clue as to what is not working.

My guess would be that B doesn't have some kind of permission set right on Windows Defender firewall, and DigitalShaman gave me a reply in one of the other threads that supports this, somewhat. If that SQL enumerator is using a different port from connections, that may be the issue.

The reason I'm trying to work this out in some detail is that I'll have to be able to repeat the process. What I really do NOT want to do is to get this working without understanding exactly why it isn't working now, and what I did to fix it.

Gotcha... sounds like the problem is with B... not so much A or S then. Makes me think the port isn't open in the firewall on B.

-tg

I have several instances running on my Surface, and have to use certain ports to connect so that the correct instance is listening. Got complicated quick - and I would not be happy to have to go back into that area again.

Code:

---

Data Source=STEVE-SURFACE;Initial Catalog=TCSHemp

or

Data Source=STEVE-SURFACE\SQLEXPRESS01;Initial Catalog=Galfiles

or

Data Source=steve-surface\SQL2017,1491;Initial Catalog=Health

---

3 different versions of SQL - three different instances.

Right-o... since A can connect to S, I'm fairly certain that S is fine... and so is A... B seems to be the issue... I wonder if there is something on B that's blocking the SQL Server port from being able to go out. That would explain why you can't see the server, and also why, even with the connection info from the heartbeat, it still can't connect. In short, there's some configuration on B that's preventing a full connection to either the server/Surface, or to the network that's impeding the ability to connect. (And yet, it clearly can see A... ???)

-tg

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.

Guessing you're using Port 1433 on the Server.... Is that Port open on "B"?

You wrote that as I was editing my last post, which essentially resolved things, but just to be clear, I AM using Port 1433 on the server, and there is no specific rule on either A or B that addresses that port. It isn't explicitly opened or closed on either system. There are enough rules to make a lawyer giddy, and I have no desire to go through each and every one of them, but most have the ports set to Any, so they SHOULD also deal with port 1433.

This is getting a bit maddening. All was well when I left off, which I guess was either the 29th or 30th. In fact, things were working quite well. A was sending out notices, B picked up the notice, checked the connections, and found that all was well. Both were connecting to S without any issue.

Now it's a new year. I haven't worked on anything related to any of this for the last few days...and today I tried it and things broke in a different way.

When I first tried to use SSMS on A to connnect to S, if failed. I found that odd, so I fired up B, and it also failed. I then went over to S, which had been running for a few days (forgot to shut it off), and found that S couldn't connect to the internet. There were a few steps before I figured that out, but once I realized that S had lost the ability to connect to the wireless network, it certainly made sense that neither A nor B could connect to it.

Restarted S, and it found the network and connected again. This may have been due to some internet outage that happened yesterday evening, though since I wasn't working with S for the last couple days, I can't be certain.

With S back on the system, B was able to connect to S using either my program or SSMS, so all appeared to be well....except that A won't connect to S anymore. In the program, it sees the two SQL Server Express instances on S, but I can't connect to either one. Can't use SSMS to connect, either.

If I start the program on B, it sends out the information that A would need to connect, and I can see that A is attempting the connection, but the connection always fails.

Last time, A would connect and B would not, until I enabled TCP connections in SQL Server Configuration Manager. Therefore, I wondered whether turning OFF the TCP connections would cause A to connect and B not to connect, once again. That wasn't the case. Turning off TCP connections meant that neither A nor B would connect, which is actually a better outcome. Turning the TCP connections back on got me back to where B connects and A does not.

My next thought was the firewall on A was getting in the way. Therefore, I went into Control Panel and turned off the Windows Defender Firewall. This had no effect that I could see. With the firewall off, I still couldn't connect on A, so I turned the firewall back on.

Firewall on or off doesn't seem to matter. I've tried a few different valid credentials in both SSMS and in the application, none will connect from A, but all connect just fine from B. One other difference is that both B and S are wireless, while A is hardwired, but this has been the arrangement for many years, now, and it was working just a couple days back. I've restarted S, which I kind of had to do once it lost the internet. I've also restarted A since restarting S.

What else can I look at?

For S'n'Gs.... take everything down and offline... bring S back up, then A... see if you can get A to connect to S again..... if you can, then see if B will.... Im wondering if it won't. If it doesn't... see if you can then take A off, and then connect B... could there be a connection pool restriction going on? I'll beh onest, I'm grasping at straws, blades of grass, and anything else that doesn't make sense because, well... I think there's a bit of 2020 that's left over that needs to be gotten rid of before we can get into 2021 properly.

-tg

When I'm setting up a new SQL Server I always ensure that TCP/IP protocol is enabled. When I want to check connection there are multiple ways of doing this but one way I found most useful was using telnet. On system that have a webserver in front of them and the SQL Server is not exposed to the internet when there is a complaint that they cannot connect that is what I use. telnet to the server IP on port 1433. If that works the SQL server will respond.

Maybe you have conflicting SQL services running, that you had previously managed to stop and clear up, and all that re-booting got them START'ed up again. Go into SERVICES and see what you have for instances. If you have two express versions, they cannot BOTH listen on 1433 - you have to change ports. Also, sometimes those ports are selected dynamically - you should be able to see the port in the IP setup.

Quote:

---

Originally Posted by GaryMazzone

When I'm setting up a new SQL Server I always ensure that TCP/IP protocol is enabled. When I want to check connection there are multiple ways of doing this but one way I found most useful was using telnet. On system that have a webserver in front of them and the SQL Server is not exposed to the internet when there is a complaint that they cannot connect that is what I use. telnet to the server IP on port 1433. If that works the SQL server will respond.

---

Never tried that. Heck, I didn't even know that telnet was still a thing.

Quote:

---

Originally Posted by szlamany

Maybe you have conflicting SQL services running, that you had previously managed to stop and clear up, and all that re-booting got them START'ed up again. Go into SERVICES and see what you have for instances. If you have two express versions, they cannot BOTH listen on 1433 - you have to change ports. Also, sometimes those ports are selected dynamically - you should be able to see the port in the IP setup.

---

Wouldn't this be an issue on S, not an issue on A?

One thing to note is that the code shown in post #1 will show two instances of SQL Server, the 2012 version and the 2017 version. B connects to the 2017 version either via the program or via SSMS (I haven't tried connecting to the 2012 version). A sees both versions, but won't connect to either. I'm actually a bit surprised that neither A nor B sees more than just the two on S. I thought it was the case that VS installed a version when it installed, and both A and B have VS on them. Perhaps VS stopped doing that after some version, but A has both VS2010 and 2019, though I believe B has only VS2019.

In any case, it was an eventful morning. I decided to test out what TG was saying in a plodding, deliberate way. I shut all three computers down last night, so the first thing I did was start up S and A. A wouldn't connect. I then started B, which did connect just fine. That was test #1. I then shut down all three, again, and shut down the router, as well. After all, something DID happen where I lost internet a bit mysteriously, for an hour or two Sunday. I don't know whether that was related, since I hadn't been using S during that time (though S was on, at the time). However, S couldn't see the internet after that until I rebooted it, as noted in post #13. So, I had some hint that the router might be related.

Once everything came back up, neither B nor S could connect to wireless anymore. A was fine because it's on an ethernet cable. This took a long time to work out, including a fair amount of time on the phone with the service provider. Eventually, they did solve the problem, though they also didn't quite identify it. The solution was to change the WiFi network password. This got B and S back on the network. Of course, you don't put in a password every time you connect to your home wireless network. The computers remember it, and since both B and S managed to lose the password at the same time, I can only assume that it was the router that actually lost the password. The password did show up in the router settings, but it's a doozy of a password, so if one character had been dropped, I likely wouldn't have noticed it.

The new password is 14 characters, symbols, numbers, upper and lower case, but when I connect with B (a Surface Pro 7), it is saying that I'm using an older security protocol which isn't totally secure. If I look at the connection, it's WPA2, so I don't think it's all that insecure.

In any case, after all that, I was back to where I started the morning at: A, B, and S are all on the internet. B can connect to S via SSMS or the application. A can see the two SQL Server instances on S using the code in post #1, but can't connect using either SSMS or the application. Also, all three were fine the last time I tried them, which was perhaps as far back as the 29th, which date I only know because that's the last time I had posted in this thread. I may have used the system a day or so after that, or maybe not, but it WAS working then.

I'm still not 100% confident in the router, but aside from the inability to connect to SQL Server, that's the ONLY issue I'm seeing, currently. I'm not seeing how the router could be impacting that.

I tested the telnet approach a couple different ways, largely because I have no experience with telnet (or at least not in a couple decades). First, I tried it with the right IP address and the right port. I got a blank screen, as if it had connected. To be sure, though, I tried with the wrong IP address, then again with the right IP address and the wrong port. As expected, both of those failed to connect. So, it appears that A can see S, A can telnet to the SQL Server port on S, but SSMS adamantly refuses to connect.

Is the failure to connect via SSMS instantaneous or does it take a few seconds?

If the latter, SSMS might use additional ports other than 1433 that are being blocked somewhere. To see, try to connect via SSMS, and while it is trying to connect, try doing a "netstat -an" from a command prompt and look for connections being made to the target IP address on ports other than 1433 that are in a state of something like "waiting".

It is taking some time, so I tried what you suggested. Nothing had the target IP address that I could see.

That seemed a bit unlikely anyways, since it isn't clear:

1) Why it would impact A but not B?
2) Why it would arise in the last few days?

As you stated earlier, B and S are wireless and A is wired, so that means A has a different connection route to S than B has to S. You haven't explicitly stated what network gear is in play here, so it seemed like a logical thing to test.

It IS a logical thing to test, and I do believe that the answer lies somewhere along these lines...except that it was working a week back, which confuses the issue considerably. This would be a whole lot more clear had A NEVER been able to connect. Note that this thread started with B not being able to connect, which turned out to be because the SQL Server services configuration had TCP disabled. I don't know why that solved B, but I did try disabling TCP again to see whether that got A back, but all it did was make it such that neither A nor B connected.

When I look at the networks that the computers are connected to, both S and B say they are connected to one network, while A says it is connected to a pretty generic "network 3". This may be something, but S and B are connected to a network I certainly recognize: It's the SSID for the wireless, so...yeah, they're connected to that. Of course, A can't be connected to that, because it isn't connected via wireless.

In a different thread I had started a while back (back in the days of Windows 7), which sapator linked to, I had an issue that turned out to be because of a homegroup setting. As far as I can tell, homegroup was removed from Win10, and is in no way accessible. Interestingly, in the firewall rules, I do still see a Homegroup rule, but it is grayed out and I can't interact with it in any way, so...it may be some home rule issue. I also notice that the issue started right around January first....Could Brexit be the reason?

In all seriousness, though, there may be something to the homegroup. There are three services enabled for SQL Server on S: Shared memory, Named Pipes, and TCP. The first two appear to be enabled by default, while TCP appears to be disabled by default. I know nothing about how the first two work. I'm wondering if they could be interfering, by some means, with A. After all, A connected just fine back before the TCP service was enabled. It connected fine after TCP was enabled, too, but the data on that is pretty sparse, as I really only tested it for a day or so, at most.

I guess I'm just rambling at this point. Time to go try disabling a few other services and give it a test.

Okay, I turned off everything except TCP, and nothing changed. B still connects, A still does not.

That still didn't answer the question of what gear (physical) is in play. Do you have a single wireless router that also has ethernet ports in it that B and S connect to wirelessly and A connects to the ethernet port on? If so, does this router have separate subnets defined for the ethernet side of things vs. the wireless side of things? If so, are there traffic rules in play that only allow certain traffic (certain ports) to communicate from the ethernet side of things to the wireless side of things and vice versa?

I have no experience with the homegroup stuff or with Windows 10 networking in general (I presume they've made it "easier" by hiding as much of the actual configuration behind configuration wizards and fancy UI nonsense), so I can't offer any assistance for any of that.

But the fact that your wireless password got reset is a big indicator that some major changes (factory reset?) happened on your wireless router. Digging through the config on that device would be the first place I would start, since it is possible that something that was previously allowed on that device has reverted to not allowed because of whatever happened on it.

Yeah, you described the hardware correctly, and that's one possible issue. The router is both wired and wireless...and also fairly new. I haven't seen anything about subnets in the setup, and certainly didn't do anything like that intentionally, but I'd have to go look to be sure.

The way I used to have it was that the internet came to a router with wireless turned off. A cable then went to a switch in the wall, with a line coming down to a wireless access point, which also had an ethernet cable to A. That meant that the router could drop out, but A, B, and S were still on a network, with no internet capability. That wireless system was probably higher quality than the new router, but I'm not sure. The new one is pretty good, and had the capability to serve all roles, so it currently has one line to the switch and one line to A, while also doing the wireless. This allowed me to remove the access point and unplug it. That happened weeks ago, though.

There was an issue with the old setup, which is what also pushed me to the current setup. I was having an issue with the access point...which was that I had forgotten the password, so I did a hard reset to factory defaults, which caused me to lose the internet entirely. That wasn't good. When I considered my options, I felt that the best arrangement was to do as I am currently doing. After all, why have the second device sipping electricity when it doesn't serve any clear role?

Anyways, the current setup was working without issue, until this. I still suspect the router because of the odd behavior Sunday evening, but I'm not sure that I dare mess with it any for a few days. I can't afford to lose the internet until after Thursday.

I found this article , maybe it can help a little? https://support.microsoft.com/en-us/...2-22e1694bb889

Now. Do you have any errors on the vent viewer? Any errors on the SQL logs?
Also (after you finish with needing the internet) can you try this command and the machines? "netsh winsock reset"

Finally can you check the server permissions? I'm not an expert at that, maybe someone else could help but would suggest for the moment give every single permission to the user and see if that works out,
also here is an article, it's not completely relevant to your issue but it has a lot of steps that you can go one by one to see if you can get anything out of it:
https://knowledgebase.apexsql.com/co...apexsql-tools/

Or....maybe the problem will just vanish...

AAAAAAAAAAUUUUUUUUUUUUUGGGGGGGGGHHHHHH!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Turned on the computer this morning: No internet.
Reboot.
Internet, but the keyboard wasn't working.
Reboot.
Internet and keyboard. Came on here, removed some spam, read a few posts in CC, came to this thread, checked the link from Sapator, decided to try something out, so fired up SSMS...and it connected, instantly, without issue.

Can it be more maddening than that? Dozens and dozens of tries, yesterday. Several reboots. Checking this, checking that, checking everything I could think to check including my checking account, and NOTHING worked. The failure was consistent....and then....poof...it just goes away.

Oh yeah, and now that I think more about it, the problem existed for two days, at least, so it went through four or five reboots of A, as well as a dozen of S and one of the router.

The problem vanished when you read my post. That was the reason!
That also happens at work, some colleague calls me on the phone and in a few seconds, the problem vanishes!! (usual try plug in the printer that is "broken" before ) :D
But in reality, something was reset back in place. That's why I tossed a dice and told you to try a netsh winsock reset but yeah, problem solved.

I'm usually the one saying that, so I totally know what you mean. Maybe it's that old saw, "the client that represents themselves has a fool for a lawyer." Computers just need a second opinion.

To me this sounds a bit like Universal Plug and Play shenanigans, but who knows.

https://en.wikipedia.org/wiki/Universal_Plug_and_Play

I'm NOT saying this is resolved. I'd sure like it to be resolved, but it doesn't feel very resolved yet, to me.

How about removing all routes, networks etc and see if it fails again?

Yeah, that breaks it, but so what?

So, Does it come back after?

Here we are in early April. All has been well since Jan 6th. Can't say that I've used this system every day, but perhaps a few times a week, each week, since between then and now. I forget when I last used it, though it was certainly last Sunday, and I'm pretty sure at least once during this last week. So, today, I'm back to having A not connect, though S is fine and B is connecting fine. A is seeing the SQL Server(s) on S, fine, as well, it just refuses to connect, either through SSMS or the program.

So, the state of the matter is: This problem will go away for months at a time, but then will spontaneously come back. When it comes back, it can stick around for days, is immune to reboots, and pretty much anything I can think of doing, and then it will vanish as mysteriously as it arrived.

In general, I feel that it is probably a router issue, since the problem began when I got the new router. Also, there are times when I boot up A and it can't get to the internet at all. This time, it's kind of good timing, though. I'm about to leave for a week, so perhaps it can sort itself out in the meantime.

If anybody has any further suggestions on the topic, I'm open to them...but I won't be responding until I return.

I think we covered this as thoroughly as we could.
I'm just writing this because you said of no internet. So a last suggestion is to check your local time in the servers and rooter (if it has that option) but other than that, as you've said, is possibly the rooter.

Quote:

---

Originally Posted by Shaggy Hiker

If anybody has any further suggestions on the topic, ...

---

Have seen similar symptoms before... a likely reason could be an "identical IP on a different device"
(in our case it was the similar Server-IP in a Test-VM we occasionally started, which then threw off our network behaviour)

Olaf

If you have multiple instances of SQL on some of these servers, then "locally" they are immediately found, but "remotely" they are listening on dynamically assigned ports unless you specifically bind them with SQL config manager utility.

I've got three instances running on one machine and need the port #, but only "remotely" (and only on the "named" instances).

Data Source=STEVE-SURFACE
Data Source=STEVE-SURFACE\SQL2017,1491
Data Source=STEVE-SURFACE\SQLEXPRESS01,1492

The following script creates the test database and enables TDE.

USE [master];
GO

-- Create the database master key to encrypt the certificate
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'FirstServerPassw0rd!';
GO

-- Create the certificate we're going to use for TDE
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Cert for Test';
GO

-- Back up the certificate and its private key Remember the password!
BACKUP CERTIFICATE TDECert
TO FILE = N'C:\SQLBackups\TDECert.cer'
WITH PRIVATE KEY (
FILE = N'C:\SQLBackups\TDECert_key.pvk',
ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
);

Some interesting suggestions, but it'll be a few more days before I can dig out enough to actually try some things.

Quote:

---

Originally Posted by Shaggy Hiker

Some interesting suggestions, but it'll be a few more days before I can dig out enough to actually try some things.

---

Several notes:
1. Your laptops are probably using dynamic IPs assigned (leased) by the DHCP daemon on the WiFi router. These remain valid for 24h and usually get renewed to same IP. . . but sometimes might change. This might be the reason for sporadic connectivity issues you experience. Using telnet is good approach but first try ping utility both for IP probing and for name resolution too. If A (or B) is trying to access S but S resolves (on A) to wrong IP nothing will happen. Try pinging S from A, try nslookup S from A, try ipconfig /all on S to dump it's local IP address and compare.

2. You have both SQL 2012 and 2017 on S but insist you are not using instances. That's impossible! You do have two MSSQL instances on S and one might be default instance (but most likely it's not). You most probably have S\SQL2012 and S\SQL2017 instances (in COMPUTER\INSTANCE notation).

3. Windows firewall cannot block outbound traffic to an outbound port. Couple of suggestions above mention that the firewall on A can block it from accessing port 1433 on S -- that's not possible with built-in firewall AFAIK.

4. Speaking of firewall, the way to enable remote access to your instances is by adding sqlservr.exe to allowed programs, not by punching individual ports on S. So you use Start->Run: firewall.cpl and choose "Allow an app or feature through firewall" then press [Allow another app...] button and find the executable and then allow all traffic to/from it with the 3 checks (for private, domain and public).

Now how to find the executables for both MSSQL's? Use Start->Run: services.msc and find SQL Server (SQL2012) and SQL Server (SQL2017) where the instance name is in brackets and double click it and on the first tab look for "Path to executable" attribute which can conveniently be selected and copy/pasted.

5. While in services.msc make sure the copy/paste the executable for "SQL Server Browser" service to add it to allowed applications in the firewall. It's usually in "C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe" no matter the version of MSSQL installed or the number of instances on S. This is the service that makes your MSSQL instances discoverable but it needs to be discoverable itself by listening on an UDP port for broadcasts hence adding it to firewall is essential.

6. You do have to enable TCP/IP protocol in SQL Server Configuration Manager. Double click on TCP/IP in "Protocols for INSTANCENAME" node to open TCP/IP properties, on second tab scroll to the bottom last two settings are all you need to check/configure here -- either leave "TCP Dynamic Ports" the one assigned (these change on each service restart) or hardcode "TCP Port" (this is static and does not change). Whichever strategy you choose (dynamic or static) it does not matter much provided that your sqlservr.exe anf sqlbrowser.exe are in the firewall allowed application list. The SSMS and .Net provider will be able to discover the correct port to connect to if the server is in local network segment (reachable by UDP broadcasts). Only if you are configuring some complicated routing with linuxes involved you might have to go for static ports, otherwise it's a non-issue altogether.

cheers,
</wqw>

Is item #1 even a real possibility? After all, the issue is that A doesn't connect, but B does. If there is an IP address issue, shouldn't neither A nor B be able to connect? Also, considering that I shut everything down (including the router) and then started everything back up, wouldn't that resolve any IP issue? Or doesn't it make it very unlikely that this always ends up with A having an issue while B does not?

I don't believe a firewall issue is likely, as I see no reason why it would work fine for a few months, then fail. No changes were made, I was just working on other things. This is a very intermittent problem, but when it happens, it persists for days, apparently. I don't really have enough data to be certain.

As for #5, the services are discoverable, they just can't be connected to via A, though they can be via B.

And for #6, TCP/IP has been enabled from early on. Disabling it does stop both A and B from connecting, but enabling it doesn't fix the problem, since it has long been enabled.

I'm actually leaning towards something strange happening in hardware, but whether the culprit is the router or system A, I can't quite say. Every now and then, when I start A, it doesn't connect to the internet. Rebooting always solves this, and it only happens sporadically. Still, this suggests that there is some kind of intermittent issue between those two pieces of hardware. It seems to me that A sometimes has other issues, even more infrequently, but it's so infrequent that at the moment I can't remember what those issues are/were.

As for #1...

If you are on a remote machine and you are trying to connect to an instance on some server and it's failing...

And you are trying this connection with SSMS.

Then you must, just to have a complete list of eliminated possibilities, see what that instance is listening on for a TCPIP port. That is done with the SQL Server surface config tool - very quick and easy. On the server the instance is running on...

Once you have a port # that it's listening on live - at that moment - check and see if appending the port to the connection attempt from SSMS works.

If it does, make the ports static, use those new static ports, and see that the problem goes away.

Quote:

---

Originally Posted by Shaggy Hiker

As for #5, the services are discoverable

---

So you have the two sqlservr.exe and the sqlbrowser.exe in the firewall exceptions already?

Btw, rebooting the machine does not renew its DHCP lease.

cheers,
</wqw>

How about rebooting all three systems plus the router itself, all at the same time?

I never reboot unless the web browser is keeping a cache on you but that is not something you would do to yourself.

IPconfig /release
IPconfig /flushdns
IPconfig /renew

The golden rule.

Quote:

---

Originally Posted by Shaggy Hiker

How about rebooting all three systems plus the router itself, all at the same time?

---

Most DHCP servers save client leases in a file on reboot. Not sure about uDHCPd/dnsmasq on your router though.

cheers,
</wqw>

Ah, that makes it more likely. If it is retaining state through a reboot, then that would explain why the power cycle didn't do anything.

It still seems a bit odd that B has no issues connecting to S while A does. They both go through the same router, though one is wired and the other (B) is wireless.

I hope to get back to this, sometime this week, though I've blown it for today, so I guess it will be later on this week.

Finally got back to this after all this time....and the problem went away, again.

Somehow, I doubt this is over.

Have you checked the timing?
I lately had issues at the office with a reseting computer because of a faulty motherboard battery that where a little like yours.
I'm not saying that it is likely but you can just do a quick check.
You can also do the IPconfig thingy. If it brings back the issue then we are getting somewhere.

Do you mean checking whether the system time is correct? If so, it is, but that's an interesting idea. If you aren't referring to the system timing, then what?

Yeah all systems and rooter(s).
If they are correct, you can scratch it out of your possible reasons list.