Windows Azure SQL Database Marketplace
Editor's Note: This post comes from Sung Hsueh, Senior Program Manager in SQL Server Team.
One of the newest ways of using SQL Server is by leveraging Microsoft’s Windows Azure Infrastructure Services and creating Windows Azure Virtual Machines to host SQL Server. You may have already seen Il-Sung’s earlier blog post (http://blogs.msdn.com/b/windowsazure/archive/2012/12/12/regulatory-compliance-considerations-for-sql-server-running-in-windows-azure-virtual-machine.aspx) about the possibilities of leveraging this environment even for applications with compliance requirements. With the availability of SQL Server 2008 R2 enterprise edition and SQL Server 2012 enterprise edition in Windows Azure Virtual Machines, you now have the option to take advantage of our Enterprise level features such as SQL Server Audit and Transparent Data Encryption in pre-configured, ready-to-deploy, per-minute-billed Windows Azure Virtual Machines! You, of course, still have the option to take advantage of these features by using License Mobility (http://www.microsoft.com/licensing/software-assurance/license-mobility.aspx) to transfer your existing Software Assurance or Enterprise Agreement licenses as well if you prefer this over the per minute billing.
Let’s see how all this works by taking a quick walkthrough of Transparent Data Encryption (http://msdn.microsoft.com/en-us/library/bb934049.aspx). If you haven’t already, create a Windows Azure Virtual Machine that has SQL Server already installed on it through the Windows Azure management portal:
And start creating some databases!
Once you have a database you want to add encryption to, the next few steps are the same as if you are running SQL Server locally:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<your password here>’;
CREATE CERTIFICATE TDEServerCert WITH SUBJECT = ‘My TDE certificate’;
Backup your certificate (you need to keep this safe as you will need this to access the database including for any backups/snapshots/etc you create for your encrypted database); note that this will create two files one containing the certificate’s public key and another with the private key, you will need both. For more info on certificate backups, please refer here.
BACKUP CERTIFICATE TDEServerCert TO FILE = ‘<your backup file path for public key>’
WITH PRIVATE KEY (
FILE = ‘<your backup file path for private key>’,
ENCRYPTION BY PASSWORD = ‘<new password for encrypting private key file>’
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDEServerCert;
ALTER DATABASE [your_database_name] SET ENCRYPTION ON;
And that’s it! The encryption will run in the background (you can check this by querying sys.dm_database_encryption_keys). No differences, exactly the same as your on-premise SQL Server instances. Similarly, you can also use SQL Server Audit (http://msdn.microsoft.com/en-us/library/cc280386.aspx) exactly like how you are already using it on-premise.
A few additional things to keep in mind, please be sure to follow the security best practices (http://msdn.microsoft.com/library/windowsazure/dn133147.aspx). A few topics to consider are:
Running SQL Server enterprise edition in Windows Azure Virtual Machines allows you to carry over the security best practices and expertise from your existing applications and leverage Microsoft’s Windows Azure to run your applications in the cloud and pay for only what you use (including Enterprise!) through the per minute billing option. Try it out and let us know your experience!