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

sql-server-2012windows-firewallwindows-server-2012-r2

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?

Best Answer

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

Related Topic