Securing your SQL Servers has never been more important when looking at hosting your databases in the cloud. No longer is your data sitting safely on a server in the company’s data centre – now it is in the cloud, on the internet and more open and vulnerable than ever before.
Of course, we can and should implement constructs like firewalls, network security groups, access control lists, multi-factor authentication, Active Directory integration and good old password security. But what about protecting the actual SQL Server database?
Transparent Data Encryption in Azure
Encryption options have been available since SQL Server 2005. The most common form of encryption used is Transparent Data Encryption (TDE). TDE encrypts the at rest data in a SQL Server database. In other words, your database data and log files.
Encryption is performed using either a certificate or an asymmetric key following the encryption hierarchy (see image below) – i.e. the certificate is protected by the Database Master Key (DMK) and the certificate protects the DEK (symmetric key) which is then used to encrypt the data.
Extensible Key Management (EKM) is available from SQL Server 2008 onwards whereby the encryption keys can be created by using an interface from a provider. Azure Key Vault is available as a provider from SQL 2012 onwards. The EKM provider may be Microsoft or a third-party company. EKM can allow for even more advanced encryption features to be utilised for SQL Server e.g. bulk encryption and key aging. More info can be found here: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/extensible-key-management-ekm?view=sql-server-2017
Advantages of enabling TDE:
- Simple to implement
- No changes need to be made to the application tier
- Is invisible to users
- Works with high availability features e.g. Availability Groups
- Database backups are automatically encrypted as well
The last point is worth highlighting is that often with Azure (especially with IAAS VMs) backups will be performed to BLOB storage. If you think of it, BLOB storage is essentially just a bucket of disks that can be accessed anywhere across the internet via a URL. The BLOB storage is secured by access keys however there have been recent instances of black hats hacking BLOB storage (https://www.itprotoday.com/cloud-storage/storage-isn-t-safe-cyber-criminals) – by obtaining the access keys to the BLOB storage. In this case, once they have access they can pretty much just copy the unencrypted database backups. With TDE encryption there is an extra layer of security.
It’s worth noting as well is that for Azure SQL Database and Azure Managed Instances; the databases have TDE enabled providing this level of encryption – so Microsoft automatically provides this for you if you are using these platforms.
Other Database Encryption Solutions
Of course, TDE is not the only encryption solution available. Some of the other solutions that can be enabled and utilised are:
Column/Cell-Level Encryption: Allows encryption of individual database columns in tables. Useful for encrypting highly sensitive column data such as credit card numbers for example. Since it utilises a function to encrypt/decrypt data, application changes may be required to work successfully with it.
Always Encrypted: Available in versions from SQL Server 2016 onwards. Always Encrypted is the evolution of column encryption. Rather than storing keys with SQL Server, the keys are abstracted from the database layer and retrieved by the client application directly. This allows Always Encrypted columns to be protected at rest, in flight and in memory. This is an ideal solution for sensitive data such as credit card numbers. It is still implemented at the column of a table, so some application changes may be required to accommodate its use.
Dynamic Data Masking: Does not actually encrypt the data but masks it from certain users so as they cannot see sensitive data. For example, a value of 123456789 may appear to the user as XXXXXX789.
SSL Transport Encryption: Secure Socket Layer (SSL) encrypts the data between network endpoints. Essentially the communication to the SQL Server is encrypted.
Backup Encryption: Similar to TDE but allows just the database backups to be encrypted. The actual database files are not encrypted.
Security of data should always be considered when locating databases to a cloud environment. So, is your data secure? For more information and advice on encryption in the cloud reach out to us here at WARDY IT Solutions. We’ll be happy to help.
