Installation

--Make sure you are running this on the PRIMARY.
--This script sets up a TDE wrapping key, login and credential on the primary.
--Create wrapping key
USE master
CREATE ASYMMETRIC KEY dbWrappingKey FROM PROVIDER SQLEKM
WITH PROVIDER_KEY_NAME='ekmWrappingKey',
CREATION_DISPOSITION = CREATE_NEW, ALGORITHM = RSA_2048;
GO
--Create wrapping key credential. Select one of OCS card, or else softcard.
--Comment out option you do not want to use.
--OCS card example
USE master
CREATE LOGIN tdeLogin FROM ASYMMETRIC KEY dbWrappingKey;
CREATE CREDENTIAL tdeCredential WITH IDENTITY = 'OCS1', SECRET = '+453X7V]MR'
FOR CRYPTOGRAPHIC PROVIDER SQLEKM;
ALTER LOGIN tdeLogin ADD CREDENTIAL tdeCredential;
GO
--Softcard example. Not used here, so commented out.
--Use master
--CREATE LOGIN tdeLogin FROM ASYMMETRIC KEY dbWrappingKey;
--CREATE CREDENTIAL tdeCredential WITH IDENTITY = 'scard1', SECRET = '0O*dG0ffz2'

--FOR CRYPTOGRAPHIC PROVIDER SQLEKM;
--ALTER LOGIN tdeLogin ADD CREDENTIAL tdeCredential;

--Make sure you are running this on the SECONDARY.
--NOTE the wrapping key must already exist, as created by the primary.
--This script opens a wrapping key, TDE login and credential on a secondary.
--The credential must match (same OCS cardset/softcard and password) as primary.
--Create wrapping key
USE master
CREATE ASYMMETRIC KEY dbWrappingKey FROM PROVIDER SQLEKM
WITH PROVIDER_KEY_NAME='ekmWrappingKey',
CREATION_DISPOSITION = OPEN_EXISTING; --Wrapping key should already have been created on the
primary.
GO
--Create wrapping key credential. Select one of OCS card, or else softcard.
--Comment out option you do not want to use.
--OCS card example
USE master
CREATE LOGIN tdeLogin FROM ASYMMETRIC KEY dbWrappingKey;
CREATE CREDENTIAL tdeCredential WITH IDENTITY = 'OCS1', SECRET = '+453X7V]MR'
FOR CRYPTOGRAPHIC PROVIDER SQLEKM;
ALTER LOGIN tdeLogin ADD CREDENTIAL tdeCredential;
GO
--Softcard example. Not used here, so commented out.
--Use master
--CREATE LOGIN tdeLogin FROM ASYMMETRIC KEY dbWrappingKey;
--CREATE CREDENTIAL tdeCredential WITH IDENTITY = 'scard1', SECRET = '0O*dG0ffz2'
--FOR CRYPTOGRAPHIC PROVIDER SQLEKM;
--ALTER LOGIN tdeLogin ADD CREDENTIAL tdeCredential;

--Run on primary/secondary that appears to be unsynchronized with availability group.
USE master;
GO

ALTER DATABASE [SourceDatabase] SET HADR RESUME

--Make sure you are running this on PRIMARY
--Create actual database encryption key (TDEDEK)
USE SourceDatabase;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY dbWrappingKey;
GO
--A short delay may be required here before switching on encryption.
WAITFOR DELAY '00:00:05'; -- Set delay period as required. One second = '00:00:01'
-- Break any connection with the SourceDatabase so that encryption can commence.
USE [master];
GO
-- Enable TDE (switch on encryption) on the SourceDatabase:
ALTER DATABASE SourceDatabase SET ENCRYPTION ON;
GO

-- Break any connection with the SourceDatabase so that encryption can commence.
USE [master];
GO
-- Enable TDE (switch on encryption) on the SourceDatabase:
ALTER DATABASE SourceDatabase SET ENCRYPTION ON;
GO

-- Encryption state check. Returns the encryption state of databases.
SELECT DB_NAME(e.database_id) AS DatabaseName, e.database_id, e.encryption_state, CASE e.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'
END AS encryption_state_desc, c.name, e.percent_complete FROM sys.dm_database_encryption_keys
AS e
LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint

--Run this on PRIMARY in high availability group environment.
--Switch off TDE encryption.
USE [master];
ALTER DATABASE SourceDatabase SET ENCRYPTION OFF;
GO

--Drop the database encryption key (TDEDEK)
USE SourceDatabase
DROP DATABASE ENCRYPTION KEY;

--You must have switched off TDE encryption on primary before running this script.
--Run this on SECONDARY in high availability group environment.
USE master;
GO
--Drop the TDE credential and login
ALTER LOGIN tdeLogin DROP CREDENTIAL tdeCredential;
DROP LOGIN tdeLogin;
DROP CREDENTIAL tdeCredential;

--Drop the wrapping key from database only
DROP ASYMMETRIC KEY dbWrappingKey;

--Run this on PRIMARY in high availability group environment.
USE master;
GO
--Drop the TDE credential and login on primary.
ALTER LOGIN tdeLogin DROP CREDENTIAL tdeCredential;
DROP LOGIN tdeLogin;
DROP CREDENTIAL tdeCredential;
--Select option below to remove wrapping key from database only, or both database
--and SQLEKM provider.
--If you remove the wrapping key copy from the SQLEKM provider, it will be lost
--forever. If you do this, be sure this is what you want to do.
--Drop the wrapping key from database only
DROP ASYMMETRIC KEY dbWrappingKey;
--Drop the wrapping key from both database and SQLEKM provider
--DROP ASYMMETRIC KEY dbWrappingKey REMOVE PROVIDER KEY;