2

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:

Windows Defender Firewall settings

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:

SSMS connection error 26

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:

Sql Server Configuration Manager

4

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.

Windows Defender Settings

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.

  • 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.... ;) – feetwet Mar 16 '18 at 22:49
  • 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. – vic Mar 17 '18 at 9:47
  • @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. – feetwet Mar 17 '18 at 19:58
1

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)

  • 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.) – feetwet Mar 14 '18 at 21:00
  • 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. – vic Mar 14 '18 at 21:20
  • 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)? – feetwet Mar 14 '18 at 21:43
  • 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 Mar 15 '18 at 7:14
  • @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. – vic Mar 16 '18 at 15:57

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.