0

Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE

Posted on by Vincent Delabre
twitterlinkedinmail

Dans un premier épisode (https://blog.capdata.fr/index.php/le-chiffrement-et-sql-server-episode-1-transparent-data-encryption-tde-vs-always-encrypted ) nous avons eu l’occasion de voir les deux grandes approches du chiffrement dans SQL Server, sous un angle théorique.

Maintenant que l’on a couvert les concepts derrière le chiffrement “at rest” de SQL Server, voici comment le mettre en œuvre concrètement.

Concepts de Transparent Data Encryption

On notera que cela a été maquetté en SQL Server 2022, mais que mis à part la sauvegarde (cf. Episode 1), les concepts sont les mêmes.

 

Créer les clés de chiffrement

Le fonctionnement est donc le suivant :

La clé de chiffrement par Windows à SQL Server va permettre de créer la Database Master Key de la base Master. Celle-ci servira à signer le certificat de la base Master. Ce certificat stockera les clés asymétriques utilisés pour chiffrer les bases de données utilisateurs.

Windows fourni une Service Master Key au premier démarrage de SQL Server après son installation. Celle-ci n’est visible et exploitable que par le compte de service SQL Server. Elle peut être identifiée ainsi :


use master
go
select * from sys.symmetric_keys ;

Elle porte le nom de ##MS_ServiceMasterKey##. Par mesure de sécurité, on va sauvegarder cette SMK :


BACKUP SERVICE MASTER KEY TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\Service_Master_Key.smk' ENCRYPTION BY PASSWORD = 'HugEEEEEP@$$word4SMK!' ;
GO

On peut donc désormais créer le certificat dans la base master . Par défaut, celui-ci a une durée de vie d’un an. Cela peut potentiellement alourdir la maintenance, car il faudra déchiffrer la base, créer un nouveau certificat et re-chiffrer la base. Dans l’exemple ci-dessous, nous partons sur une durée de vie… “lointaine” :

Use master
go
CREATE CERTIFICATE MyServerMasterCertForTDE WITH SUBJECT = 'DEK Certificate used for TDE keys' ,EXPIRY_DATE = '2100-12-31';
GO

On peut la vérifier avec la commande suivante :

select * from sys.certificates where SUBJECT = 'DEK Certificate used for TDE keys';

De la même manière que l’on a sauvegardé la Service Master Key, on va sauvegarder ce certificat : il nous servira si l’on souhaite restaurer la base sur un autre serveur ou bien pour des solutions de haute disponibilité :

BACKUP CERTIFICATE MyServerMasterCertForTDE TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\Master_Certificate_for_TDE.crt' 
WITH PRIVATE KEY (FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\Master_Certificate_for_TDE_PrivateKey' , 
ENCRYPTION BY PASSWORD = 'HugEEEEEP@$$word4Cert!');

Maintenant que l’on a ce certificat, on peut enfin créer les clés de chiffrements TDE pour la base métier que l’on souhaite chiffrer :

Use AdventureWorks
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyServerMasterCertForTDE ; 
GO

Egalement, on peut vérifier la présence de notre paire de clés avec :

Use AdventureWorks
GO
select * from sys.key_encryptions

Deux lignes doivent apparaitre avec le key_id 101.

Il est possible que l’avertissement suivant apparaisse :

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key
associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server,
you must have backups of both the certificate and the private key or you will not be able to open the database.

C’est parce que vous avez oublié de réaliser une sauvegarde du certificat. Cela peut être vérifié aisément :


Use AdventureWorks
GO
SELECT pvt_key_last_backup_date,
Db_name(dek.database_id) AS encrypteddatabase,
c.name AS Certificate_Name
FROM master.sys.certificates c
INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint;

 

On voit donc le certificat associé à la paire de clé ainsi que la date de la dernière sauvegarde.

Activation de TDE pour la base

Maintenant que les pré-requis cryptographiques sont présents, on peut activer enfin TDE :

Use AdventureWorks
GO
ALTER DATABASE AdventureWorks SET ENCRYPTION ON ;
GO

Cela se fera en fond de tâche et n’empêchera pas l’utilisation de la base. La durée de l’opération dépendera de la volumétrie, de la version de SQL Server et des CPU présents (cf. Episode 1).

On peu cependant mettre en suspend si jamais cela consommait trop de ressource, à partir de SQL Server 2019 avec les commandes suivantes  :

ALTER DATABASE AdventureWorks SET ENCRYPTION SUSPEND ;
ALTER DATABASE AdventureWorks SET ENCRYPTION RESUME ;

Si jamais vous ne savez plus où vous en êtes du chiffrement de vos bases, vous pouvez également interroger vérifier ainsi :

select
db.name,
EncryptionStatus = CASE encryption_state
WHEN 0 THEN 'Pas de DEK, pas chiffrée' -- oui, alors dans les faits, si c'est le cas il n'y pas de ligne dans la table dek. Mais dans la doc...
WHEN 1 THEN 'DEK présente, pas chiffrée'
WHEN 2 THEN 'Chiffrement en cours'
WHEN 3 THEN 'Chiffrée'
WHEN 4 THEN 'Changement de clé en cours'
WHEN 5 THEN 'Déchiffrement en cours'
WHEN 6 THEN 'Changement de certificat ou clé en cours'
ELSE 'Statut inconnu'
END
from sys.dm_database_encryption_keys dek
LEFT OUTER JOIN sys.databases db ON dek.database_id = db.database_id

Cela nous renvoie ainsi le tableau suivant :

On voit ainsi que notre base AdventureWorks est bien chiffrée (et non pas “chiffrement en cours”). On voit également que la TempDb est chiffrée ! Eh oui : si jamais il y avait des opérations avec des tables temporaires, il faut bien entendu que les données soient chiffrée sur celle-ci. Cela veut donc dire un impact en terme de de performances pour les autres bases métier qui seraient sur le même serveur, même si elles ne sont pas chiffrées !

Intégration du chiffrement TDE dans un groupe de disponibilité Always-On

Maintenant, imaginons que l’on veuille mettre cette base de données dans un groupe de disponibilité AlwaysOn : comment faire ? Il suffit simplement de restaurer le certificat et sa clé privé sur le(s) réplicat(s) secondaire(s) pour pouvoir accéder aux données. C’est également la même procédure si on veut juste restaurer la base sur un autre serveur.

Dans un premier temps, on va s’assurer que le serveur qui nous servira de réplica secondaire a bien une Master Key  :

Use master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'HugEEEEEP@$$word!'
GO

On copie la paire de fichiers sauvegardés avec le certificat plus tôt sur le serveur destination (par exemple dans le répertoire de backup, mais attention à ne pas les y laisser trainer trop longtemps) :

Use master
go
CREATE CERTIFICATE MyServerMasterCertForTDE FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\Master_Certificate_for_TDE.crt'
WITH PRIVATE KEY (FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\Master_Certificate_for_TDE_PrivateKey',
DECRYPTION BY PASSWORD = 'HugEEEEEP@$$word4Cert!') ;
GO

A partir de maintenant, une base de données chiffrée avec une paire de clé issue de ce certificat sera lisible sur ce serveur !

On peut donc par exemple l’intégrer à un groupe de disponibilité déjà présent. Sur le réplica principal (LAB1SQL1 et LAB1SQL2 est le secondaire):

USE [master]
GO
ALTER AVAILABILITY GROUP [LAB1AG]
MODIFY REPLICA ON N'LAB1SQL2' WITH (SEEDING_MODE = AUTOMATIC)
GO

ALTER AVAILABILITY GROUP [LAB1AG]
ADD DATABASE [AdventureWorks];
GO

puis sur le réplica secondaire :

ALTER AVAILABILITY GROUP [LAB1AG] GRANT CREATE ANY DATABASE;
GO

Avec l’Automatic Seeding, la base sera crée et dupliquée chiffrée, sans même passer par l’étape de backup.

Remarques sur les différences de taille de base

Dans l’épisode 1, on parlait de l’impact du chiffrement sur la taille des backups. Ici, dans notre exemple avec SQL Server 2022 et la base AdventureWorks, la base de données sans TDE fait 54 688 Ko, tandis que la version avec TDE fait 52 292 Ko : autant dire que c’est négligeable et que l’écart est lié à la fragmentation après le déchiffrement.

Une fois de plus et comme expliqué dans l’épisode 1, pensez à mettre les sauvegardes de vos clés et certificats dans un endroit sécurisé !! En l’absence de votre certificat, vous serez incapable de restaurer vos données !

Dans le prochain épisode, nous verrons concrètement comment déployer une stratégie de chiffrement avec Always Encrypted : stay tuned !

Continuez votre lecture sur le blog :

twitterlinkedinmail

Vincent Delabre

More Posts

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.