Management Reporter: Base de données avec SQL AlwaysOn Availability Groups - Partie 1 (Difficile)

Dernièrement, j’ai écrit un billet sur un problème déchiffrement de données de la base de données Management Reporter lorsqu'elle est restaurée sur un autre serveur SQL. Vous pouvez la lire ici: Management reporter : The "DecryptData'" procédure attempted To return à status of NULL.

Le même problème survient lorsque la base de données ManagementReporter est dans un groupe de disponibilité Always On (Availability group). C'est-à-dire que lorsque vous basculez la base de données ManagementReporter vers le serveur secondaire, celui-ci ne sera pas capable de déchiffrer les données de la base de données. Par conséquence, la la base de données DataMart disparait de la console et le message d'erreur suivant apparait dans le journal d'évènements:

Please create a master key in the database or open the master key in the session before performing this operation.

The 'DecryptData' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. Originating procedure: DecryptData.

J’ai donc fait des recherches pour savoir comment configurer Management Reporter dans un environnement hautement disponible avec SQL AlwaysOn Availability Group. Je conseille de lire ce billet afin de comprendre le problème, toutefois, j'ai écrit un billet qui offre une solution plus facile: Management Reporter: Base de données avec SQL AlwaysOn Availability Groups - Partie 2 (Facile)

Lors de mes recherches, j’ai trouvé le lien suivant: High Availability best practices for Dynamics AX 2012. Dans la section Financial Reporting High Availability, on peut y lire: [...] If encryption is used, you need to apply the same configuration onto the other node to ensure business continuity after failover.[...].

Tout d’abord, je ne sais pas pourquoi il y a l’utilisation du mot "if" puisqu'a ma connaissance il est obligatoire d’utiliser le cryptage lors de du déploiement de Management Reporter. Ensuite, j’ai lu l’article recommandé et celui-ci explique comment exporter/importer le certificat de cryptage TDE. Cette technique ne fonctionne pas puisque ManagementReporter n’utilise pas TDE, il utilise plutôt une clé symétrique pour crypter des colonnes dans la base de données. Alors, j’ai poussé mes recherches voici ce que j'ai trouvé.

Tout d'abord, il faut comprendre les différente clé utilisées lors du processus de chiffrage/déchiffrage de SQL:

1. Service Key (SMK) – Généré par le service SQL, elle est utilisé pour crypter le Database Master Key (DMK).
2. Database Master Key (DMK) – utilisé pour crypter le certificat.
3. Certificat – Utilisé pour crypter la clé symétrique
4. Symmetric Key – Utilisé pour crypter les données (finalement!).

Sommaire: Service Key --> Database Master Key --> Certificat -->  Symmetric Key --> Données

Eh oui, assez fou ! Le problème dans notre cas c’est que le Service Key (SMK) du second serveur SQL n’est pas capable de déchiffrer le Database Master Key (DMK) situé dans la a base de données Management Reporter puisque celle-ci fut chiffré avec le Service Key du serveur primaire. Puisque SQL n'est pas capable d'ouvrir la DMK, il ne peut donc pas ouvrir le certificat, etc...

Regardons la configuration.

Tout d'abord, on peut y voir que SMK se trouve dans la base de données Master. Cette clé est crée lors du démarrage du service SQL et ne possède pas de mot de passe.

USE MASTER 

SELECT * 

FROM sys.symmetric_keys

Ensuite, je peux trouver toutes les bases de donnée avec une DMK encrypter par le SMK:

USE MASTER

SELECT name

FROM sys.databases

WHERE is_master_key_encrypted_by_server = 1

La DMK permet de déchiffrer les certificats. Donc, regardons les certificats dans la base de données Management Reporter:

USE ManagementReporter

SELECT name,

certificate_id,

subject, 

pvt_key_encryption_type_desc

FROM sys.certificates

Les certificats permettent de déchiffrer les clés symétriques. Donc, regardons les clés symétriques dans la base de données Management Reporter:

USE ManagementReporter

SELECT * 

FROM sys.symmetric_keys

En fait, il est possible de voir cela via l'interface graphique:

Les clés symétriques permettent de déchiffrer les données. Donc, trouvons les données chiffrer dans la base de données Management Reporter:

SELECT S.name AS 'Schema',
T.name AS 'Table', 

C.name AS 'Column', ty.name AS 'DataType'

FROM sys.columns C

JOIN sys.types ty ON C.system_type_id = ty.system_type_id

JOIN sys.tables T ON C.object_id = T.object_id

JOIN sys.schemas S ON T.schema_id = S.schema_id

WHERE ty.name IN ('binary', 'varbinary', 'image')

ORDER BY S.name, T.name, C.name, ty.name;

Ultimement, j'aimerais déchiffrer des données avec la cle symétrique, mais malheureuse j'ai trouvé aucune donnée chiffrée dans ma base de données ManagementReporter (les champs sont vides). Donc, le plus loin que je peux aller est d'ouvrir la clé symétrique avec l'aide du certificat.

Ensuite, j'ai basculé la base de données ManagementReporter vers mon serveur secondaire et j'ai testé l'ouverture de la clé symétrique. Sans surprise, j'ai reçu le message d'erreur suivant:

Please create a master key in the database or open the master key in the session before performing this operation.

OPEN SYMMETRIC KEY ConnectorServiceSymmetricKey
DECRYPTION BY CERTIFICATE ConnectorServiceCertificate
CLOSE SYMMETRIC KEY ConnectorServiceSymmetricKey

OPEN SYMMETRIC KEY GeneralUserSymmetricKey
DECRYPTION BY CERTIFICATE GeneralUserCertificate
CLOSE SYMMETRIC KEY GeneralUserSymmetricKey

Le problème est dû au fait que la SMK n'est pas capable d'ouvrir la DMK. Pour corriger le problème, il faut faire une sauvegarde de la SMK du serveur primaire et de la restaurer sur le serveur secondaire.

Sauvegarde sur le serveur primaire:

BACKUP SERVICE MASTER KEY
TO FILE = 'C:\SQLBackup\Keys\service_master_key'
ENCRYPTION BY PASSWORD = 'MettreUnMotdePasseComplexe'

Restauration sur le serveur secondaire:

RESTORE SERVICE MASTER KEY 
FROM FILE = 'C:\SQLBackup\Keys\service_master_key' 
DECRYPTION BY PASSWORD = 'MettreUnMotdePasseComplexe'

Toutefois, lors de la restauration, j'ai reçu le message suivant:

The old and new master keys are identical. No data re-encryption is required.

Donc, j'ai basculé la base de données vers le serveur secondaire et j'ai restauré la SMK. Cette fois-ci, j'ai reçu le message suivant:

An error occurred while decrypting master key 'ManagementReporter' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.

Cette erreur survient parce que SQL tente de déchiffrer la DMK afin de la rechiffrer avec la nouvelle clé. Il y a l'option FORCE, mais celle-ci peut causer des pertes de données. A la place d'utiliser l'option FORCE, j'ai manuellement ouvert la DMK pour ajouter la SMK:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'PasswordDuringMRInstallation'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Si vous ne connaissez pas le mot de passe de la DMK, je suggère de faire la procédure suivante: Error messages when you restore a Microsoft Management Reporter 2012 database: The operation could not be completed.

Ensuite, j'ai fait la restauration de la SMK:

RESTORE SERVICE MASTER KEY 
FROM FILE = 'C:\SQLBackup\Keys\service_master_key' 
DECRYPTION BY PASSWORD = 'MettreUnMotdePasseComplexe'

Finalement, j'ai fait des tests de basculement de la base de données et j'étais capable d'ouvrir les clés symétriques sur les deux serveurs et Management Reporter fonctionnait parfaitement.

Source:
- Configuring MS SQL for AlwaysOn Automated Failover of the K2 Database.
- ALTER SERVICE MASTER KEY
- RESTORE SERVICE MASTER KEY
- Finding Encrypted Data in a SQL Server Database
- Database Master Key Error After Database Restore