SQL Server 2014 commence à pointer le bout de son nez avec la CTP2. Cette nouvelle version va apporter pas mal de nouveautés côté moteur notamment la plus attendue Hekaton mais d’autres seront également de la partie comme une amélioration continue de la haute disponibilité avec SQL Server AlwaysOn, la sécurité avec le chiffrement des sauvegardes ou encore la limitation des actions possibles pour un administrateur de bases de données, les index columstore index pouvant être mis à jour et bien d’autres. Dans ce billet j’ai choisi de parler de SQL Server AlwaysOn et du chiffrement des bases de données et des sauvegardes. Un premier billet sur la possibilité de chiffrer les sauvegardes avec SQL14 a déjà par Christophe Laporte. Je vous invite à le lire si vous voulez en savoir un peu plus sur cette fonctionnalité.

En testant SQL Server 2014, AlwaysOn et le chiffrement certaines questions me sont venus à l’esprit.  Ces questions sont les suivantes :

• Est-ce qu’une table Hekaton (In memory) est chiffrée si la base de données qui l’héberge utilise TDE ?
• Est-ce qu’il est possible d’implémenter TDE avec une base de données déjà membre d’un groupe de disponibilité ?
• Quel avantage à avoir du chiffrement de sauvegardes de bases de données si celles-ci le sont déjà avec TDE ?

Est-ce qu’une table Hekaton (In memory) est chiffrée si la base de données qui l’héberge utilise TDE ?

Tout dépend ici de quoi l’on parle. Une table Hekaton réside en mémoire mais selon sa durabilité les données la concernant peuvent être stockées sur disque. TDE chiffre les données sur disque et pas en mémoire. Un processus de chiffrement et déchiffrement opère en temps réel sur les fichiers de données et journaux de transactions (cf la BOL) . Donc ici on peut se demander si le chiffrement s’effectue pour les tables Hekaton durables car celles-ci utilisent des fichiers de données et des fichiers delta afin de pouvoir remonter les données en cas de problème.

Activons maintenant TDE sur la base de données AdventureWorks2012.

… et là SQL Server nous donne rapidement la réponse attendue :

Les tables Hekaton durables en réalité reposent sur un stockage de type filestream. On peut le voir sur l’image ci-dessous :

Un coup d’œil sur la documentation en ligne de SQL Server à propos de TDE nous permet de confirmer que le chiffrement n’est pas valable pour ce type de conteneur. Cela est également confirmé par ce lien pour SQL14. –> TDE n’est donc pas supporté avec l’utilisation des tables Hekaton.

  Est-ce qu’il est possible d’implémenter TDE avec une base de données déjà membre d’un groupe de disponibilité ?

Nous avons implémenté une belle architecture AlwaysOn et une des bases de données d’un groupe de disponibilité requiert maintenant d’être chiffré pour des raisons de sécurité. Comment dois je procéder dans ce cas ? Est-ce qu’il suffit d’activer TDE sur la base de données située sur le réplica primaire ? Dois-je effectuer des manipulations sur les secondaires ? Dois-je repartir de zéro en supprimant cette base de données du groupe de disponibilité, chiffrer la base de données et refaire tout le processus d’ajout à un groupe de disponibilité (backup, restauration sur les secondaires et ajout au groupe de disponibilité) ?

Soyons un peu feignant et partons du principe que tout est à faire depuis le réplica primaire et que tout sera répliqué par la suite par AlwaysOn .. après tout ca sert à cela la réplication non ? . Précédemment nous avons implémenté TDE sur la base de données AdventureWorks2012 qui faisait parti d’un groupe de disponibilité lst_adv. On pourrait conclure que tout est fini mais si l’on jette un rapide coup d’œil à l’état de synchronisation de notre groupe de disponibilité on s’aperçoit vite qu’un problème est survenu suite à la mise en place de TDE et que la base de données Adventureworks2012 est même dans un état suspect sur les serveurs secondaires sur SSMS.

Parfait … on active TDE et on se retrouve avec une base de données suspecte !! Bravo TDE !! Ne paniquons pas et regardons un peu plus loin dans les logs d’erreurs SQL on peut voir ceci :

La cause du problème est la suivante : après avoir chiffré notre base de données sur le réplica primaire, les réplicas secondaires ne trouvent pas le certificat dont l’emprunte correspond à celle utilisé par le certificat créé initialement pour le chiffrement TDE. Dans ce cas la réplication du flux de données du réplica primaire vers réplicas secondaires est donc naturellement suspendue. Ce comportement est tout à fait normal puisque nous essayons de répliquer une base de données chiffrée vers une base de données qui ne peut pas être chiffrée. Ce fameux certificat est hébergé dans la base de données master et n’est pas répliqué automatiquement par AlwaysOn contrairement à la clé de chiffrement de bases de données située dans la base de données AdventureWorks2012. Il ne nous reste plus qu’à sauvegarder ce certificat, le restaurer sur les réplicas secondaires et relancer la réplication des données pour AdventureWorks2012 :

Après quelques secondes ou minutes (en fonction de la quantité de données à répliquer car l’état de synchronisation est suspendue) on peut voir sur le dashbord du groupe de disponibilité lst_adv que tout est rentré dans l’ordre :

Pour résumé, il est possible d’implémenter TDE sur une topologie AlwaysOn existante à condition de bien penser à restaurer le certificat servant au chiffrement de la clé de chiffrement de la base de données pour TDE. Le mode feignant est donc à proscrire

 Quel avantage à avoir du chiffrement de sauvegardes de bases de données si celles-ci le sont déjà avec TDE ?

La documentation en ligne concernant TDE explique qu’une sauvegarde provenant d’une base chiffrée par TDE est également chiffrée par le même certificat de serveur. Quel intérêt à chiffrer de nouveau le média de backup par la nouvelle fonctionnalité de chiffrement proposée avec SQL Server 14 si celle-ci est déjà initialement chiffrée par TDE ? Je me suis posé la question en pensant qu’une fois qu’une base était chiffrée par TDE, le chiffrement des sauvegardes n’avaient aucun effet. Mais je me trompais bien sûr : le chiffrement des sauvegardes avec SQL14 agit indépendamment de TDE et chiffrera une seconde fois le média de sauvegarde. Cela nous permet d’ajouter une protection supplémentaire au média de sauvegarde à savoir que chaque chiffrement peut utiliser un algorithme différent. Voyons comment cela se passe.

On commence donc par créer un certificat dédié aux chiffrement des sauvegardes dans la base de données master :

On effectue une sauvegarde de notre base de données AdventureWorks2012 avec le nouveau mot clé ENCRYTION pour la commande BACKUP :

On a maintenant un média de sauvegarde doublement chiffré. Si maintenant on tente de lire le contenu de ce média depuis une nouvelle instance SQL on se retrouve avec le message d’erreur suivant :

La lecture de l’entête du média de sauvegarde est impossible si le serveur SQL ne possède pas un certain certificat avec l’emprunte décrite ci-dessus. Par déduction, on peut aussi affirmer que la restauration ne sera pas impossible pour le moment.

Restaurons le certificat utilisé pour le chiffrement des sauvegardes dans la base de données master de la nouvelle instance SQL et essayons à nouveau de lire le contenu du média de sauvegarde :

Cela ne fonctionne toujours pas … on peut remarquer que l’emprunte du certificat manquant est différent cette fois. Il correspond en réalité à celle du certificat créé initialement pour activer TDE sur AdventureWorks2012. Après avoir restauré ce certificat il est possible de lire le contenu du média de sauvegarde et donc de restaurer notre base de données :

Pour un nouveau réplica AlwaysOn, il faudra donc bien penser à restaurer tous les certificats nécessaires à la restauration d’une base de données avant de la joindre au groupe de disponibilité concerné.

Bon chiffrement !!

David BARBARIN (Mikedavem)
MVP et MCM SQL Server