1

When accessing SQL server on a remote development PC via Management Studio the connection will consistently fail on the first attempt. After the failure I retry and can now successfully connect to the database. This isn't a huge issue, but when I have to connect 10 or 15 times a day it gets a little tedious.

I have a work-around already, but I'm concerned about any security implications that go along with the fix. In the Configuration Manager, under SQL Server Network Configuration -> Protocols for SQLEXPRESS -> TCP/IP -> IP Address tab, there is a setting called TCP Dynamic Ports and a port listed, and when I made an exception for this port in the local firewall the problem went away. Being a SQL Server neophyte I am not familiar with what this setting does. My specific questions are as follows:

  • Do i need to specify a port in this setting?
  • If so, does it require a firewall exception or is there a better way to circumvent this issue?
  • Is there a better fix for the issue so the first attempt succeeds, without needing to open holes in my firewall?
awilson53
  • 125
  • 1
  • 7

1 Answers1

4

Do i need to specify a port in this setting?

Yes there needs to be a port number listed there. The port that is listed is the port that was assigned on startup. Every time the machine restarts this port number will change.

If so, does it require a firewall exception or is there a better way to circumvent this issue?

Setup the Windows firewall to allow the application to open what ever ports it needs. Do this by setting up an access rule in the Windows firewall for the application sqlservr.exe.

Is there a better fix for the issue so the first attempt succeeds, without needing to open holes in my firewall?

Nope, you need to have holes in the firewall to allow the communication.

jscott
  • 24,484
  • 8
  • 79
  • 100
mrdenny
  • 27,174
  • 4
  • 41
  • 69