4

What are some good links.articles on how to white list, with flexibility, IPs connecting to MS SQL server. (using win server 2008 r2, if it matters) Thnkas

EDIT:

I want to be able to whitelist IPs conecting to MS SQL Server via the firewall. I need the method to be "Flexible" so that i can change the list frequently. why? because i noticed recently in the database logs that there was an extreme number of failed login attempts from people just trying to brute their way in.
So any information on how to do this would be great. So far in the firewall i know know how to whitelist or blacklist single IPs per rule not entire lists. furthermore i would like to know if this will accomplish what i want. Again Thanks in advance

2
  • is this a general purpose sql server or is it for an application?
    – Jim B
    Feb 25, 2011 at 13:52
  • @Jim B im using the sql server on the same server as the web apps are running Feb 26, 2011 at 19:26

1 Answer 1

5

Would you like to do this through command line scripts or through the user interface? Not sure what you mean by flexibility, but I find it easiest to create firewall rules from the command line and then manage the list of whitelisted IPs through the user interface.

Assuming a new SQL Server installation, enabled Windows firewall, and no current allowance for SQL Server through the firewall, this command will set up a new rule with only the IPs you desire (run as Administrator):

C:\Users\Administrator>netsh advfirewall firewall add rule name="SQL Server Whitelist" dir=in action=allow localport=1433 protocol=tcp remoteip=1.1.1.1,1.1.8.10

In your case, just replace the list of IPs after remoteip= with a comma-separated list of the IPs you wish to whitelist. Then to manage the list, you can either run this command to update the list:

C:\Users\Administrator>netsh advfirewall firewall set rule name="SQL Server Whitelist" new remoteip=1.1.1.1,1.1.8.10

Or to avoid retyping the list every time you can use the user interface, under Start > Programs > Administrative Tools > Windows Firewall with Advanced Security. Then choose "Inbound Rules" and you will see your "SQL Server Whitelist" rule in the main window. Double click your rule, and choose the "Scope" tab to manage the allowed IPs.

Alternatively, if you like managing the list through the command line, you can create a separate rule for each IP.

C:\Users\Administrator>netsh advfirewall firewall add rule name="SQL Server Whitelist 1.1.1.1" dir=in action=allow localport=1433 protocol=tcp remoteip=1.1.1.1

C:\Users\Administrator>netsh advfirewall firewall add rule name="SQL Server Whitelist 1.1.8.10" dir=in action=allow localport=1433 protocol=tcp remoteip=1.1.8.10

You can then remove rules when you need to change the list like so:

C:\Users\Administrator>netsh advfirewall firewall delete rule name="SQL Server Whitelist 1.1.8.10"
3
  • 1
    by "flexibility" i meant that i will need to be able to change the list frequently Feb 21, 2011 at 6:42
  • @Letseatlunch, then why didn't you say so? We shouldn't have to guess. Feb 21, 2011 at 20:43
  • This deserves more upvotes. Really helpful. Feb 6, 2014 at 21:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .