SQL Server 2014–Chiffrement des sauvegardes avec un certificat

SQL Server 2014, comme toute nouvelle version, apporte son lot de nouveautés. Certaines sont liées à la disponibilité, d’autres à la performance ou au développement, et certaines ont trait à la sécurité.Un prochain post devrait d’ailleurs aborder une fonctionnalité attendu : refuser l’accès aux données à un administrateur.

Mais, pour le moment, parlons sauvegardes. Oui : est-ce que vos sauvegardes sont protégées ? Le but n’est pas ici d’énumérer la longue liste du pourquoi, du comment et des conséquences de la disponibilité de données “privées” d’une entreprise à un Tiers.

Alors, oui il faut protéger les données, à plusieurs niveaux :

• Accès physique restreint aux serveurs
• Accès restreint à l’OS (qui est administrateur Windows)
• Accès restreint à l’instance SQL (qui est sysadmin, qui est db_owner, qui est db_datareader, etc …)
• Possibilité de chiffrer le transport des données, via SSL
• Possibilité de chiffrer les données
• Chiffrage au niveau colonnes avec un système de passphrase (encryptByPassPhrase)
• Chiffrage au niveau colonnes avec des clés symétriques ou  asymétriques (EncryptByKey)
• Chiffrage niveau base avec le TDE
• Possibilité de protéger les sauvegardes
• Le TDE protège de fait les sauvegardes car impossible de restaurer ou attacher des fichiers sans le certificat
• L’option PASSWORD sur l’ordre Backup Database (présent en 2008R2, plus disponible depuis 2012)

Sachant qu’il est possible de placer les fichiers de sauvegarde dans le cloud (j’y reviendrais dans un prochain billet) mais que probablement un certain nombre d’entre vous externalisiez déjà une partie de vos sauvegardes, afin de se protéger d’un désastre niveau datacenter, il était urgent de fournir ne nouvelle solution.

SQL Server 2014 propose la protection des sauvegardes via Certificat. Enfin !

En premier lieu, il convient de créer une master key et un certificat. Ensuite, et bien, c’est tout, il suffit de sauvegarder la base …

--Création de la master key
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '##UnM0tDePasseC0mplexe!!!';
GO 


 --Création du certificat
Use Master
GO
CREATE CERTIFICATE CertificatPourSauvegardes
 WITH SUBJECT = 'Le certificat qui permettra de chiffrer les sauvegardes';
GO 

 -- Sauveagrde de la base ...
BACKUP DATABASE [AdventureWorks] 
TO DISK = 'C:\temp\AdventureWorks.bak'
WITH INIT,
     COMPRESSION,
	 Stats=5,
     ENCRYPTION 
	 (
		ALGORITHM = AES_256, 
		SERVER CERTIFICATE = CertificatPourSauvegardes
	 );

 -- Et c'est tout !

Oups … Pensez, à sauvegarder aussi votre certificat. Sans certificat, si vous crashez votre serveur ou si vous souhaitez déplacer votre base sur une autre instance, pas de restaure possible !

Il est donc fondamental, tout comme avec le TDE, de sauvegarder le certificat en lieu sur, et de ne pas stocker au même endroit les sauvegardes des bases et la sauvegarde du certificat !

BACKUP CERTIFICATE CertificatPourSauvegardes
TO FILE = 'c:\temp\CertificatPourSauvegardes.cer'
WITH PRIVATE KEY (FILE='c:\temp\CertificatPourSauvegardes.pvk',
                  ENCRYPTION BY PASSWORD = '##UnAutrePWDintr0uvable!');

Notez ensuite, que malgré l’option INIT, il se sera pas possible de faire une nouvelle sauvegarde de base de données dans le même fichier. Il faut utiliser l’option FORMAT si vous souhaitez écraser le fichier de sauvegarde.

Le warning concernant la sauvegarde du certificat a bien évidement disparu …

Plusieurs algorithmes de cryptages sont possible : AES_256 , AES_128, AES_192 et Triple_DES.

L’utilisation du certificat pour la sauvegarde est également accessible depuis l’interface graphique :

Mais pour l’instant, cela ne fonctionne que pour le backup dans le cloud (URL) dont je parlerais dans un autre billet. Est-ce une limitation de la CTP, l’interface graphique n’étant pas terminé. C’est fort probable.

Si l’on supprime le certificat (ou si l’on change d’instance), la restauration devient impossible :

-- supression du certificat
DROP CERTIFICATE CertificatPourSauvegardes;
GO

-- Tentative de restoration de la base
USE [master]
RESTORE DATABASE [AdventureWorks] 
FROM  DISK = N'C:\temp\AdventureWorks.bak' 
WITH  STATS = 5,REPLACE;
GO

Si l’on restaure le certificat (en fait on le créé à partir de la sauvegarde), il sera alors possible de restaurer la base :

-- Restauration du certificat
USE [master]
CREATE CERTIFICATE CertificatPourSauvegardes
FROM FILE = 'c:\temp\CertificatPourSauvegardes.cer'
WITH PRIVATE KEY (FILE='c:\temp\CertificatPourSauvegardes.pvk',
                  DECRYPTION BY PASSWORD = '##UnAutrePWDintr0uvable!');
GO

USE [master]
RESTORE DATABASE [AdventureWorks] 
FROM  DISK = N'C:\temp\AdventureWorks.bak' 
WITH  STATS = 5,REPLACE;
GO

Revenons quelques secondes sur le certificat. Par défaut, dans SQL Server, un certificat auto généré a une durée de vie de … 1 an.

Que se passe t’il si l’on essaie de sauvegarder une base avec un certificat expiré ou bien de restaurer une base dont le certificat était valide au moment du backup mais qui a expiré depuis ?

Histoire de tester la restore d’une BD dont le certificat n’est plus valide, alors qu’il l’était au moment du backup, je vais changer date d’expiration du certificat et avancer de quelques jours la date système.

-- Creation d'un certificat
-- avec date d'expiration
Use Master
GO
DROP CERTIFICATE CertificatPourSauvegardes;
GO
CREATE CERTIFICATE CertificatPourSauvegardes
WITH SUBJECT = 'Le certificat qui permettra de chiffrer les sauvegardes',
	 START_DATE='20130101', 
	 EXPIRY_DATE='20131215'; 
GO

-- visualisation du certificat
SELECT name,expiry_date,start_date
FROM sys.certificates;

On sauvegarde la base comme cela a été fait plus haut. On modifie ensuite la date système pour être au delà de la période de validité du certificat.

On lance une restauration de base : cela a fonctionné.

Oui, il a été possible de restaurer une base alors que la date d’expiration a été dépassée. Je pense que ce comportement est voulu.

En ce qui concerne la sauvegarde, en toute logique, il n’est pas possible de sauvegarder une base à l’aide d’un certificat expiré :

Si vous souhaitez restaurer une vieille base sur un nouveau serveur, dont le certificat d’origine a expiré, sachez que c’est également possible, un message nous informe juste que le certificat que nous venons de créer n’est plus valide :

Pensez donc à bien sauvegarder vos certificats … Et vos bases !

Et côté performances, est-ce que l’on consomme des ressources ? Nécessairement, oui.

SET STATISTICS TIME ON

BACKUP DATABASE  [ContosoRetailDW]
TO DISK = 'C:\backup\ContosoRetailDW.bak'
WITH FORMAT,
     COMPRESSION,
	 Stats=5;
			
BACKUP DATABASE  [ContosoRetailDW]
TO DISK = 'C:\backup\ContosoRetailDW_certificat.bak'
WITH FORMAT,
     COMPRESSION,
	 Stats=5,
     ENCRYPTION 
	 (
		ALGORITHM = AES_256, 
		SERVER CERTIFICATE = CertificatPourSauvegardes
	 );
			

Cette base un peu plus volumineuse que les bases de test classique prends effectivement 2 secondes de plus à sauvegarder. Le prix à payer pour la sécurité. Il faudrait faire de plus amples mesures avec des bases de différentes tailles pour voir s’il s’agit d’un surtout linéaire,  fonction de la taille de la base, ou bien plutôt de type exponentiel ou logarithmique … Qui a un serveur SQL 2014 en prod pour mener ces tests sur des cas réels ?

Fort heureusement, la même quantité de données est sauvegardée ! C’est une blague. Il n’est pas possible que le chiffrement influe sur le nombre de pages de données. Par contre, on observe un très léger changement dans la taille de la sauvegarde :

Des informations supplémentaires sont stockées sur la table système backupset :

En conclusion, la protection de la sauvegarde via certificat me semble être une très bonne chose. Niveau licensing, je n’ai pas trouvé d’information précise sur la disponibilité de cette fonction. Est-ce qu’elle sera limitée à l’édition entreprise, tout comme l’est le TDE ? Je en pense pas, car le MSDN laisse entendre que seules les éditions Web et Express ne pourront pas utiliser cette fonctionnalité lors de la sauvegarde mais qu’il sera néanmoins possible de restaurer une base sur ces éditions alors qu’elles a été sauvegardée chiffrée à l’aide d’un certificat.

Soyons optimiste, cela va être possible en édition standard …

Bonnes sauvegardes …