Activation Encryption

Cela implique d'avoir installé ou fait passer l'instance en Enterprise Edition (release 2016 et 2017)

-- Vérification de la version du moteur
 Select @@version

Création de la clef maître au niveau instance

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxx'
GO

Création du certificat d'instance

Use master
GO
CREATE CERTIFICATE FR0_VSIAAS_7003_INWP1Z81_TDECertificate WITH SUBJECT = 'SQL Server TDE Certificate'
GO
 
SELECT * FROM sys.certificates where [name] = 'FR0_VSIAAS_7003_INWP1Z81_TDECertificate'
GO

Création de la clef d'encryption pour la base

Use [DBWP1Z81]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE "FR0-VSIAAS-7003_INWP1Z81_TDECertificate"
GO

L'avertissement suivant va apparaître:
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

Sauvegarde du certificat d'instance par précaution:

Use master
GO
BACKUP CERTIFICATE "FR0-VSIAAS-7003_INWP1Z81_TDECertificate"
TO FILE = 'F:\Local\BackupSQL\INWP1Z81\Backup\Certificate\TDE\TDECertificate_FR0-VSIAAS-7003_INWP1Z81.cert' WITH PRIVATE KEY
( FILE = 'F:\Local\BackupSQL\INWP1Z81\Backup\Certificate\TDE\EncryptPrivateKey_FR0-VSIAAS-7003_INWP1Z81.key',
ENCRYPTION BY PASSWORD = 'b_N566H__2Q3h_iv_RfH')
GO

On lance l'encryption de la base en question (et de la TempDB si elle n'a pas été encryptée auparavant)

Use master
go
ALTER DATABASE DBWP1Z81 SET ENCRYPTION ON
GO

Vérification du statut d'encryption

-- 0 = No database encryption key present, no encryption 
-- 1 = Unencrypted 
-- 2 = Encryption in progress
-- 3 = Encrypted 
-- 4 = Key change in progress 
-- 5 = Decryption in progress
-- 6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)
Use master
GO
SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
         WHEN '0'  THEN  'No database encryption key present, no encryption'
         WHEN '1'  THEN  'Unencrypted'
         WHEN '2'  THEN  'Encryption in progress'
         WHEN '3'  THEN  'Encrypted'
         WHEN '4'  THEN  'Key change in progress'
         WHEN '5'  THEN  'Decryption in progress'
         WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
         ELSE 'No Status'
         END,
percent_complete,encryptor_thumbprint, encryptor_type, key_algorithm AS Algorithm, key_length AS KeyLength
FROM sys.dm_database_encryption_keys