The different SQL Server encryption modes

Net4All > Publications > TECHNICAL > The different SQL Server encryption modes
visuel

TECHNICAL

Part 2: The different encryptions

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 discover the second part of the summary of this study.

TLS encryption of the connection

In order to increase the security of the applications, it is possible to communicate with the SQL server by encrypting communications using TLS. It is therefore necessary to install an SSL certificate, attributed from a public certification authority on the server (using MMC for example).

Before configuring and using TLS 1.2 on the SQL Server, it is important to check whether an update is needed.

SQL Server configuration:

Using the « SQL Server Configuration Manager”

article

On the client:

  • Exporter le certificat (sans la clé privée) du serveur et l’importer sur le client.
  • Install the « SQL Server Native Client ».
  • Right-click on « SQL Server Native Client Configuration » and then click on « Properties ».
  • Click on « Yes » in the « Indicators » page, in the « Force protocol encryption » area

TDE – Transparent Data Encryption

TDE encryption was introduced with SQL Server 2008 Enterprise. It is now available for the first time with SQL 2019 in the Standard version and the main purpose is to protect data by encrypting physical files. MDF data files and the LDF log can also be encrypted in this way.

The technology was created so that the encryption process is completely transparent to those applications that access the database. The aim of the process is to encrypt the pages before they are written to the files; upon reading the pages, they are therefore decrypted and then placed in the RAM. The data is then encrypted as “data at rest ». During (SELECT) operations using decryption, however, the data in the RAM is not encrypted.

If a database uses TDE, then the TEMPDB database will also be encrypted. It is equally important to know that the encryption/decryption is carried out by the SQL Server CPU, to ensure that data that travels over the network is not encrypted. It is then possible to use this solution in conjunction with the TLS connection solution.

In order to implement TDE encryption, the following needs to be done :

  • Create the Master Key in « master”
  • Create certificate protected by the Master Key.
  • Create a Database Encryption Key, or DEK
  • Enable encryption in the database options
  • Backup the Certificate and Master Key (off-site)
  • Ensure that the SQL account will perform the “db_backupoperator” backup

SQL tips :

  • Master Key creation: USE MASTER; CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MasterKeyPassword’; GO
  • Creation of the certificate: USE MASTER; CREATE CERTIFICATE myCertificate WITH SUBJECT = ‘My Certificate’, START_DATE = ’01/01/2021′, EXPIRY_DATE = ’31/12/2029′; GO
  • Creation of the encryption key in the client database: USE MADB; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE myCertificate; GO
  • Enable TDE encryption on the client database: ALTER DATABASE MADB SET ENCRYPTION ON; GO

When activating or deactivating TDE on the database, the action is direct. In other words, a non-truncated log will contain both unencrypted records and encrypted records. When deactivating TDE and deleting the encryption key and certificate, you must also remember to truncate the transaction log. If you do not do so, the database will refuse to mount after the server has rebooted.

Regarding the backup, the server that will be used for restoring the database must have the encryption certificate that is present in the database. As long as this is the case, there will be no problem with Veeam.

Column level encryption

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.

In order to protect data, a database does not necessarily need to be fully encrypted. It is fully possible to only encrypt specific data, such as an email address, an account number, a credit card number, etc. In this instance, SQL Server provides a column level encryption feature. With this process, it is also possible to encrypt several columns using different keys. In this way, for example, a « Confidential Documents » view and another « Top Secret Documents » view can be proposed.

In that instance, it is possible to use SQL users that have access to one or both of the encryption keys. Depending on the user, access to data can be different. The data is encrypted « at rest » and during SELECT operations using decryption, the data in the RAM is not encrypted.

Implementation:

  • Create a Master Key encryption key in the client base
  • Create a self-signed certificate for the SQL Server
  • Setting up symmetric key encryption
  • Encrypt the column(s)
  • Test the solution

T-SQL tips:

  • Master Key creation: USE MADB; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MasterKeyPassword’; GO
  • Creation of the certificate: USE MADB CREATE CERTIFICATE myCertificate WITH SUBJECT = ‘My Certificate’, START_DATE = ’01/01/2021′, EXPIRY_DATE = ’31/12/2029′; GO
  • Create a symmetric encryption key: USE MADB; GO CREATE SYMMETRIC KEY mySymKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE myCertificate; GO
  • Add an encrypted column: USE MADB; GO ALTER TABLE maTable ADD myCol_Encrypted varbinary(MAX); GO
  • Updating a table: USE MADB; GO OPEN SYMMETRIC KEY mySymKey DECRYPTION BY CERTIFICATE myCertificate; UPDATE maTable SET myCol_Encrypted = EncryptByKey (Key_GUID(‘mySymKey’), myCol) FROM maTable; GO CLOSE SYMMETRIC KEY mySymKey; GO
  • Permissions needed to decrypt a column: USE MADB; GRANT VIEW DEFINITION ON SYMMETRIC KEY::mySymKey TO myUser; GO GRANT VIEW DEFINITION ON Certificate::[myCertificate] TO myUser; GO GRANT CONTROL ON Certificate::[myCertificate] To myUser; GO
  • Read an encrypted column in an application: USE MADB; GO OPEN SYMMETRIC KEY mySymKey DECRYPTION BY CERTIFICATE myCertificate; SELECT myID, myCol_Encrypted AS ‘Encrypted Data’, CONVERT(varchar, DecryptByKey(myCol_Encrypted)) AS ‘Decrypted Data’ FROM maTable; GO

“Always Encrypted » encryption

There are several shortcomings associated with TDE encryption:

  • Only the written data is encrypted (protects data at rest)
  • It requires the entire database to be encrypted
  • All data is encrypted in the same way

The release of « Always Encrypted » encoding resolves these issues:

  • Data is encrypted at rest, in motion and in memory
  • You can choose to encrypt a single column
  • TEMPDB is not encrypted
  • 2 choices of encryption: Deterministic encryption (for indexes) and Random

It is, however, necessary to update the database driver used by the client software in order to support this new feature. Encryption/decryption is automatically carried out by the database driver.

Points to note when using this encryption:

  • The only operations that the Database Engine can perform on encrypted data are equality comparisons (only available using deterministic encryption).
  • The size of the columns in the database grows (usually doubles).
  • The data types to be encrypted have to be looked at carefully (datetime transformed into datetime2).
  • Be aware of MAX for varchar(MAX) and nvarchar(MAX) as these can cause type errors during decryption.
  • Memory-optimised tables (in-memory OLTP) are not supported.
  • Deterministically encrypted numbered columns must be in BIN2 Collation.
  • It is not possible to encrypt an IDENTITY column.
  • Data types: XML, IMAGE, NTEXT, TEXT, HIERARCHYID, SQL_VARIANT, GEOGRAPHY, GEOMETRY, ROWVERSION, and user-defined types are not supported.
  • Requires dotNet 4.6 (minimum)
  • Full Text Search is not supported

Continue for the next part

This is the end of the second part of this summary of the SQL Server encryption study. The following part will look at dynamic data masking and what we at Net4All advise about this. Meanwhile, please feel free to check out the rest of our blog content, or contact us to learn more.

Contact us