SQL Server – Transparent Data Encryption

För att höja säkerheten i SQL Server så kan du kryptera din databas med Transparent Data Encryption (TDE). Denna kryptering är precis som namnet antyder transparent för applikationerna. Att aktivera TDE på en databas är relativt enkelt med några få steg:

• Skapa en master database key som skyddas av service key
• Skapa ett certificat som skyddas av master database key
• Ta en backup av certificatet. Detta är mycket viktigt för att kunna återläsa en krypterad databas
• Skapa en database key i databasen som skall krypteras
• Aktivera krypteringen

För att testa TDE så kan du använda koden nedan för att sätta upp kryptering, ta en backup av certifikatet och databasen, ta bort dessa och återläsa den krypterade databasen.

USE MASTER;
GO


IF EXISTS(SELECT * FROM sys.[databases] AS d WHERE name ='EncryptedDatabaseTest')
BEGIN
	DROP DATABASE [EncryptedDatabaseTest] 
END

-- create the master database key
IF NOT EXISTS (select * from sys.symmetric_keys WHERE name ='##MS_DatabaseMasterKey##')
	CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyMasterPassword';

IF EXISTS (SELECT * FROM sys.[certificates] AS c WHERE c.[name]='MyCert1')
	DROP CERTIFICATE MyCert1;

-- Statement 1
CREATE CERTIFICATE MyCert1 WITH SUBJECT='My test';

SELECT * FROM [sys].[certificates] AS c WHERE name ='MyCert1';

-- Backup the certificate!!! Very important, don't loose it!!!--
BACKUP CERTIFICATE MyCert1 
TO FILE ='E:SQLFilesSQL2012CertificatesMyCert1.cer' 
WITH PRIVATE KEY(
	FILE = 'E:SQLFilesSQL2012CertificatesMyCert1.key', 
	ENCRYPTION BY PASSWORD='MyPassword1');
GO

CREATE DATABASE EncryptedDatabaseTest
GO

USE [EncryptedDatabaseTest]
GO

-- Create the database encryption key
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyCert1;

--Turn encryption on!
ALTER DATABASE [EncryptedDatabaseTest] SET ENCRYPTION ON;
GO

--Backup the database
BACKUP DATABASE [EncryptedDatabaseTest] TO  DISK = N'E:SQLFilesSQL2012BackupsEncryptedDatabaseTest.bak' 
WITH NOFORMAT, INIT,  NAME = N'EncryptedDatabaseTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

USE master;
GO

DROP DATABASE [EncryptedDatabaseTest]
DROP CERTIFICATE MyCert1;
GO

PRINT 'Restore certificate'

-- restore the certificate from backup file
CREATE CERTIFICATE MyCert1
      FROM FILE = 'E:SQLFilesSQL2012CertificatesMyCert1.cer'
		WITH PRIVATE KEY (FILE = 'E:SQLFilesSQL2012CertificatesMyCert1.key', DECRYPTION BY PASSWORD='MyPassword1');
GO

PRINT 'Restored certificate'
SELECT * FROM [sys].[certificates] AS c WHERE name ='MyCert1';

-- STATEMENT 6
RESTORE DATABASE [EncryptedDatabaseTest] FROM DISK = N'E:SQLFilesSQL2012BackupsEncryptedDatabaseTest.bak'

--check the encryption--
SELECT db.name, is_encrypted, encryptor_type, encryptor_thumbprint, c.name 
FROM sys.databases db INNER JOIN sys.dm_database_encryption_keys dek
ON dek.database_id = db.database_id
LEFT JOIN sys.certificates c
ON dek.encryptor_thumbprint = c.thumbprint
WHERE db.name = 'EncryptedDatabaseTest';

/Håkan Winther
Twitter: @h_winther