Is your WA SQL Database open to the world?
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
Windows Azure SQL Database Firewall
https://msdn.microsoft.com/en-us/library/windowsazure/ee621782.aspx
How to: Configure Server Level Firewall Settings
https://msdn.microsoft.com/en-us/library/windowsazure/ee621783.aspx
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