256 lines
7.0 KiB
Transact-SQL
256 lines
7.0 KiB
Transact-SQL
/* TDE ACTIVATION */
|
|
USE [master];
|
|
GO
|
|
|
|
DECLARE @oldlogin VARCHAR(255),
|
|
@keyname VARCHAR(255) = 'TDE_20241101',
|
|
@AZkeyname VARCHAR(255) = 'SQLTDE';
|
|
|
|
SELECT @oldlogin = sp.name
|
|
FROM sys.server_principal_credentials pc
|
|
INNER JOIN sys.credentials c
|
|
ON pc.credential_id = c.credential_id
|
|
JOIN sys.server_principals sp
|
|
ON sp.principal_id = pc.principal_id
|
|
WHERE c.name = 'sysadmin_ekm_cred';
|
|
|
|
IF (@oldlogin IS NOT NULL) EXEC ('ALTER LOGIN ' + @oldlogin + ' DROP CREDENTIAL [sysadmin_ekm_cred];');
|
|
|
|
ALTER LOGIN [sysadmin_ekm] ADD CREDENTIAL [sysadmin_ekm_cred];
|
|
|
|
EXEC ('IF NOT EXISTS(SELECT 1 FROM sys.asymmetric_keys WHERE name = ''' + @keyname + ''') CREATE ASYMMETRIC KEY [' + @keyname + ']
|
|
FROM PROVIDER [AzureKeyVault_EKM_Prov]
|
|
WITH PROVIDER_KEY_NAME = ''' + @AZkeyname + ''', CREATION_DISPOSITION = OPEN_EXISTING;');
|
|
|
|
ALTER LOGIN [sysadmin_ekm] DROP CREDENTIAL [sysadmin_ekm_cred];
|
|
|
|
EXEC ('IF NOT EXISTS (SELECT 1 FROM sys.syslogins where name = ''' + @keyname + ''')CREATE LOGIN [' + @keyname + '] FROM ASYMMETRIC KEY [' + @keyname + '];');
|
|
|
|
EXEC ('ALTER LOGIN [' + @keyname + '] ADD CREDENTIAL [sysadmin_ekm_cred];');
|
|
|
|
DECLARE @sqlCommand VARCHAR(MAX);
|
|
|
|
SET @sqlCommand
|
|
= 'USE ? IF NOT EXISTS (SELECT 1 FROM sys.dm_database_encryption_keys AS e
|
|
LEFT JOIN master.sys.asymmetric_keys AS c
|
|
ON e.encryptor_thumbprint = c.thumbprint
|
|
WHERE DB_NAME(e.database_id) = DB_NAME() AND c.name like ''TDE%'') IF DB_ID(''?'') > 4
|
|
BEGIN
|
|
CREATE DATABASE ENCRYPTION KEY
|
|
WITH ALGORITHM = AES_256
|
|
ENCRYPTION BY SERVER ASYMMETRIC KEY [' + @keyname + '];
|
|
ALTER DATABASE ? SET ENCRYPTION ON
|
|
END;';
|
|
|
|
EXEC master..sp_MSforeachdb @sqlCommand;
|
|
|
|
|
|
|
|
|
|
|
|
BACKUP DATABASE [TestTDE]
|
|
TO DISK = N'D:\SQLDatabaseDump\TDE_20241101.bak'
|
|
WITH COPY_ONLY,
|
|
NOFORMAT,
|
|
INIT,
|
|
NAME = N'TestTDE-Full Database Backup',
|
|
SKIP,
|
|
NOREWIND,
|
|
NOUNLOAD,
|
|
COMPRESSION,
|
|
STATS = 10;
|
|
GO
|
|
|
|
|
|
|
|
/* ROTATE */
|
|
|
|
/* CREATE NEW KEY IN AZURE BY POWERSHELL */
|
|
USE [master];
|
|
GO
|
|
|
|
DECLARE @oldlogin VARCHAR(255),
|
|
@keyname VARCHAR(255) = 'TDE_20241201',
|
|
@AZkeyname VARCHAR(255) = 'SQLTDE';
|
|
|
|
SELECT @oldlogin = sp.name
|
|
FROM sys.server_principal_credentials pc
|
|
INNER JOIN sys.credentials c
|
|
ON pc.credential_id = c.credential_id
|
|
JOIN sys.server_principals sp
|
|
ON sp.principal_id = pc.principal_id
|
|
WHERE c.name = 'sysadmin_ekm_cred';
|
|
|
|
EXEC ('ALTER LOGIN ' + @oldlogin + ' DROP CREDENTIAL [sysadmin_ekm_cred];');
|
|
|
|
ALTER LOGIN [sysadmin_ekm] ADD CREDENTIAL [sysadmin_ekm_cred];
|
|
|
|
EXEC ('CREATE ASYMMETRIC KEY [' + @keyname + ']
|
|
FROM PROVIDER [AzureKeyVault_EKM_Prov]
|
|
WITH PROVIDER_KEY_NAME = ''' + @AZkeyname + ''', CREATION_DISPOSITION = OPEN_EXISTING;');
|
|
|
|
ALTER LOGIN [sysadmin_ekm] DROP CREDENTIAL [sysadmin_ekm_cred];
|
|
|
|
EXEC ('CREATE LOGIN [' + @keyname + '] FROM ASYMMETRIC KEY [' + @keyname + '];');
|
|
|
|
EXEC ('ALTER LOGIN [' + @keyname + '] ADD CREDENTIAL [sysadmin_ekm_cred];');
|
|
|
|
DECLARE @sqlCommand VARCHAR(MAX);
|
|
|
|
SET @sqlCommand
|
|
= 'USE ? IF EXISTS (SELECT 1 FROM sys.dm_database_encryption_keys AS e
|
|
LEFT JOIN master.sys.asymmetric_keys AS c
|
|
ON e.encryptor_thumbprint = c.thumbprint
|
|
WHERE DB_NAME(e.database_id) = DB_NAME() AND c.name like ''TDE%'') ALTER DATABASE ENCRYPTION KEY
|
|
ENCRYPTION BY SERVER ASYMMETRIC KEY [' + @keyname + '];';
|
|
|
|
EXEC master..sp_MSforeachdb @sqlCommand;
|
|
|
|
EXEC ('DROP LOGIN ' + @oldlogin + ';');
|
|
EXEC ('DROP ASYMMETRIC KEY [' + @oldlogin + '];');
|
|
|
|
|
|
|
|
|
|
|
|
/* RESTORE OLD BACKUP */
|
|
USE [master];
|
|
GO
|
|
|
|
|
|
DECLARE @oldlogin VARCHAR(255),
|
|
@keynametorestore VARCHAR(255) = 'TDE_20241101',
|
|
@keyname VARCHAR(255) = 'TDE_20241201',
|
|
@db VARCHAR(255) = 'TestTDE2',
|
|
@AZkeyname VARCHAR(255) = 'SQLTDE/f514174481184130aef24e8999dd14c4';
|
|
|
|
SELECT @oldlogin = sp.name
|
|
FROM sys.server_principal_credentials pc
|
|
INNER JOIN sys.credentials c
|
|
ON pc.credential_id = c.credential_id
|
|
JOIN sys.server_principals sp
|
|
ON sp.principal_id = pc.principal_id
|
|
WHERE c.name = 'sysadmin_ekm_cred';
|
|
|
|
EXEC ('ALTER LOGIN ' + @oldlogin + ' DROP CREDENTIAL [sysadmin_ekm_cred];');
|
|
|
|
ALTER LOGIN [sysadmin_ekm] ADD CREDENTIAL [sysadmin_ekm_cred];
|
|
|
|
EXEC ('CREATE ASYMMETRIC KEY [' + @keynametorestore + ']
|
|
FROM PROVIDER [AzureKeyVault_EKM_Prov]
|
|
WITH PROVIDER_KEY_NAME = ''' + @AZkeyname + ''', CREATION_DISPOSITION = OPEN_EXISTING;');
|
|
|
|
|
|
EXEC ('CREATE LOGIN [' + @keynametorestore + '] FROM ASYMMETRIC KEY [' + @keynametorestore + '];');
|
|
|
|
EXEC ('ALTER LOGIN [sysadmin_ekm] DROP CREDENTIAL [sysadmin_ekm_cred];');
|
|
|
|
EXEC ('ALTER LOGIN [' + @keynametorestore + '] ADD CREDENTIAL [sysadmin_ekm_cred];');
|
|
|
|
|
|
RESTORE DATABASE [TestTDE2]
|
|
FROM DISK = N'D:\SQLDatabaseDump\TDE_20241101.bak'
|
|
WITH FILE = 1,
|
|
MOVE N'AdventureWorks2022'
|
|
TO N'F:\SQLDatabase\TestTDE2.mdf',
|
|
MOVE N'AdventureWorks2022_log'
|
|
TO N'G:\SQLDatabase\TestTDE2_log.ldf',
|
|
NOUNLOAD,
|
|
REPLACE,
|
|
STATS = 5;
|
|
|
|
|
|
EXEC ('ALTER LOGIN [' + @keynametorestore + '] DROP CREDENTIAL [sysadmin_ekm_cred];');
|
|
|
|
EXEC ('ALTER LOGIN [' + @keyname + '] ADD CREDENTIAL [sysadmin_ekm_cred];');
|
|
|
|
EXEC('USE ['+ @db +'];ALTER DATABASE ENCRYPTION KEY
|
|
ENCRYPTION BY SERVER ASYMMETRIC KEY [' + @keyname + '];')
|
|
|
|
|
|
USE [master];
|
|
|
|
EXEC('DROP LOGIN [' + @keynametorestore + '];')
|
|
EXEC('DROP ASYMMETRIC KEY [' + @keynametorestore + '];')
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/* VALIDATE */
|
|
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,
|
|
e.create_date,
|
|
e.regenerate_date,
|
|
e.modify_date,
|
|
e.set_date,
|
|
e.opened_date,
|
|
e.key_algorithm,
|
|
e.key_length,
|
|
e.encryptor_thumbprint,
|
|
e.encryptor_type,
|
|
c.principal_id,
|
|
c.asymmetric_key_id,
|
|
c.pvt_key_encryption_type,
|
|
c.pvt_key_encryption_type_desc,
|
|
c.thumbprint,
|
|
c.algorithm,
|
|
c.algorithm_desc,
|
|
c.key_length,
|
|
c.sid,
|
|
c.string_sid,
|
|
c.public_key,
|
|
c.attested_by,
|
|
c.provider_type,
|
|
c.cryptographic_provider_guid,
|
|
c.cryptographic_provider_algid
|
|
FROM sys.dm_database_encryption_keys AS e
|
|
LEFT JOIN master.sys.asymmetric_keys AS c
|
|
ON e.encryptor_thumbprint = c.thumbprint
|
|
WHERE c.name <> 'tempdb';
|
|
|
|
|
|
|
|
|
|
/* DROP */
|
|
USE [master];
|
|
GO
|
|
|
|
ALTER DATABASE [TestTDE] SET ENCRYPTION OFF;
|
|
GO
|
|
|
|
/* WAIT */
|
|
|
|
USE [TestTDE];
|
|
GO
|
|
|
|
DROP DATABASE ENCRYPTION KEY;
|
|
GO
|
|
|
|
USE [master];
|
|
GO
|
|
|
|
ALTER LOGIN [TDE_20240601] DROP CREDENTIAL [sysadmin_ekm_cred];
|
|
GO
|
|
|
|
DROP LOGIN [TDE_20240601];
|
|
GO
|
|
|
|
DROP ASYMMETRIC KEY [TDE_20240601];
|
|
GO |