TDE DB on Windows 2008 Enterprise

SQL 2008 Enterprise and Windows local policy FIPS compliant is required for TDE.

Change “DB” to your DB name you want to encrypt and of course the password fields:

–*******************************************************************************************************************
–Use this script to enable TDE on the DB database. Ensure you check the backup paths in Step 5 of this script.
–*******************************************************************************************************************

–************* 1-2. Create Key and Cert for TDE on Database**************
USE master;
GO

IF NOT EXISTS
(
SELECT 1 FROM sys.symmetric_keys WHERE name = ‘##MS_DatabaseMasterKey##’
)
BEGIN
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = ‘password’ ;
PRINT ‘Step 1: Master Key was created.’ ;
END
GO

IF NOT EXISTS
(
SELECT 1 FROM sys.certificates WHERE name = ‘DBCert’
)
BEGIN
CREATE CERTIFICATE DBCert
WITH SUBJECT = ‘DB_ENCRYTED’ ;
PRINT ‘Step 2: Certificate was created.’ ;
END
GO

–************* 3. Enabling TDE on Database**************
USE DB
GO

IF NOT EXISTS
(
SELECT 1 FROM sys.dm_database_encryption_keys WHERE database_id =
(
SELECT dbid FROM master..sysprocesses WHERE spid = @@SPID
)
)
BEGIN
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DBCert;
PRINT ‘Step 3. Database Encryption Key was created’ ;
END
GO

–************* 4. Turn ON Encryption**************
IF (
SELECT is_encrypted FROM sys.databases WHERE name = ‘DB’
) = 0
BEGIN
ALTER DATABASE DB
SET ENCRYPTION ON; –SET ENCRYPTION OFF;
PRINT ‘Step 4. Encryption was turned ON.’ ;
END
GO

–************* 5. Back Up a Certificate**************
USE master
GO

DECLARE @output int , @filePathBackup varchar ( 200 ) , @filePathKey varchar ( 200 ) ;

SET @filePathBackup = ‘C:\TEMP\DBCert_backup’ ; –For each server, enter the full file path and name here. The path must
already exist on the server.
SET @filePathKey = ‘C:\TEMP\DB_key’

–find out if backup is already done or not
CREATE TABLE #fileResults ( File_Exists int , File_is_a_Directory int , Parent_Directory_Exists int )
INSERT INTO #fileResults
( File_Exists , File_is_a_Directory , Parent_Directory_Exists )

EXEC master..xp_fileexist @filePathBackup

SET @output =
(
SELECT File_Exists FROM #fileResults
)
IF @output = 0
BEGIN
–backup cerficate and key
BACKUP CERTIFICATE DBCert
TO FILE = ‘C:\TEMP\DBCert_backup’
WITH PRIVATE KEY
( FILE = ‘C:\TEMP\DBCert_key’
, ENCRYPTION BY PASSWORD = ‘password’ )
PRINT ‘Step 5. Backup of cerficate completed.’ ;
END

GO
DROP TABLE #fileResults

–************* 6. Monitor TDE**************
USE master
GO

–SELECT * FROM sys.certificates

—-encryption state 3 = is encrypted
–SELECT * FROM sys.dm_database_encryption_keys WHERE encryption_state=3

–is_encrypted state 1 = the database encryption is turned on
CREATE TABLE #isEncrypted ( name varchar ( 50 ) , is_encrypted varchar ( 50 ) )
INSERT INTO #isEncrypted ( name , is_encrypted )
SELECT
name ,
is_encrypted
FROM sys.databases WHERE name = ‘DB’

DECLARE @is_encrypted int;
SET @is_encrypted =
(
SELECT is_encrypted FROM sys.databases WHERE name = ‘DB’
)
IF @is_encrypted = 1
PRINT ‘******DB database encrypted successfully.******’ ;
ELSE
PRINT ‘DB data is NOT encrypted!’ ;
DROP TABLE #isEncrypted;

GO

Advertisements