Securing Production #Azure SQL Databases

The Challenge

As developers, we are up against odds that push us to make trade-offs in order to go into production on time. More often than not, it’s a race where security becomes an afterthought.

Securing Azure SQL Databases

Security mechanisms come in many flavors. It is a requirement that needs to be defined and implemented on day 1. These rituals (policies and practices), must become natural in your application life cycle management. Consider these as a starting point from which you can develop your own security practices.

• Do not use the default user for development, testing or for deployments
  • Create a user specifically for deployments (can perform schema alterations)
  • Create a user on a per application basis (cannot alter schema and has limited write access)
  • Create a user for support investigations (this should be read-only)
  • Create individual accounts for members of DevOps who will need to act upon the database. (these accounts should have limited write access)
• Reference data should be read-only (immutable versions) and should only be updated through deployments. This type of data can be stored in NoSQL data services to augment the overall scalability of your application.
• Enable Auditing for Azure SQL Database, this feature will give you deep insight in how the database is manipulated and about how it is used.
• Use SQL Database Projects to design, build, version and deploy
• Use schemas to segregate tenants, reference data, activity data and resouce (shared) data.
• Use schemas to keep track of ownership chaining
• Encrypt connection string passwords at rest
• Use strong passwords
• Set Trusted_Connection=False in the connection string. This forces server certificate validation
• Set Encrypt=True in the connection string to force the client to use SSL
• Ensure that you are covered against SQL Injection
• SQL Database Firewall rules should block everything except the consuming applications
  • SQL Database Firewall rules should be set on both the Server and Database

Automating Azure SQL Server Firewall Rule Maintenance

All too often our Azure SQL Server firewall rules are riddled with unused and forgotten entries.

Although we try to be vigilant, we are human and can forget to remove unnecessary firewall rules. These rules, become attack vectors that can be leverage against our application and we should proactively do everything we can to reduce potential risks. This vulnerability can affect business continuity in two ways. The first is a malicious attack meant to steal or destroy data. The second way business continuity can be affected is by human error. Imagine for a moment that a developer updates a database and targets the production database by mistake, the results can be disastrous. Rolling back isn’t always possible and downtime can be costly. On the other hand, if the Azure SQL Server firewall rules are maintained, pruned and specifically targeted at the consuming applications, developers would be blocked from targeting the wrong database during their development cycle. In order to update the database, developers would need to manually add a firewall rule to the Azure SQL Server and target the database.

Protecting SQL Database instance against human error got me thinking and I decided to write a runbook that could be schedule using Azure Automation.

<#
.Synopsis
   The purpose of this script is to maintain a clean list of firewall rules
   for Azure SQL Database Server. It will add a rule for each Role it finds
   within the targeted Cloud Service. It will update rules that are out of
   sync with the deployed cloud service. Then it will delete all unrelated rules.
.EXAMPLE
   Set-CloudServiceSqlDatabaseFirewallRules -AzureCredentialsName 'runbooks' `
                                            -SqlServerName $SqlServerName `
                                            -CloudServiceName $CloudServiceName `
                                            -DeploymentSlot Production `
                                            -Verbose
#>
workflow Set-CloudServiceSqlDatabaseFirewallRules
{
    Param
    (
        [parameter(Mandatory=$true, `
                 HelpMessage = 'The name given to the Windows PowerShell Credentials located# in the Assets of this Azure Automation instance')]
        [String]
        $AzureCredentialsName,

        # Azure SQL Server name
        [parameter(Mandatory=$true, `
                 HelpMessage = 'The name of the Azure SQL Server')]
        [string]
        $SqlServerName,

        # Azure Cloud Service Name
        [parameter(Mandatory=$true, `
                 HelpMessage = 'The name of the Azure Cloud Service')]
        [string]
        $CloudServiceName,

        # Azure Cloud Service Deployment Slot
        [parameter(Mandatory=$true, `
                 HelpMessage = 'The Azure Cloud Service deployment Slot')]
        [ValidateSet('Production', 'Staging')]
        [string]
        $DeploymentSlot
    )

    $Credentials = Get-AutomationPSCredential `
                       -Name $AzureCredentialsName

    Add-AzureAccount `
       -Credential $Credentials

    $rules = Get-AzureSqlDatabaseServerFirewallRule -ServerName $SqlServerName
    'Rules Obtained from Azure SQL Server'
    $rules

    InlineScript
    {
      $instances = Get-AzureRole -ServiceName $Using:CloudServiceName -Slot $Using:DeploymentSlot -InstanceDetails `
                 | Select-Object ServiceName,  @{Name="Vip";Expression={$_.InstanceEndpoints[0].Vip}}

      $instances | `
      ForEach-Object {

        $name = $_.ServiceName

        $vip =$_.Vip

        $rule = $null

        $rules = Get-AzureSqlDatabaseServerFirewallRule -ServerName $Using:SqlServerName

        if($rules)
        {
           $rule = $rules | Where-Object {$_.RuleName -eq $name}
        }

        if($vip)
        {
          if($rule)
          {
            if($rule.StartIpAddress -ne $vip -and $rule.EndIpAddress -ne $vip)
            {
              Set-AzureSqlDatabaseServerFirewallRule -ServerName $Using:SqlServerName `
                                                     -RuleName $name `
                                                     -StartIpAddress $vip `
                                                     -EndIpAddress $vip `
                                                     -Verbose
            }
          }
          else
          {
            New-AzureSqlDatabaseServerFirewallRule -ServerName $Using:SqlServerName `
                                                   -RuleName $name `
                                                   -StartIpAddress $vip `
                                                   -EndIpAddress $vip `
                                                   -Verbose
          }
        }
      }

      $rules = Get-AzureSqlDatabaseServerFirewallRule -ServerName $Using:SqlServerName

      $rules | `
      ForEach-Object {
        $name = $_.RuleName

        $instance = $instances | Where-Object {$_.ServiceName -eq $name}

        if(!$instance)
        {
          Remove-AzureSqlDatabaseServerFirewallRule -ServerName $Using:SqlServerName `
                                                    -RuleName $name `
                                                    -Verbose
        }
      }
    }

    'Updated on Azure SQL Server'
    $rules = Get-AzureSqlDatabaseServerFirewallRule -ServerName $SqlServerName
    $rules
}

Executing this runbook on a regular schedule requires us to set up credentials and a schedule in Azure Automation Assets. My earlier post about scaling Azure Cloud Services up and down like clockwork can help you get up and running.

Everytime Set-CloudServiceSqlDatabaseFirewallRules is executed, it will give you an output similar to the following. Let’s walk through the output and understand what we can extract from it. The first part is a list of the Azure SQL Server firewall rules prior to maintenance. Then it will list all removed and added firewalls rules. Finally it will list the updated set of Azure SQL Server firewall rules.

Rules Obtained from Azure SQL Server

RuleName       : ClientIPAddress_Alex_home
StartIpAddress : 69.130.90.185
EndIpAddress   : 69.130.90.185
ServerName     : bnv4xb0akd
PSComputerName : localhost

RuleName       : ClientIPAddress_Alex_work
StartIpAddress : 69.130.90.19
EndIpAddress   : 69.130.90.19
ServerName     : bnv4xb0akd
PSComputerName : localhost

RuleName       : console
StartIpAddress : 23.99.200.245
EndIpAddress   : 23.99.200.245
ServerName     : bnv4xb0akd
PSComputerName : localhost

1/8/2015 9:25:05 AM, Verbose: 606a4214-a079-47a8-9105-f65dc92f9e8f:[localhost]:Removing firewall rule "ClientIPAddress_Alex_home" for Microsoft Azure
Sql Database server "bnv4xb0akd".

1/8/2015 9:25:06 AM, Verbose: 606a4214-a079-47a8-9105-f65dc92f9e8f:[localhost]:Removing firewall rule "ClientIPAddress_Alex_work" for Microsoft Azure
Sql Database server "bnv4xb0akd".

Updated on Azure SQL Server

RuleName       : console
StartIpAddress : 23.99.200.245
EndIpAddress   : 23.99.200.245
ServerName     : bnv4xb0akd
PSComputerName : localhost

Once the runbook execution is complete, the Microsoft Azure management portal should look much cleaner. The newly created firewall rules will have the same name as the Cloud Service that they represent.

Next Steps

This runbook is an example that you can use to build out your maintenance and security strategy around Azure SQL Dababase firewall rules.

The above runbook has short comings that need to be addressed. It does not support a scenarios where a database is shared by more than one Cloud Service. It also does not configure the database level firewall. As illustrated below, Azure SQL Databases has two levels of Firewalls and you should take advantage of this.

Azure SQL Server firewall rules give access to all of its databases. If you need to restrict access to a single database within an Azure SQL Server, you will need to execute SQL commands against the Azure SQL Database. Refer to this documentation about database-level firewall rules.

Azure SQL Database Resources

• How to: Configure Firewall Settings (Azure SQL Database)
• Best Practices for Securing Azure SQL Database
• Azure SQL Database Firewall
• Guidelines for Connecting to Azure SQL Database
• Managing Databases and Logins in Azure SQL Database
• Azure SQL Database Guidelines and Limitations

TSQL Resources

• Create Firewall Rule
• Delete Firewall Rule
• Get Firewall Rule
• List Firewall Rules

PowerShell Resources

• New-AzureSqlDatabaseServerFirewallRule
• Remove-AzureSqlDatabaseServerFirewallRule
• Set-AzureSqlDatabaseServerFirewallRule
• Get-AzureSqlDatabaseServerFirewallRule