RSS

How to do WhiteList IPs – MS SQL Server

10 Dec

WhiteList

You will need to whitelist your IP on the server if you want to remotely manage your databases with software such as Microsoft Server Management Studio.

There’re two ways to do that, the first one is through command line and the second one is through UI.

Let’s show how to do it using UI:

  • Go to your Control Panel.
  • Go to Administrative Tools.
  • Double-click on “Windows Firewall with Advanced Security“.
  • Click on “Inbound Rules” which is located at the left pane.
  • Select your rule that’s related to Microsoft SQL Server, which is named “MSSQL Server
  • Click on the “Properties” option which is located at the right pane.
  • You can follow this figure to complete these steps.

WhiteList-SQL

  • If you didn’t find this rule in your firewall rules, you have to create it manually, by clicking on “New Rule” at the top of the right pane, then choose “Program” and click Next, then specify your SSMS physical path on your HDD and click Next, then choose “Allow the connection” and click Next, then apply this rule on “Domain, Private and Public” and click Next, finally give this rule a name and a description if you want to do and click Finish.
  • Now you can apply the previous steps in the last figure on your new rule.

If you want to do it by using command line, follow this:

  • 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=100.200.100.50,100.200.100.51
  • If you want to add multiple remote IPs, 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=100.200.100.55,100.200.100.57
  • If you want to remove this rule, just run this on command line:
    • C:\Users\Administrator>netsh advfirewall firewall delete rule name=”SQL Server Whitelist”
 
Leave a comment

Posted by on December 10, 2013 in MS SQL Server

 

Tags: , , , , , , , , , , , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: