2

I have a SQL server 2014 instance running on Windows 2012 R2. If I turn off the Windows FW for domain, I'm able to connect to the SQL instance remotely using SSMS. If I turn it on, I cannot connect. I get an error stating the connection timeout has expired. I have added SQL rules for 1433 and 1434 on the FW to no avail. I have even gone as far as to add any/any rules inbound and outbound on the server FW, also to no avail. It seems as though regardless of the FW settings, if it's on, I can't connect to SQL remotely. Any help would be greatly appreciated!

  • Stupid question, but have you verified that TCP/IP is turned on for the SQL Server instance? I've been bit by that one more than once in weird ways. – user Oct 4 '16 at 15:40
  • Have you enabled logging of dropped packets in the Windows Firewall and then reviewed the logs after an unsuccessful attempt? – Mass Nerder Oct 4 '16 at 16:20
  • 1
    Yeah, TCP/IP is enabled. I also enabled logging of dropped packets, but oddly enough I don't see any indication of that in the log file. – D.Ox Oct 4 '16 at 17:55
2

The principal steps to allow access are:

  1. Configure the Database Engine to use a specific TCP/IP port. The default instance of the Database Engine uses port 1433, but that can be changed. The port used by the Database Engine is listed in the SQL Server error log. Instances of SQL Server Express, SQL Server Compact, and named instances of the Database Engine use dynamic ports. To configure these instances to use a specific port, see Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager).

  2. Configure the firewall to allow access to that port for authorized users or computers.

| improve this answer | |
  • Although I agree that these steps are correct, my issue lies in the fact that even with FW rules to allow 1433 (that is the port being used), SSMS still can't connect. As stated, even with ANY/ANY rules for inbound and outbound setup, SSMS still can't connect. I'm looking for suggestions other than the standard configuration documentation. – D.Ox Oct 5 '16 at 14:22
1

Ultimately the issue was resolved by going to SSCM, SQL server network configuration, protocols for MSSQLServer, TCP/IP and setting the option to listen on all IPs to "yes". Important to note that even with this set to "no", I could still connect if the FW was turned off.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.