April 12, 2017 at 2:54 pm
On my host I can connect to two instances by typing:
. (this connects to the default instance of a 2008 R2 Sql Server)
.\SS2016 (this connects to the named instance of a 2016 Sql Server)
On my laptop I can connect to each instance so I know the ports are opened correctly.
hdcvs-lims-sql (this connects to default on host)
hdcvs-lims-sql,49559 (this connects to named instance 2016 Sql Server if I specify the port)
Why won't this connection string work?
hdcvs-lims-sql\SS2016 (host name, instance name which is using port 49559)
There is a 2008 R2 Sql Browser running. Won't it also handle the 2016 instance? How does an instance name get resolved to the correct port?
I'm much more a developer than a network or IT guy so any help is appreciated.
April 12, 2017 at 3:17 pm
As a thought - does connecting as hdcvs-lims-sql\ss2016, 49559 work? I expect that it will.
If so, then it is likely your firewall is blocking connection on port 1434 (the SQL Server Browser Service Port). To test this (short term), try turning off the firewall. If you can connect by the name (hdcvs-lims-sql\ss2016) then it is a firewall issue and you will need to allow port 1434. If you still can't connect, try restarting the browser service as it only reads the config at launch time (I believe? I could be wrong on this).
But I am fairly confident the problem is the firewall. I am pretty sure the browser service uses UDP and just listens on port 1434 and SSMS, if you try to connect to a named instance without specifying the port, sends a UDP packet on port 1434 to the server with the named instance. When the service gets the packet, it checks the registry to see the port and sends that back and SSMS then connects via the port.
So even if the ports are open for SQL Server instances, you still need it open for the browser service.
So the TL;DR version:
1 - try restarting the browser service
2 - make sure port 1434 is open in the firewall (pretty sure just UDP needs to be open, but if that doesn't work try TCP as well)
3 - after opening the port, try restarting the browser service
4 - post back your results (so others know if it worked or not) If it didn't work, we can do more troubleshooting.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 12, 2017 at 3:31 pm
I turned off the firewall and tested connecting from my laptop to the host with "hdcvs-lims-sql\ss2016" and it worked! That implies sql browser is working and likely that 1434 UDP was blocked. I went back into windows firewall and add the SSMS app and turned on the firewall. Everything works with the firewall on now! I'm using Windows Server 2012 and it appears they simplified port configuration down to add apps instead of opening ports.
Thanks for your help.
April 12, 2017 at 4:12 pm
Glad I could help. I knew you could add programs through too, I just personally like having the port instead of the whole program as it feels like I have more control that way. Plus I'm a little old school with firewall and port configs and have had to work with hardware firewalls which (at least the ones I've used in the past, nothing recent) do not allow for you to pick programs.
In the event that you need this outside facing (which I can think of no good reasons for having the SQL browser service visible to the entire internet), you'd need to set it up with the port on your hardware firewall.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 13, 2017 at 7:59 am
After applying 90 security updates and restarting the host I could not connect to the named instance without specifying the port. I went back into the "Windows Firewall with Advanced Security" app and opened port 1434 (sql browser) for domain users and now it works. The port assignment makes me feel more confident about the solution too. Would adding SQL Browser as an app have worked as an alternate solution? Maybe.
April 13, 2017 at 8:10 am
I betcha windows saw the app get changed when you did windows updates so it thought it was a different app (the hash of the file didn't match up).
I imagine it does this to prevent virus infected exe's from getting the same permissions that the original had.
Mind you, this is just a guess. I am not positive how that all works behind the scenes.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply