The only secure computer is one that’s unplugged, locked in a safe, and buried 20 feet under the ground in a secret location… and I’m not event too sure about that one. – Dennis Huges, FBI
The only truly secure system is one that is powered off, cast in a block of concrete and sealed in a lead-lined room with armed guards – and even then I have my doubts. – Eugene Spafford, Purdue University Professor of Computer Science and Executive Director of CERIAS
As the above quotes indicate, and as you’re hopefully aware, simply by turning a computer on you open it up to risks. Connecting it to a network opens it up to even more risks. Connecting it to the internet opens it up to even greater risks still. Because of these risks it’s more important for you to consider the deployment topology of your applications and where the vulnerabilities lie. In this post I’ll talk about an enhancement made to Windows Azure SQL Database to give you finer grain control over who gain access your data.
When it comes to securing your Windows Azure SQL Databases, Microsoft has done some of the heavy lifting for you. Prior to the June update to the Windows Azure platform, you were able to secure your databases at the server level by specifying firewall rules. These rules filtered granted/denied connections based on the client’s IP address. The result was something like this:
You can configure these server levels rules in the portal. Alternatively, and much more to a DBA’s liking, you can also use some system views and stored procedures to view, create, update, and delete server firewall rules. These objects are available in your server’s master database. Here’s a rundown of what you can use:
- System view sys.firewall_rules will display the server’s current firewall rules.
- System extended stored procedure sys.sp_set_firewall_rule creates or updates a server-level firewall rule.
- System extended stored procedure sys.sp_delete_firewall_rule deletes the specified server-level firewall rule.
While Windows Azure SQL Database server-level firewall rules were a good first step, they weren’t quite good enough. The problem is that server-level rules are an all-or-none proposition. If a client IP address has access to the server, then the client has access to any database on the server. Of course you can, and should, use SQL authentication to harden your databases, but that won’t prevent a decent hacker from trying to brute force his or her way into your database once server access is gained.
In the June update to the Windows Azure platform, Microsoft introduced another layer of security to SQL databases. This layer is the database firewall rule. With this level of security you can now filter traffic to specific databases on a given server based on client IP address. The result looks something like this:
In this scenario I’ve created a database-level firewall rule for database three. As a result, even if a client is able to access the server where database three resides, unless the client’s IP address is accounted for in the database-level firewall rules, the client will not be able to access the database.
Currently you cannot configure database-level firewall rules in the portal. However, just like server-level rules, you can take advantage of system views and stored procedures to view, create, update, and delete server firewall rules. These objects are available in your server’s master database. Here’s what you can use:
- System view sys.database_firewall_rules will display the current firewall rules for databases on the server.
- System extended stored procedure sys.sp_set_database_firewall_rule creates or updates a database-level firewall rule.
- System extended stored procedure sys.sp_delete_database_firewall_rule deletes the specified database-level firewall rule.
%7Cutmccn%3D(direct)%7Cutmcmd%3D(none)%3B&utmu=qB~){kind=small}
Here’s a recap.
Hey there! My name is Adam, and I'm a Technical Evangelist at Microsoft where I spend time focusing on Windows, Windows Phone, and Windows Azure.