Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
is this a general purpose sql server or is it for an application? –  Jim B Feb 25 '11 at 13:52
    
@Jim B im using the sql server on the same server as the web apps are running –  Letseatlunch Feb 26 '11 at 19:26
add comment

1 Answer

up vote 4 down vote accepted

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"
share|improve this answer
    
by "flexibility" i meant that i will need to be able to change the list frequently –  Letseatlunch Feb 21 '11 at 6:42
    
@Letseatlunch, then why didn't you say so? We shouldn't have to guess. –  John Gardeniers Feb 21 '11 at 20:43
    
This deserves more upvotes. Really helpful. –  Brian MacKay Feb 6 at 21:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.