Programmatically Changing the Firewall Settings - SQL Azure Team Blog - Site Home - MSDN Blogs

Programmatically Changing the Firewall Settings

Programmatically Changing the Firewall Settings

Rate This
  • Comments 11

SQL Azure has two types of access control: SQL Authentication logins and passwords and a server side firewall that restricts access by IP address. This blog post will discuss how to programmatically modify the firewall settings. For information about programmatically creating logins, see this blog post.

Firewall

SQL Azure maintains a firewall for the SQL Azure servers, preventing anyone from connecting to your server if you do not give their client IP address access. The most common way to do this is via the SQL Azure portal. If you have used SQL Azure, you have used the portal to create firewall rules; you can’t connect to SQL Azure until you have granted your client IP access. Below is a screen shot from the portal:

clip_image001

Programmatically Changing the Firewall Rules

SQL Azure allows you to change the firewall rules by executing Transact-SQL on the master database with a connection to SQL Azure. You can add a firewall rule with the system extended stored procedure: sp_set_firewall_rule. Here is an example of the Transact-SQL of creating a firewall rule for a single IP address:

exec sp_set_firewall_rule N'Wayne Berry','206.63.251.3','206.63.251.3'

Here is an example of enabling the firewall for Microsoft services and Windows Azure:

exec sp_set_firewall_rule N'MicrosoftServices','0.0.0.0','0.0.0.0'

Note that every firewall rule must have a unique name and are not case sensitive. You can get a list of firewall rules by calling the view sys.firewall_rules. Here is an example of the Transact-SQL:

select * from sys.firewall_rules

The output of this command executed on my SQL Azure server (see in the portal screen shot above) viewed in the SQL Server Management Studio looks like this:

clip_image002

You can also delete a firewall rule using the sp_delete_firewall_rule system extended stored procedure:

exec sp_delete_firewall_rule N'Wayne Berry'

You can read more about these firewall extended stored procedures here.

Security Considerations

Only the server-level principal login, while connected to the master database, can configure firewall settings for your SQL Azure server. This is the same login as the administrator login found in the SQL Azure portal.

Another thing to note is that you must have at least one firewall rule before you can connection to SQL Azure; you need that connection to execute sp_set_firewall_rule and the other extended stored procedures.

From the Command Line

You can execute Transact-SQL against SQL Azure from the Windows command line using sqlcmd.exe. More about how to use sqlcmd.exe can be found in this blog post. Since you can execute Transact-SQL against SQL Azure from the command line, you can execute the firewall command above against SQL Azure from the command line. Using the command line you can script your firewall rules, along with your database creation scripts (see this blog post), schema creation, and schema synchronization.

From Windows Azure

Windows Azure can execute Transact-SQL against SQL Azure using ADO.NET; which means that you can programmatically add firewall rules to SQL Azure from Windows Azure. One of the nice things about doing this via Windows Azure is that Windows Azure “knows” the caller’s client side IP address.

One technique is to create a simple interface that allowed anyone that called a web page on your Windows Azure web role, to gain access to your SQL Azure account by adding their IP address to the SQL Azure firewall rules. You would want to make sure that the caller was authenticated by Windows Azure, using an authentication method of your choice. This technique would allow PowerPivot or WinForms users to grant themselves direct access to SQL Azure, by making a request to a web page. More about connecting to SQL Azure via PowerPivot here, and Winforms here.

Here is a little example code to get you started:

String clientIPAddress = Request.UserHostAddress;

using (SqlConnection sqlConnection = 
    new SqlConnection(ConfigurationManager.ConnectionStrings["SqlAzureMaster"].ConnectionString))
{
    sqlConnection.Open();

    using (SqlCommand sqlCommand =
        new SqlCommand("sp_set_firewall_rule", sqlConnection))
    {
        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

        sqlCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value 
            = clientIPAddress;
        sqlCommand.Parameters.Add("@start_ip_address", SqlDbType.VarChar).Value 
            = clientIPAddress;
        sqlCommand.Parameters.Add("@end_ip_address", SqlDbType.VarChar).Value 
            = clientIPAddress;


        sqlCommand.ExecuteNonQuery();
    }
}

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.

  • What if your ip address changes? Mine seems to be different every day. How do I connect to SQL Azure if the ip address is unpredictable?

  • Right - I have the same situation as Peter.  What if SQL Azure is just the backend data for a Access database which is distributed remotely (lots of IPs that change).  Any best practices here?  Is it sane to essentially turn the Firewall off by allowing ALL IPs?

  • Peter, Drew:  Did you read the section entitled: "From Windows Azure", it explains how you can allow your own user's to grant themselves access using an authentication schema that you provide.

  • Wayne - thanks for the prompt response.  I must have missed this section during my first pass through the article.  

    I am not clear on how adding the IP to the firewall rule through Windows Azure provides any additional security UNLESS you are forcing users to authenticate to the Windows Azure page using a DIFFERENT username/password than they use to connect to the SQL Azure database.  

    If the user has correct SQL Azure credentials than it's just an extra step to go to the Windows Azure page to have his/her IP address added to the firewall rule.  Using a separate set of login credentials (SQL Azure vs. Windows Azure) is a difficult sell for obvious reasons.

    Thanks in advance for your help and feedback.

    Drew

  • I understand that it is not a perfect solution.  Any good solution involves going through a portal where they have to provide double credentials and your exposure is limited.  Think of the IP address as a second password, that only you and they know and that is harder to impersontate.

    Leaving your firewall wide open is not good security.  If the user name is compromised (like sent in email), then anyone anywhere can preform a brute force attack on SQL Azure.

    Another option is running a VPN client on their box, that only allows access to your SQL Azure server.  This is technology that we don't have yet.  However, they could VPN into your network, and then you could open your external IP address to SQL Azure.  This way you don't have to open the whole range of IP address.

    If you have a suggestion about how you want the security to work, post it; I am all ears.

  • Thanks again for the thoughtful and well written response.

    I'm not sure I have any quality suggestions since I'm not a security expert.  Perhaps I am thinking of this more on the web layer (banking, email) than a database layer.  VPN could be an option but it's a difficult sell because we are in a very weakIT environment with limited resources (East Africa).

    A few ideas...

    1.  Use Windows Azure page as you suggested but have the person authenticate using their SQL Azure credentials and a secret question.  This might be a good compromise since it allows a user to answer a question about themselves instead of having to remember two passwords.

    2.  Setup a Windows Azure application similar to what is done with online banking where you have to identify yourself through another mechanism (email, text message...).  A user's IP is only added to the firewall rules once he/she is authenticated through that second communication medium.

    3.  Leave the firewall open.  Lots of web services are completely open - why couldn't we do this with SQL Azure?  At the end of the day I think we should assess risk based on probability of incident/impact of incident. If we are storing data that isn't sensitive then maybe it's nothing to worry about?

  • Drew:  All of these are great ideas.

  • How does one switch the firewall off? Or in other words allow access to everyone's IP Addresses? We are thinking of using SQL Azure in a WinForms app; where it will pull common catalog data from SQL Azure using standard SQLClient .net API.

    Problem is we will never know the IP Addresses of the customers using it.

    Thinking we need to do like a web role on windows azure that provides web services type access to the SQL Azure. Maybe that will work.

  • Add a rule from 0.0.0.0 to 255.255.255.255 and it will open the firewall right up.

  • i use the windows Azure to open chat rooms inside it but the problem there are no sound and cams not working too in the chat rooms . my VM is windows server 2008 .... need your help Mr Wayne

  • You can make a simple ASP.NET MVC or Webforms app published as an Azure website. This way, when working on the website, it doesn't matter if your IP is not in the list.

Page 1 of 1 (11 items)
Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post