Windows Azure SQL Database : How to manage your firewall rules using Windows Azure SQL Database Management API (REST)

 

In addition to Windows Azure Management Portal, SQL Commands or PowerShell you can also manage your Windows Azure SQL Database using SQL Database Management  REST API.

In this article I’ll show you how to connect, authorize and execute REST API commands from your applications.

First of all you need to Create a Management Certificate and upload it to Windows Azure Management Portal.

 

1. Create a Management Certificate for Windows Azure, start your Visual Studio 012 Developer Command Prompt or Visual Studio 2010 Command Prompt as a administrator and run the command below by updating <CertificateName>. You’ll have your X.509 v3 certificate with a 2048 bits key length.

 makecert -sky exchange -r -n "CN=<CertificateName>" -pe 

 -a sha1 -len 2048 -ss My "<CertificateName>.cer"

2. Login to your Windows Azure Management Portal and upload this certificate to Settings > Management Certificates section.

Then you need to export this certificate in PFX format to attach in your calls

3. Run certmgr.msc in your computer

4. In the Microsoft Management Console, in the console tree, expand Certificates, and then expand Personal.

5. In the details pane, click the certificate you want to manage.

6. On the Action menu, point to All Tasks, and then click Export. The Certificate Export Wizard appears. Click Next.

7. On the Export Private Key page, click Yes, export the private key. Click Next.

8. On the Export File Format page, select Personal Information Exchange – PKCS #12 (.PFX) . Click Next.

9.On the Password page, type and confirm the password that is used to encrypt the private key. Click Next.

Please note that PFX file and password are important. You’ll need this password while attaching PFX file in REST API calls and anyone who has PFX file and password can make authenticated calls to your subscription if the certificate is uploaded in your Windows Azure Management Portal.

 

10. Follow the pages of the wizard to export the certificate in PFX format.

 

Now, you can add REST API calls in your application. You can use this code by modifying the value of subscriptionId, certFilename, certPassword and servername variables.

The sample code makes a REST API call by attaching PFX file with password to get the list of Server-Level Firewall Rules.

 

 string subscriptionId = "[YourSubscriptionID]";
string certFilename = "[PFX File Path]";
string certPassword = "[CertPassword]";
string serverName = "[SQL Database Server Name]";

string RESTAPI = "https://management.database.windows.net:8443"
string url = string.Format(
 "{0}/{1}/servers/{2}/firewallrules", 
 , RESTAPI, subscriptionId, serverName
);

HttpWebRequest webRequest = HttpWebRequest.Create(url) as HttpWebRequest;
webRequest.ClientCertificates.Add(
 new X509Certificate2(certFilename, certPassword)
);
webRequest.Headers["x-ms-version"] = "1.0";
webRequest.Method = "GET";

HttpWebResponse webResponse = (HttpWebResponse)webRequest.GetResponse();
StreamReader reader = new StreamReader(webResponse.GetResponseStream());
string str = reader.ReadLine();
while (str != null)
{
    Console.WriteLine(str);
    str = reader.ReadLine();
}

Sample output will be in XML format as below :

 

 <FirewallRules xmlns="https://schemas.microsoft.com/sqlazure/2010/12/">
  <FirewallRule>
    <Name>AllowAllWindowsAzureIps</Name>
    <StartIpAddress>0.0.0.0</StartIpAddress>
    <EndIpAddress>0.0.0.0</EndIpAddress>
  </FirewallRule>
  <FirewallRule>
    <Name>ClientIpAddress_19216811</Name>
    <StartIpAddress>192.168.1.1</StartIpAddress>
    <EndIpAddress>192.168.1.1</EndIpAddress>
  </FirewallRule>
</FirewallRules>

 

Using SQL Database Management REST API, you can :

• Create/Update a Server-Level Firewall
• Create/Update a Server-Level Firewall with IP Detect (This is useful for applications that are running on computers with dynamics IP, whenever you call this command Server-Level Firewall is updated with the requestor IP)
• List Server-Level Firewall Rules
• Delete Server-Level Firewall Rules

 

For a general overview of securing your Windows Azure SQL Database please check my previous article : Secure Your Windows Azure SQL Database.

 

You can access to SQL Database Management REST API Reference for full list of commands.

 

Start using your free Windows Azure trial Windows Azure Free Trial