Sql-server – how to whitelist IPs connecting to MS SQL Server with firewall

sql serversql-server-2008whitelistwindows-server-2008

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

Best Answer

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"
Related Topic