Welcome to my latest post, detailing encrypted backups in SQL Server 2014. In this article we'll be looking at the security employed to encrypt the backups
and how to implement them. We'll also look at how it interacts with backup compression. For the purposes of this article the following apply,

Previously the options available to secure database backups, unless you were using a 3rd party product, consisted solely of a backup media password and\or
NTFS filesystem ACLs. However, the password option is more appropriate to prevent an accidental restore of an incorrect media rather than securing the
backup from unauthorised users, as early as SQL Server 2005, Books Online has detailed the following,

For sometime now, although it really doesn't seem that long ago, we have been offered the ability to use Transparent Data Encryption (TDE) to
protect the data at rest. This provided the ability to not only protect the raw database files, but also any backups that were created using
the BACKUP DATABASE command.
The problem with TDE is that when combining this with native compression, the compression was pretty much zero, the encryption negates
the ability to compress the data.

Now there's a viable alternative, the media encryption option is a new feature in SQL Server 2014 and offers the ability to securely encrypt your backups
as well as being able to utilise native compression, something which was not previously available natively.

How Does Encryption Work?

The encryption option in the BACKUP DATABASE command, when selected, uses an encryption algorithm to encrypt the backup.
The algorithm options available are,

• AES_128

• AES_192

• AES_256

• TRIPLE_DES_3KEY

• NO_ENCRYPTION  

The algorithm in turn uses an encryptor, either in the form of a server certificate or a server asymmetric key. Both of these options
are fairly similar, the certificate uses the CREATE CERTIFICATE command whereas the asymmetrical key uses the
CREATE ASYMMETRICAL KEY command. Both utilise a public key and private key pair, the main difference is that an asymmetric
key cannot be exported, it also does not have expiry options.

The default protection option for both these encryptors is via the database master key. If this key does not exist when attempting to
create the encryptor, the command will fail and you will be asked to either supply an encryption password or create a database master key.

Since I've opted for protection by certificates, let's look at the creation process now, in order to create our certificate. On my new instance of
SQL Server I first need to create a Database Master Key, I use the CREATE MASTER KEY command as follows:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd1'

A quick check via the sys.databases catalog view shows the following, the master key is also encrypted by the Service Master Key and may
be opened\closed automatically.

Note: This behaviour can be altered by dropping encryption by the SMK but this requires the DMK to then be manually opened\closed.

Now that we have our DMK created, we may create the encryptor in the form of a certificate. This is done using the following T-SQL:

USE master
GO
CREATE CERTIFICATE [MyNewCert]  AUTHORIZATION [dbo]
--ENCRYPTION BY PASSWORD = 'P@sswordt0encryptcert'
WITH SUBJECT = 'My New Certificate'
, START_DATE = '2014-01-01 00:00:00'
, EXPIRY_DATE = '2020-01-01 00:00:00'

Depending on how much you trust your administrators, to specify encryption by a password instead of the DMK
uncomment the line above. Password protecting the certificate will restrict further who may backup and restore the certificate.

A quick check via the sys.certificates catalog view shows the following:

Note: the certificate is protected by the DMK

As soon as you have created the new certificate you must back it up immediately to avoid the possibility of data loss due to a missing certificate.
The backup is created via the following T-SQL:

USE master 

GO 

BACKUP CERTIFICATE [MyNewCert] TO 
FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.cer'
WITH PRIVATE KEY(
--DECRYPTION BY PASSWORD = 'P@sswordt0encryptcert',
FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.pky',
ENCRYPTION BY PASSWORD = 'P@sswordt0encryptcertbackup')

If in the previous section when creating the certificate, you encrypted the certificates private key with a password rather than the DMK,
you will need to uncomment the line above and provide this password to decrypt the private key.

Backup The database

The backups may now be encrypted using T-SQL or you may use the backup GUI within SSMS. To backup via the GUI, see the screenshots below

On the media options, you must specify a new media set to use backup encryption,

On the backup options page the "Encrypt backup" option is available.

Note: if you do not create a new media set, the encrypt backup option will be greyed out!

Alternatively, to backup via T-SQL use the following command

BACKUP DATABASE [AdventureWorks2014_ENCBACKUP] TO
DISK = N'E:\Bak\MSSQL12.INST1\MSSQL\Backup\AdventureWorks2014_ENCBACKUP.bak'
WITH INIT, MEDIANAME = N'Encrypted backup'
NAME = N'AdventureWorks2014_ENCBACKUP-Full Database Backup',
COMPRESSION, ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = [MyNewCert] )
GO

Restore The Database

What happens when we want to restore our backup to another server, let's imagine for a moment we are going to create an
AlwaysOn group and we are using these backups to initialise the secondary database(s) on our new instance(s). If you attempt
to restore the backup immediately via the GUI you will see the following screenshot. There's an error but it's not helpful.

An attempt to read the backup header also produces an error, more meaningful this time.

We need to create the certificate on each instance where we intend to restore the database. This is done by copying the public key
and private key certificate backups that were taken previously, to a location accessible by the new instance and issuing a
CREATE CERTIFICATE command from a file. You must also create a Database Master Key on the new instance if it doesn't exist already.

The DMK does not have to match the source instance, they have no dependency on each other.

The following T-SQL shows how to create the certificate from a file on the new instance

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Someseccurepassword'
GO CREATE CERTIFICATE [MyNewCert] AUTHORIZATION [dbo]
FROM FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.cer'
WITH PRIVATE KEY (
DECRYPTION BY PASSWORD = 'P@sswordt0encryptbackup'
, FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.pky'
--, ENCRYPTION BY PASSWORD = 'P@sswordt0encryptcert'
) 

When restoring the certificate backup, if you wish to encrypt the private key by password instead of the DMK, uncomment the line above.

A fresh attempt at reading the backup header shows us the certificate has been correctly installed.

The restore via the GUI is now possible.

Effects of Encryption on Compressed Backups

To test the effects I took three backups. Backup A used no compression or encryption. Backup B used compression only. Backup C
used compression and AES256 encryption. The results were as follows;

Finally, SQL Server now offers the ability to encrypt your data alongside using efficient compression. Whether or not they will rival popular
3rd party backup products remains to be seen, but at least in native mode you can be sure your data is both stored efficiently to conserve
space and is safe from prying eyes. Just ensure you manage the certificates correctly and any passwords used to secure them.
Have fun and as always, if you have any questions, send me an email and I'll help all I can.

Hello and welcome to my latest post detailing the Always Encrypted feature within SQL Server. Much has happened within SQL Server these last few years and Always Encrypted is another big feature in the SQL Server encryption arena.

But what is it, how can it help my organisation?

What is "Always Encrypted"?

For a full and detailed explanation, check the Microsoft documentation at the following link;

Always Encrypted (Database Engine)

Always Encrypted was initially made available in Enterprise Edition of SQL Server 2016 RTM, it was later introduced into Standard, Web and Express Editions during Service Pack 1 for SQL Server 2016.
Always encrypted uses a series of steps utilising symmetric and asymmetric keys, the steps are dependent upon who will create and manage the keys and the tools used to create them. Different organisations have different levels of support and role separation. We'll look at these later but for now let's look at exactly what it does and why?

What does Always Encrypted Do and Why?

Traditionally, if you used Transparent Data Encryption, you would have a Certificate and Database Master Key (DMK) in the master database and then a Database Encryption Key (DEK) in the user database. As the Certificate and DMK are SQL Server instance level objects, it's pretty much available to anyone who has the key. Obviously, this cannot be used to truly encrypt and prevent SQL Server users (including DBAs) from reading the encrypted data.

This is where the Column Master Key (CMK) and Column Encryption Key (CEK) come into play. Always Encrypted uses 2 different types of key and an external encryptor to achieve the secure data encryption. The external key provisioning options are; 

• Hardware Security Module using CNG/KSP (Cryptographic Next Generation using Key Storage Provider)
• Hardware Security Module using CAPI/CSP (Cryptographic API using Cryptography Service Provider)
• Azure Key Vault
• Windows Certificate Store (Computer)
• Windows Certificate Store (User)

The first 2 options generate a column master key from an external secure source, the 3rd option uses a certificate in an Azure Key Vault.
The last 2 options use the Windows Certificate key store. There are vast differences between the above options and it all depends on your organisations preferred method of key storage.
Of the first 2 options above, CNG\KSP should be preferred over the legacy CAPI/CSP. Both options require the installation of a client side driver to provide interaction between the key store and the Always Encrypted system.
A typical example of a Windows Certificate key store cert would look similar to the screenshot below;

Column Master Key (CMK)

The CMK is an asymmetric key which is stored externally to the SQL Server instance, this is usually in the form of a certificate. If required, DBAs have no access to the CMK at all.
A reference to the CMK is held inside the user database, but it only holds the path (including the thumbprint) to the external key not the key itself, it's created and managed outside of the SQL server system. 

Column Encryption Key (CEK)

The CEK resides inside the user database and is protected and encrypted by the CMK. The CEK key holds only the encrypted CEK value, no plaintext details are stored.

With the Certificate residing outside of the SQL Server system, The CMK certificate is distributed to only those users who are required to decrypt the data, given security and data best practices this list should be limited.

There are a set of DMVs we can use to track the keys and an example is shown below from a test system;

As we can see from the output above, the CMK metadata holds very limited detail, the key path includes the thumbprint which is required to create the CEK. This value is encrypted and then stored in the "encrypted_value" column in the CEK key values DMV. 

Table Schemas

The base column definitions stay the same in that their data types do not change, there's no creating the columns as varbinary as was necessary with traditional column level encryption. However, once encryption has been applied, the table definition does change slightly as shown below;

When scripting out the schema we can see that the CardNumber, ExpMonth and ExpYear columns now have an extra clause applied to their definition. The type of encryption selected for each chosen column during configuration, now directly applies on a per column basis. The 2 types of encryption which may be applied are defined as one of the following; 

• Randomised
• Deterministic 

Randomised

The Randomised encryption type produces a different value each time even when the data encrypted is the same. This is more secure but also prevents the usage of these columns in indexes, grouping and join criteria.

Deterministic

The Deterministic encryption type produces common encryption values. Whilst this aids the use of the columns in join criteria and indexing, etc it could be possible when analysing the encrypted strings to work out patterns in the encrypted values, so exposing the protected data.

Always Encrypted With Secure Enclaves

New in SQL Server 2019 is the feature Always Encrypted with Secure Enclaves. This feature was added to overcome some of the limitations with the way encrypted data is used. Traditionally, the data encryption operations occur on the client side via the client driver. The client modifies the data unencrypted and then sends the encrypted value(s) back to the server. The data and the encryption keys are never exposed over the link. This affects the way the data is used though and one reason why the choice for Randomised or Deterministic encryption should be carefully made, as in the case of column joins and grouping, etc.

With the feature enhancement, some computations of the unencrypted clear text data are available on the server within secure enclaves. They are protected regions of memory assigned to the SQL Server process so that further computation operations are possible over the standard Always Encrypted functionality. Of course, this would involve extra design and configuration tasks and also development routes would need to be changed too, but the feature is there for those who wish to consume it. This link details further;

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves?view=sql-server-ver16

Always Encrypted Deployment Routes

Whilst it is possible for a DBA to manage the whole process, this may not fit all organisations, especially where role separation is enforced. So the way in which the encryption is applied depends on whether role separation is employed and whether you fully trust the DBA team or are they supplied by a 3rd party and not authorised to view the sensitive data.

Of course you may have Core employee DBAs who are trusted but the Security team handle all encryption. The answer to these questions would straight away drive a particular route, let's look at some scenarios;

Scenario A

The DBA is a direct employee of the organisation and trusted fully with the database systems. They are either authorised to view the data or do not exercise any elevated privilege to view data they are not required to have access to. They would have access to an Azure Key Vault or Windows Certificate Stores or HSM modules.

Scenario B

The DBA is a resource supplied by a 3rd party support vendor to the organisation. The organisation also has a separate security admin team that manage security keys. Neither team are authorised to have access to the encrypted data but are required to implement encryption and provide BAU management of the database system.

The scenarios above map to the following;

Scenario A (Singularly Managed)

The management via SQL Server Management Studio will be suitable for those who entrust their DBAs with the sensitive data held in the database or where the DBA has a wide range of permissions for their role outside of SQL server. For instance, if selecting a Computer Certificate Store the DBA would require local admin permissions on their machine, otherwise when attempting to create a column master key and selecting the local computer certificate store, an error will be raised.

Allowing the DBAs to create and manage the keys and view the data is fairly straight forward and can be achieved via SQL Server Management Studio, using the [Sales].[CreditCard].[CardNumber] column in the AdventureWorks database as an example, a typical deployment would be as follows;

First, right click the table in SSMS and select "Encrypt Columns" as shown below;

Note: The CMKs and CEKs can be created and managed from SQL Server Management Studio and requires v17.0 and above.

Click "Next" through the Introduction screen and you will see the "Column Selection" dialog, select the columns and the encryption type. For this I have selected the CardNumber column and Randomised encryption. A new CEK has been automatically selected since i do not have any existing keys in the database, I also do not have the option of changing the CEK name. Select your options and click "Next";

 Landing at the CMK configuration dialog we see the following, since no keys exist the system will auto generate with default names. Click "Next" to continue;

 At this point you may script the configuration, leave as "Proceed to Finish Now" and click "Next";

Clicking "Finish" will apply the Always Encrypted configuration.

At this point you are left with a configuration that may not meet naming standards, this is one of the downsides of using the Always Encrypted wizard to deploy your configuration.
Ideally, you would deploy your CMK certificate and create your CEK before you invoke the "Encrypt Columns" wizard. The CMK would be created on a secure asset within the organisation. In this scenario I have used a cert store on the same computer as the SQL server instance, this is for demonstration purposes only and would not meet best practice, this would likely breach any security restrictions imposed by your organisation too.

For the purposes of this demonstration, you should have a basic idea of what is required for a single role deployment.

Scenario B (Role Separated)

For scenarios where role separation is employed, the process will need to be broken down and actioned by the appropriate agent. The process employs PowerShell cmdlets in the first steps, which are used by the Security or Infrastructure admins. The DBAs are then passed the relevant thumbprint values and may complete the process via PowerShell or T-SQL.

To use a scenario, this could be where an organisation has a separate security administration team and a team of Database Administrators provided by a 3rd party support vendor.

Neither party are permitted to view encrypted data in the salary column of the employee table. The security operative is to manage the key but no permission to select the column, the DBA is to manage the SQL Server configuration but have no ability to read the table. Without access to the security key itself, selecting the column returns the encrypted data.

The configuration steps for "role separated" organisations are different and are broken down into subsets for each role to perform. The key values provided by the security team to the DBA are the CMK type and path\location and the CEK encrypted value. In this example we will use a user based certificate in the local Windows Certificate store. The Always Encrypted deployment via a role separated route is as follows;

Security Admin

• Generate a user based certificate on a secure asset within the organisation. Typically the PowerShell cmdlet "New-SelfSignedCertificate" could be used.
• Create a CMK setting using the PowerShell cmdlet "New-SqlCertificateStoreColumnMasterKeySettings". This cmdlet connects to the SQL PowerShell provider but performs no actual database connectivity.
• Create a CEK setting using the PowerShell cmdlet "New-SqlColumnEncryptionKeyEncryptedValue". Again, this cmdlet connects to the SQL PowerShell provider but performs no actual database connectivity.
• Provide the DBA with the CMK path\location and the CEK encrypted value, this can be done via secure sharing of a file generated by the previous steps.

 DBA

• Connect to the SQL Server instance and database via PowerShell.
• Create a column master key setting from the settings previously provided by the Security Admin. This uses the PowerShell cmdlet "New-SqlColumnMasterKeySettings". No actual database connectivity is performed at this point.
• Create a CMK in the user database by using the PowerShell cmdlet "New-SqlColumnMasterKey". This performs database connectivity and creates the new CMK. This key would be available during the column encryption wizard steps as previously seen in the single role deployment.
• Create a CEK in the user database by using the PowerShell cmdlet "New-SqlColumnEncryptionKey". This also performs database connectivity and creates the new CEK based on the CMK details. Again, this key would be available during the column encryption wizard steps as previously seen in the single role deployment.
• Complete the column encryption using the newly created keys

After completing the steps above, when first querying the data the encrypted version is returned.

After ensuring access to the certificate in my current user Windows Certificate store and reconfiguring the SSMS connection to allow Always Encrypted as shown below;

Re running the query now shows the unencrypted data

To remove the encryption simply re run the "Encrypt Columns" wizard and set the columns to "PlainText", then complete and Finish the wizard.

It should be understood from the outset that the configuration and implementation of Always Encrypted requires the skills of an experienced Security Admin and an experienced DBA. Where necessary, Proof Of Concept environments can be used to drive the High Level Designs and Low Level Designs. Configurations outside the basics of the Windows Certificate Store will require deep knowledge of all associated subsystems and PowerShell. It's important that the data security be paramount at all times and that all options are fully evaluated to ensure the correct configurations are applied, observing any rules the organisation follows or are regulated to follow.