Programmatically Changing the Firewall Settings

[This article was contributed by the SQL Azure team.]

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:

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:

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.