Share via

Is your WA SQL Database open to the world?

  • Article

The introductory post in this blog series is located here

Windows Azure SQL Database has a firewall feature that allows you to selectively grant access to a range of client IP addresses. Any traffic that is not explicitly allowed access is rejected by the system. In order to allow web/worker role access (where there IP address may change on each deployment), many customers choose to allow the built-in Windows Azure Services option.

A typical view of customer's management portal looks like this:

 

     
 

     
 

     
 

     
 

     
 

     
 

     
 

     
 

 

 

 

However, many customers do not realize that by allowing any Windows Azure Services access they are making their database visible to all Azure tenants, regardless of the data center. A more secure practice is to turn off connections from all Windows Azure Service (by setting it to No) and then setting up specific firewall rules for the virtual IP address (VIP) of your web and worker roles which are static for the lifetime of the deployment. In other words you can tighten the security on your logical server to only allow connections from specific IP addresses that are valid for your deployments. The blog talks about couple of different techniques via which you can do that.

  • Use REST API calls within .Net code to manually create/update the firewall rule at the start of your application. The sample code for this is given here

  • Via Windows Azure PowerShell using the *-AzureSqlDatabaseServerFirewallRule cmdlets

Before we look at how to implement each method, be aware of the following:

Both approaches start by uploading a management certificate to the Windows Azure portal. The instructions for creating the creating an x509 certificate using makecert are outlined here.

The REST API approach depends on a private key (.pfx) and a password that are referenced within the source code (details are here). The web/worker role's startup code adds a new firewall rule for that instance, meaning that it would work seamlessly when you deploy a new package or if you dynamically scale up/down the number of instances. However, if the application itself was compromised, the attacker could access the certificate and password.

The PowerShell approach on the other hand is more appropriate for an administrators since the logic is contained in cmdlets and there no direct dependency on the certificate file as such since the settings are abstracted out of the local certificate store. But the administrator must manually run the PowerShell script each time a new package is deployed or a new role instance is started/stopped.

REST API

For the code approach I wrote a simple WA Cloud Service in C# (using Visual Studio 2012) which connects to SQL Database, reads some data and displays in a grid format.

Next I created a class named WebRoleCertAuth that simply wraps around the sample code for the REST API calls SetServerFirewallRuleAutoDetectClientIP and DeleteServerNameFirewallRule

Finally I called these methods in the Application_Start/End from Global.asax.cs    

Here is how the WA Portal looks before and after the rule is added

 Before

   

     
 

 After

   

     
 

PowerShell cmdlets

The PowerShell approach allows you to set/unset the firewall rules directly from your local machine using the following cmdlets from Windows Azure PowerShell

The pre-requisite is to be able to authenticate against the management certificates that you uploadedin the Windows Azure portal. You can either do this via Set-AzureSubscription cmdlet as outlined here.

 

     
 

     
 

 An alternative is to simply run the Get-AzurePublishSettingsFile cmdlet that prompts you to download the settings file from your subscription.

 

     
 

 Save the file on your local machine and then import it via Import-AzurePublishSettingsFile

 

     
 

The next step is to obtain the VIP of your cloud service via [System.Net.Dns]::GetHostAddresses cmdlet

$cloudSvcIp= [System.Net.Dns]::GetHostAddresses("mycustomerorders.cloudapp.net") |Format-Wide
-Property
IPAddressToString|Out-String

After that you can use the cmdlets to add, remove or modify existing rules. In my script here is how I am adding a new rule

# Get the IP Address for the cloud service

$cloudSvcName= Get-AzureDeployment -ServiceName "mycustomerorders" -Slot "Production" | Format-Wide
-Property
ServiceName|Out-String

$cloudSvcName=
$cloudSvcName.trim()

$cloudSvcIp= [System.Net.Dns]::GetHostAddresses("mycustomerorders.cloudapp.net") |Format-Wide
-Property
IPAddressToString|Out-String

 

 

# Enum all rules on the database server associated with subscription

$dbSrvName=Get-AzureSqlDatabaseServer | Format-Wide
-Property
ServerName|Out-String

 

 

$fwRules
= Get-AzureSqlDatabaseServer -ServerName $dbSrvName.trim() | Get-AzureSqlDatabaseServerFirewallRule

ForEach($i
in
$fwRules)

{

    #$fwRuleName= $i -Property RuleName

    if ($i.RuleName -like
$cloudSvcName)

        {

        #Remove old rule of the same name

        Remove-AzureSqlDatabaseServerFirewallRule –ServerName $dbSrvName.trim() -RuleName $fwRuleName

        #Optionally you could also use Set-AzureSqlDatabaseServerFirewallRule to update the IP address for the exisiting rul

        }

    }

 

#Insert the new rule here

    New-AzureSqlDatabaseServerFirewallRule –ServerName $dbSrvName.trim() -RuleName $cloudSvcName"_FWRule" -StartIpAddress $cloudSvcIp.trim() –EndIpAddress $cloudSvcIp.trim()

 

 

# Check the firewall rules again and you should see new rule added

Get-AzureSqlDatabaseServer | Get-AzureSqlDatabaseServerFirewallRule |Format-Wide
-Property
RuleName
-Column 1

 

 

In the next blog located here we shall look at options to move your data from on premise SQL Server to WASD.

References

  1. Windows Azure SQL Database Firewall

    https://msdn.microsoft.com/en-us/library/windowsazure/ee621782.aspx

  2. How to: Configure Server Level Firewall Settings

    https://msdn.microsoft.com/en-us/library/windowsazure/ee621783.aspx

  3. Authenticating Windows Azure SQL Database Management API Requests

    https://msdn.microsoft.com/en-us/library/windowsazure/gg715282.aspx
       

       
     

         
     

Author: - Rohit Nayak (@sqlrohit)

Reviewers: - Keith Elmore, José Batista-Neto

Escalation Services, Microsoft

Comments

  • Anonymous
    November 01, 2013
  1. Since Azure Websites goes thru public interfaces to interact with the db how do you secure those? Or do those public IP's never change? What happens when the Azure websites is shifted to another availability zone or restarted so Azure employees can do maintainece on that server?
  2. If you're using Auto Scale in Azure Websites how do make your firewall rules change as more servers are scaled out with Auto Scale? Then the oppisite. How to auto remove when the Azure Websites are scaled down?