How do I configure the Windows Firewall to allow specific computers to connect to a SQL Server Instance?

Question

I'm trying to allow only specific computers on my domain to connect to a SQL Server instance but I appear to be inadvertently blocking all computers from connecting.

When I go in to configure the firewall on the server hosting the database, after setting which computers I want to be able to access the instance. It blocks all of them. After setting the rule back to its original state, it works.

On the Firewall rule, I have set the following:

• General Tab
  • Action: Allow the connection if it is secure.
• Remote Computers
  • Only allow connections from these computers: Checked
  • The dialog box below is filled with the computer names I want: DOMAIN\PC1$, DOMAIN\PC2$, etc.

Setting this rule causes the connect to the server to time out after about 60 seconds and then comes back with the generic 'server does not exist or access denied' message.

What are the correct settings I need to use?

Solution

You can do this using the Windows Server 2012 built-in firewall and issuing simple and applicable Netsh AdvFirewall Firewall Commands from a command prompt (or batch script) run as administrator.

See "Scripts and Commands" below "Explanation". . .

---

Explanation

Essentially you'd allow inbound connections like so:

1. By the TCP port number the SQL Server instance is listening on the server

   • ^{[ localport = { any | Integer | rpc | rpc-epmap | teredo | [ ,... ] } ]}

     • ^{Specifies that network packets with matching IP port numbers matched by this rule. localport is compared to the Source Port field of an outbound network packet. It is compared to the Destination Port field of an inbound network packet.}

       • ^{Integer. Specifies the exact port number that must be present for the packet to match the rule. The port values can be individual numbers from 0 through 65535, a range, such as 5000-5020, or a comma-separated list of numbers and ranges.}

     ^{Multiple entries can be specified for localport by separating them with a comma. Do not include any spaces.}

     ^{If localport is not specified, the default is any.}

2. By allowing connections on this TCP port only from a specific (or a set of) IP address(es)

   • ^{[ localip = { Addresses } ]}

     • ^{Specifies that network packets with matching IP addresses match this rule. localip is compared to the Destination IP address field of an inbound network packet. It is compared to the Source IP address field of an outbound network packet.}

       • ^{IPAddress. Matches only the exact IPv4 or IPv6 address.}

     ---

     ^{Multiple entries can be specified for localip by separating them with a comma. Do not include any spaces.}

     ^{If localip is not specified, the default is any.}

---

Scripts and Commands

Command Line

netsh advfirewall firewall add rule name="Inbound 1433 Access Per IP Address" dir=in  protocol=tcp  action=allow localip=192.168.1.10,192.168.1.20,192.168.1.30

---

Batch Script Allow

ECHO ON
SET name="Inbound 1433 Access Per IP Address"
SET dir=in 
SET protocol=tcp 
SET action=allow 
SET localport=1433 
SET localip=192.168.1.1,192.168.1.2,192.168.1.1

netsh advfirewall firewall add rule name=%name% dir=%dir% protocol=%protocol% action=%action% localip=%localip%

---

Removing The Rule

netsh advfirewall firewall delete rule name="Inbound 1433 Access Per IP Address"

---

Further Resources

• Netsh AdvFirewall Firewall Commands

Comments

[Pimp Juice IT]

Jake - I just added an answer that should work and suffice for the need just as you explained, and I hope you find it helpful. I encourage you to ensure your servers have static local IP addresses assigned to them for easier administration of network lockdown requirements, and for to the most granular level of control with this respect. Most DHCP functions have a MAC address reservation, IP Allocation, or some feature to always assign the same IP address to a specific MAC address if you prefer to maintain at that level rather than the TCP/IP configs of the server NIC level.

[Jake]

I know this can be done using IP's, I've tested it using IP addresses in the Firewall and it works with no problem. I'm just wondering if there's any way to set it so I can specify computer names instead of IP addresses. I'll be meeting with a colleague later next week and I'll see how he wants to approach this.

[Pimp Juice IT]

I left an applicable comment in your question above too, but you could just let SQL Server take care who it allows to authenticate against it with security configurations at this level per account being even more granular than the per machine restriction. So have the FW allow all traffic to it on the SQL TCP port on your domain network for example but in order to authenticate to gain access to SQL Server resources, a valid login credential or security principal would need to authenticate to it. If you're looking for third party non-native solutions then I'm sure there are slews to use.

[Jake]

We're trying to keep this as simple as possible. Need to speak to my superior on Thursday, so I'll see what happens then.

[Pimp Juice IT]

Sounds good, letting SQL Server handle it as it does by default and allowing all DOMAIN IP addresses access should be the easiest I would think. You're going to have to maintain the SQL Server level security regardless so just letting that do what it does may be sufficient otherwise I would think the static IP address route would be easy as well.