Unblocking Windows 10 Defender Firewall for MS SQL Server 2014

Question

I have an instance of MS SQL Server 2014 (call it MSSQL) on a Windows 10 machine (call it WINTEN).

Running SSMS on WINTEN, I can connect to both the local SQL Server and also to SQL Server instances on other machines on the private network.

However, I cannot make connections from SSMS on any other machine to the SQL Server instance WINTEN\MSSQL unless I completely turn off Windows Defender Firewall.

Following all instructions I could find I added two Inbound Rules to the Windows Firewall on WINTENas shown here:

However, even with those rules in place, as soon as I enable the Windows Defender Firewall all SSMS connections to WINTEN\MSSQL fail with the following message:

What is the correct way to configure Windows Defender Firewall to allow other private network machines to connect to MS SQL Server?

---

Update per first suggestion: I already had TCP Port 1433 Enabled for the SQL Server as shown here:

Solution 1

Open up services and find the following entries:

SQL Server Browser

SQL Server (WINTEN)

Go into their properties and make a note of the paths to the executables.

In Windows Firewall with Advanced Security create 4 new inbound rules which allow the connection both these programs for TCP and UDP.

I normally do this via the standard Windows Firewall (firewall.cpl) which will create these rules for you with less work. Open firewall.cpl, then choose "Allow an app or feature through Windows Firewall". Choose Allow another app and add the two executable paths found previously.

The reason you need to allow the program rather than a port through the firewall is that although the SQL Browser is always on port 1433, your SQL Instance will be on a random/dynamic port which the SQL Browser will direct the connection to. If you wish to allow ports rather than programs then you would need to change the SQL Instance to a default instance and force it to use a fixed port with the SQL browser disabled.

Please also check that your SQL Browser Service is configured to start automatically at startup and that in SQL Configuration Manager that TCP/IP is enabled for your SQL Instance.

Solution 2

Open the Sql Server Configuration Manager on WINTEN.

In the SQL Server Network Configuration, choose your instance MSSQL and check the settings for TCP/IP.

Make sure that the protocol is enabled and that the server is listening on port 1433. (Tab IP Addresses, section IPAll, field TCP Port)

Comments

[feetwet]

Thanks: In the course of trying to get this to work I did enable and set that port. I just added screenshot to the OP. (I assume that without that inbound connections wouldn't work even with the Firewall wide open.)

[vic]

I assume that the interface "IP8" is not relevant to you because it's clearly not connected to your network. So, the only other thing I can think of trying is to go and remove the entry TCP Dynamic Ports, then make sure you restart the server. Also, try connecting using the IP address and let me know if you get the same error message.

[feetwet]

Removing the TCP Dynamic Ports entry did not work. But I am able to connect instantly using the server's LAN IP address! Does that illuminate the problem(s)?

[vic]

You can rule out that your problem has anything to do with SQL Server or the respective firewall settings. My guess is that you are not in a domain but in a Windows workgroup and thus need to open Windows filesharing and Netbios ports on the firewall for this to work. Try opening TCP/UDP 135-139 and TCP/UDP 445 for your private zone.

[vic]

@feetwet Do you have any update on your situation? If my last remarks helped, I'll add them to the answer and we can mark the issue as solved.

[feetwet]

Sorry, just got back to this problem and this machine: You are correct that it is not on a Domain; it's on a workgroup and connection is classified as "Private." I'm looking at the Firewall settings and for this "Private" profile already I see Allow for Program "System" on UDP 137, 138, and TCP 139, 445. Are the other ones necessary? There must be a better way to control these permissions than manually entering such a litany of rules. (None of the other Windows 10 machines are having this problem.)

[feetwet]

Yep, following the process you described in Firewall.cpl was straightforward and finally got this working! Now, if only we could get a database, O/S, and firewall all developed by the same company, just imagine how cool it would be: When a user configures the database to allow remote connections, it could automatically check to determine whether the firewall ports are open, and maybe even prompt the user to allow it to open the necessary ports automatically! I know, I know, my vision exceeds my grasp.... ;)

[vic]

Some things don't add up here. 1st, the browser is on UDP port 1434, not 1433. 2nd, OP confirmed that he set a fixed port of TCP 1433 for his instance so the sql browser should not be necessary.

[feetwet]

@vic – that may be true, and this solution might be adding more than is necessary, especially given where my previous efforts had taken me. I can't verify from a "clean" condition, but I'm guessing that if one were starting from scratch with this problem this would cover all bases.

[Grant Birchmeier]

I don't understand why every Steam game I install can set firewall rules automagically, but SQL Server doesn't have a simple wizard for it.

[pholcroft]

@GrantBirchmeier I think they went for a secure by default approach, even TCP/IP is disabled by default. It's probably the right approach, databases usually contain important data.