1

If I've understood what I've read, all you need to do in order to allow remote access to SQL Server is to open TCP port 1433 in Windows firewall. But that will leave your server wide open to the entire internet and SQL Server itself provides no further tools to fine-tune the source of connections.

Is there a way to grant access to such port only to connections from a know static IP address?

  • 2
    That's what firewalls are for. Why do you think a firewall will not do here? – spaghettidba Jul 8 '16 at 13:03
  • @spaghettidba Sorry, I'm not sure about what part of my question suggests I want a no firewall solution. I'm asking for specific directions using the integrated firewall in Windows Server 2012 R2 (I couldn't find an existing tag and didn't dare creating one). – Álvaro González Jul 10 '16 at 13:42
2

I use Windows Firewall and Powershell for this. Unless of course you want to get something like ScaleArc to act as a reverse proxy. Your options are really going to be rather limited to something that utilizes one of those technologies. Firewall/IPSec or a reverse proxy.

This is the outline of the script I use to verify ports the rule exists in this environment and add it. Specify -RemoteAddress to limit access to only certain IPs:

New-NetFirewallRule Docs

-RemoteAddress<String[]>
Specifies that network packets with matching IP addresses match this rule. 
This parameter value is the second end point of an IPsec rule and specifies the computers that are subject to the requirements of this rule. 
This parameter value is an IPv4 or IPv6 address, hostname, subnet, range, or the following keyword: Any. 
The acceptable formats for this parameter are: 
-- Single IPv4 Address: 1.2.3.4 
-- Single IPv6 Address: fe80::1 
-- IPv4 Subnet (by network bit count): 1.2.3.4/24 
-- IPv6 Subnet (by network bit count): fe80::1/48 
-- IPv4 Subnet (by network mask): 1.2.3.4/255.255.255.0 
-- IPv4 Range: 1.2.3.4 through 1.2.3.7 
-- IPv6 Range: fe80::1 through fe80::9 
Note: Querying for rules with this parameter can only be performed using filter objects. See the Get-NetFirewallAddressFilter cmdlet for more information

.

Powershell Script:

##Internal SQL Endpoint for AGs to communicate together. Needed on all nodes.
$LocalPortNumber = '5122'
$PortDisplayName = 'SQL-AG-Endpoint'
$Protocol = 'TCP'
$PortCheck = Get-NetFirewallPortFilter | where {$_.LocalPort -eq $LocalPortNumber}
if ($PortCheck){  ##Check if exists))
write-host "Port $($LocalPortNumber) for $($PortDisplayName) already found. Not adding Firewall Entry" #-ForegroundColor DarkGreen
}
ELSE
{
write-host "Port $($LocalPortNumber) is not found. Adding now" 
New-NetFirewallRule -DisplayName "$($PortDisplayName)" -Direction Inbound –LocalPort $($LocalPortNumber) -Protocol $($Protocol) -Action Allow
}
  • 1
    Thank you very much, I know nothing about PowerShell but adapting your script was fairly straightforward. FYI, I've just learnt that you can set the IP address in the "Scope" tab of the Properties dialogue of the inbound rule. I was confused by *New Rule" wizard that shows up when you create a rule, which does not offer the option! (I'll possibly add that as another answer.) – Álvaro González Jul 11 '16 at 10:49
  • Excellent. Thank you for expanding on it. +1 – Ali Razeghi Jul 11 '16 at 16:55
2

Windows firewall allows to filter by IP address out of the box, it's just that you aren't offered the choice in the New Rule wizard. You have to create a rule first and then edit its properties:

Properties

Scope

1

You can control this via your network and your routing/switching infrastructure. Whenever I've had similar situations, our network team would limit access to the subnet that SQL is on to certain subnets. For example, you want your internal developers to have access to the dev systems but not the production systems. Instead of only limiting this by Windows ID or SQL Users, you can also open up a route/tunnel for the subnet that SQL is in, to/from the subnet that your developers are in. So you not only are controlling access by Windows/SQL accounts, but also by subnet/network.

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.