====== Activation Encryption ======
Cela implique d'avoir installé ou fait passer l'instance en Enterprise Edition (release 2016 et 2017)
<code TSQL>
-- Vérification de la version du moteur
 Select @@version
</code>
{{:wiki:version_mssql.png?1000|}}

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

<code TSQL>
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxx'
GO
</code>
\\  
== Création du certificat d'instance  ==
<code TSQL>
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
</code>
\\  
== Création de la clef d'encryption pour la base ==
<code TSQL>
Use [DBWP1Z81]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE "FR0-VSIAAS-7003_INWP1Z81_TDECertificate"
GO
</code>
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: ==
<code TSQL>
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
</code>
\\  
== On lance l'encryption de la base en question (et de la TempDB si elle n'a pas été encryptée auparavant) ==
<code TSQL>
Use master
go
ALTER DATABASE DBWP1Z81 SET ENCRYPTION ON
GO
</code>
\\  
====== Vérification du statut d'encryption ======

<code TSQL>
-- 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
</code>
{{:sql_server:tde_db_encryptionstatus.png?1000|}}