Microsoft Azure SQL Database Security – Firewall Configuration

Deployment of cloud-based technologies introduces a wide range of challenges; however few of them are subjected to the same degree of scrutiny, concern, and public debate as security. In order to properly analyze security related challenges, it is important to note that they encompass several distinct but interrelated concepts, such as data integrity and confidentiality, access control, authentication, and authorization. In this article, we will start reviewing them in the context of Microsoft Azure Software as a Service-based SQL Database, focusing in particular on the SQL Server and database-level firewall access control functionality and methods that can be employed to implement it.

The first line of defense when controlling access to Microsoft Azure SQL Databases involves IP address and port filtering on the Azure SQL Server instance level, implemented as firewall rules defined in its master database. The firewall restricts incoming traffic to TCP port 1433 only. In addition, by default, all external (from the Azure point of view) connections are blocked, so you need to explicitly enable them by specifying the public IP address (or IP address range) assigned to your Internet entry point. This can be done by employing one of the following mechanisms:

• Azure Platform Management Portal – offers a convenient approach (as long as IP address changes are infrequent or that you consistently use the portal to manage SQL Databases) since it automatically detects the public IP address of the remote endpoint (assuming that you are connecting from the same system that must be able to communicate with a target database). In such cases, you will be prompted whether you want to add the current IP address to the allowed list. Your approval triggers the generation of a new SQL Server-level (i.e. defined within the master database) firewall rule, whose name consists of the ClientIPAddress_ prefix, followed by the creation timestamp.

• Windows PowerShell – comes in handy in roaming scenarios (with a direct Internet connection) where your public IP address is assigned dynamically, forcing you to modify your firewall rules on a regular basis. To implement this solution, install and import Windows Azure Module for Windows PowerShell(its setup program is available from Azure Downloads, but note that its execution triggers Web Platform Installer that automatically downloads and installs all necessary prerequisites, including Windows Azure SDK and .NET Framework 4.5); connect to your subscription, and run the New-AzureSqlDatabaseServerFirewallRule (or Set-AzureSqlDatabaseServerFirewallRuleif modifying an existing firewall rule) cmdlet. The first of these steps is quite straightforward, so let’s focus on the remaining two.

  In general, there are two ways of connecting to your subscription. The first one relies on the account-based authentication and involves logging on to Windows Azure by explicitly specifying your credentials (in response to the Add-AzureAccount cmdlet that you launch). You can use either a Microsoft Account or an Organizational ID for this purpose, as long as the one you choose is designated as either the Azure Service Administrator or a Co-administrators for the target subscription. (You can manage these assignments via the Administrators tab of the Settings page within the management portal). An alternative approach relies on certificate-based authentication and involves generating and importing a management certificate that represents your subscription with corresponding credentials. This is accomplished by invoking the Get-AzurePublishSettingsFile cmdlet, which allows you to download from Azure a .publishsettings file and, subsequently, placing its content in your Windows user profile (specifically, in the C:UsersUser_NameAppDataRoamingWindows Azure Powershell folder and your personal certificate store) by running the Import-AzurePublishSettingsFilecmdlet.

  With the first of these two methods, credentials are cached for 12 hours, which makes it ill suited for scenarios that involve execution of repetitive, scheduled tasks. While the second approach eliminates this shortcoming (providing continuous access as long as the subscription and management certificate remain valid), it introduces security and manageability challenges (especially when providing shared access to a subscription or when dealing with multiple subscriptions by using the same account).

  These two procedures translate into the following PowerShell code sequence:

  1. Connect to Windows Azure.

  1a. Either use explicit credentials (this will launch a separate browser window prompting for your username and password),

  Add-AzureAccount

  1b. or generate management certificate, download the .publishsettings file, and import it into your profile.

  Get-AzurePublishSettingsFile

  Import-AzurePublishSettingsFile

  2. List your Azure subscriptions (optional) in order to identify the SubscriptionName that hosts the target SQL Server instance.

  Get-AzureSubscription

  3. Connect to the target subscription (note that the credentials you specified must be associated with this subscription).

  Select-AzureSubscription "SubscriptionName"

  4. List your SQL Server instances in order to identify the ServerName that hosts the target SQL Database (optional).

  Get-AzureSqlDatabaseServer

  5. List existing firewall rules on the target SQL Server-level (optional).

  Get-AzureSqlDatabaseServerFirewallRule -ServerName ServerName | ft -auto

  6. Identify your current IP address based on the method published in the TechNet Script Center (this assumes that you have a direct Internet connection).

  $currentIP = (New-Object net.webclient).downloadstring("http://checkip.dyndns.com") -replace "[^d.]"

  7. Check if there is an existing firewall rule with the same name.

  7a. If such rule does not exist, create a new one.

  If ((Get-AzureSqlDatabaseServerFirewallRule -ServerName hq4g4hz60d -RuleName "ClientIPAddress_Dynamic") -eq $null) {
  	New-AzureSqlDatabaseServerFirewallRule -ServerName hq4g4hz60d 
  		-RuleName "ClientIPAddress_Dynamic" -StartIpAddress $currentIP -EndIpAddress $currentIP
  	}
    

  7b. If such rule does exist, update it to allow database connections originating from $currentIP.

  else {
  	Set-AzureSqlDatabaseServerFirewallRule -ServerName hq4g4hz60d 
  		-RuleName "ClientIPAddress_Dynamic" -StartIpAddress $currentIP -EndIpAddress $currentIP
  	}
    

• T-SQL – provides the ability to manage Azure SQL Server-level firewall rules by leveraging stored procedures defined in the master database hosted by the target Azure-based SQL Server. Such rules are easily enumerated by running the SELECT statement against the sys.firewall_rules view. In order to create a new rule or update an existing one, you need to execute the sp_set_firewall_rule stored procedure (available only in the master database), which takes three VARCHAR parameters – name, start_ip_address, and end_ip_address. Similarly, removing any previously defined rules can be done by running sp_delete_firewall_rule with a single parameter (name) identifying the target. T-SQL-based access to Azure SQL Server is available through a variety of methods, including SQL Server Management Studio, Azure Management Portal (via Manage link on the Servers tab of the SQL Databases section), and sqlcmd utility.

• Azure SQL Database Management API (REST) – we will discuss this approach in more detail in upcoming articles.

  In addition to restricting access to Azure SaaS-based SQL Server from the Internet, you also have the ability to control its inbound connectivity from Azure-resident services (such as, Azure Web Sites, Virtual Machines, or Cloud Services). Note, however, that enabling or disabling this setting applies to the entire Azure-hosted estate (without the same level of granular control you have in regard to traffic originating from the external IP address space). This functionality is represented in the Management Portal by the Yes/No switch in the allowed services section of the Configure page for each Azure SQL Server instance. It is also possible to control it via the other methods listed above. In the case of PowerShell, this involves invoking the already familiar Set-AzureSqlDatabaseServerFirewallRule cmdlet applied to the AllowAllWindowsAzureIps firewall rule with the StartIpAddress and EndIpAddress parameters set to 0.0.0.0. Similarly, a T-SQL-based approach leverages the sp_set_firewall_rulestored procedure with start_ip_address and end_ip_address parameters set to 0.0.0.0.

  There is also an alternative (and complementary to SQL Server-level firewall rules) method of protecting access to your SQL Databases, which relies on equivalent firewall rules applied to individual databases. It is important to note that they are considered only if incoming traffic is not permitted based on the server-level rules (in other words, they allow you to provide additional exclusions, beyond those already stored in the master database). In this case, however, your management options are more limited, since this functionality is configurable via T-SQL and Azure SQL Database Management API (REST) only. The first of them provides the ability to examine existing rules by enumerating the content of the sys.database_firewall_rules view, to create and modify rules by running sp_set_database_firewall_rule stored procedure (with the same three parameters as its master counterpart, i.e. name, start_ip_address, and end_ip_address), as well as to delete them with the sp_delete_database_firewall_rulestored procedure (all of these actions must be carried out within the target SQL Database). As far as the second one goes, as mentioned earlier, we will cover the Azure SQL Database Management API (REST)-based solutions in upcoming articles. We will also explore other means of securing your cloud-resident resources.

  See all articles by Marcin Policht