Using Microsoft SQL Server security features in Windows Azure Virtual Machines

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 () 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 (https://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.

Using TDE with SQL Server in Windows Azure Virtual Machines

Let’s see how all this works by taking a quick walkthrough of Transparent Data Encryption (https://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:

1. Log in to the machine with the credentials of someone who can create objects in Master
2. Run the following DDL in master (“USE MASTER”):
   

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘’;

   Go

   CREATE CERTIFICATE TDEServerCert WITH SUBJECT = ‘My TDE certificate’;

   Go

3. 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 = ‘’

           WITH PRIVATE KEY (

                  FILE = ‘’,

                  ENCRYPTION BY PASSWORD = ‘’

           );

4. Switch to the database you want to encrypt
5. Run the following DDL:

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDEServerCert;

Go

ALTER DATABASE [your_database_name] SET ENCRYPTION ON;

Go

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 (https://msdn.microsoft.com/en-us/library/cc280386.aspx) exactly like how you are already using it on-premise.

Additional security considerations

A few additional things to keep in mind, please be sure to follow the security best practices (https://msdn.microsoft.com/library/windowsazure/dn133147.aspx). A few topics to consider are:

• Reduce the surface area by disabling unneeded services
• Leverage Policy-Based Management to detect security conditions such as using weak algorithms
• Use minimal permissions where possible, avoid using built-in accounts or groups such as sa or sysadmin where possible; consider using SQL Server Audit for tracking administrative actions
• If you plan on using encryption features, think about creating a key aging/rotation policy starting with the service master key
• Consider using SSL encryption especially if connecting to SQL Server over a public endpoint in Windows Azure
• Consider changing the port SQL Server uses from 1433 for the default instance to something else especially if connecting to SQL Server over a public endpoint in Windows Azure (ideally, avoid external connections to SQL Server instances from the public internet entirely) 

In closing…

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!