The different SQL Server encryption modes

Net4All > Publications > TECHNICAL > The different SQL Server encryption modes
valuation-accompaniement-ot

TECHNICAL

Part 1: Introduction and encrypted backup

We are very pleased to offer you the chance to discover a three-part summary of an internal study we have undertaken on SQL Server encryption. Please read on to enjoy the first part of the summary of this study.

Why did we undertake this study ?

This study formed part of a project aimed at deriving benefits from SQL Server encryption with certain applications. The objectives were to :

  • Encrypt data
  • Study the impact on performance
  • Study the impact on the application part

It was also important to look at some of the crucial elements for setting up SQL Server 2019, namely database sizing and data backup and recovery.

To best meet these requirements – while also taking note of the fact that SQL Server offers several encryption methods in respect of its databases – we have detailed and summarised the procedures for each method, as well as any potential impact they may have on server resources or the source application.

Different methods of encryption

For SQL Server 2019 (version 15.x), there are 5 encryption methods:

  • Backup encryption
  • SSL encryption at the SQL Server connection
  • TDE: Transparent Data Encryption
  • Column level encryption
  • Global Encryption (Always Encrypted)

Another method is also available which, strictly speaking, is not encryption but masking :

  • Dynamic Data Masking

In order to make it easier to understand the accounting for these different methods as based on SQL Server 2019 editions, here is a short table summarising them. With the exception of encrypted backup, you can see that each method is available in SQL Server Wed Edition and higher :

tableau

Encrypted backup

There are several advantages of encrypted backup. Firstly, the backed up data can be secured, but more importantly, it means that the encrypted backups can only be restored on a server or an instance that has the certificate or the asymmetric encryption key being used. These elements help to ensure that the information in the computer system cannot be stolen and read by someone who intends to make use of it for malicious purposes.

Looking at the practical steps involved, here are the different stages required to set up the backup and restore the data, as well as some T-SQL tips :

Setting up the backup :

  • Creation of a Master Key in « master ».
  • Creation of a certificate protected by the Master Key.
  • Backup of the certificate and private key (off-site).
  • Verification that the SQL account that will perform the “db_backupoperator” backup
  • Implementation of backup maintenance and/or Veeam plans.

Restoration procedure:

  • If using a different server: Create a new Master Key in « master ».
  • If using a different server: Restore certificate and private key (off-site files).
  • Verification that the SQL account that is going to perform the restore procedure is authorised to see the certificate that is in « master ».

T-SQL tips:

  • Master Key creation: CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MasterKeyPassword’; GO
  • Restoring a certificate and private key: CREATE CERTIFICATE [Backup_Certificate] AUTHORIZATION [dbo] FROM FILE = ‘D:\Backup-Keys\Backup_Certificate.cer’ WITH PRIVATE KEY (DECRYPTION BY PASSWORD = ‘myDecryptPassword’, FILE = ‘D:\Backup-Keys\Backup_Certificate.key’); GO
  • Creating a certificate and private key and backup: CREATE CERTIFICATE Backup_Certificate WITH Subject = ‘mySSLSubject’; GO BACKUP CERTIFICATE Backup_Certificate TO FILE = ‘D:\Backup-Keys\Backup_Certificate.cer’ WITH PRIVATE KEY ( FILE = ‘D:\Backup-Keys\Backup_Certificate.key’, ENCRYPTION BY PASSWORD = ‘myBackupPasword’); GO
  • Launch an Encrypted Backup: BACKUP DATABASE myDB TO DISK = ‘D:\Backup-Keys\myDB_BackupEncrypted.bak’ WITH COMPRESSION, STATS = 10, ENCRYPTION (ALGORITHM = AES_256,SERVER CERTIFICATE = ‘mySSLSubject’);

Continue for the next part

This is the end of the first part of this summary of the SQL Server encryption study. In the following part, we will provide more details on the different encryption methods listed above. Meanwhile, please feel free to check out the rest of our blog content, or contact us to learn more about this topic.

Contact us