SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Transparent Database Encryption on all User Databases

By Prem Kumar,

Applying Transparent database encryption is one of the important tasks which we need to do across the enterprise level databases.

Below script will apply the encryption on all user databases in a particular instance. The certificate needs to be created on all secondary databases in an Always-On environment.

USE [master]

--Create Master Key

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Azdb_pRd@2017';

--Create Certificate  
 
CREATE CERTIFICATE AZ_TDE_PRD
WITH SUBJECT='AZ_TDE_ON_PRD';
GO

--Create Database Encryption Key on each user databases

DECLARE @sql varchar(2000),
        @dbname varchar(64)
 
DECLARE apply_tde CURSOR FOR
   SELECT d.name
   FROM  sys.databases d
   WHERE d.database_id >4

   OPEN apply_tde
       FETCH NEXT FROM apply_tde INTO @dbname
       WHILE @@FETCH_STATUS <> -1
       BEGIN

            SELECT @sql ='USE [@dbname]
            CREATE DATABASE ENCRYPTION KEY
            WITH ALGORITHM = AES_128
            ENCRYPTION BY SERVER CERTIFICATE AZ_TDE_PRD'

            SELECT @sql = REPLACE(@sql,'@dbname',@dbname)
            PRINT  'Encryption Started --'+@sql
            EXEC  (@sql)
        
            SELECT @sql = 'USE [master] ALTER DATABASE [@dbname] SET ENCRYPTION ON'
            SELECT @sql = REPLACE(@sql,'@dbname',@dbname)
            PRINT  'Encryption Finished --'+@sql
            EXEC  (@sql)

        FETCH NEXT FROM apply_tde into @dbname
        END
   CLOSE apply_tde
   DEALLOCATE apply_tde


BACKUP CERTIFICATE AZ_TDE_PRD
TO FILE = 'C:\temp\AZ_TDE_PRD'
WITH PRIVATE KEY (file='C:\temp\AZ_TDE_PRD_PVT',
ENCRYPTION BY PASSWORD='Cer#Azdb_pRd@2017')

GO

SELECT
     DB_NAME(database_id) AS 'Database Name'
    ,create_date AS 'Create Date'
    ,set_date AS 'Set Date'
    ,(CASE
        WHEN Encryption_State='0' THEN 'No Database Encryption'
        WHEN Encryption_State='1' THEN 'Unencrypted'
        WHEN Encryption_State='2' THEN 'Encryption In Progress'
        WHEN Encryption_State='3' THEN 'Encrypted'
        WHEN Encryption_State='4' THEN 'Key Change In Progress'
        WHEN Encryption_State='5' THEN 'Decryption In Progress'
        WHEN Encryption_State='6' THEN 'Protection Change In Progress'
      END) AS 'Encryption State'
    ,key_algorithm AS 'Algorithm Used'
    ,key_length AS 'Key Length'
    ,encryptor_thumbprint AS 'Encryptor Thumbprint'
    ,percent_complete AS 'Percent Complete'
    ,encryptor_type AS 'Encryptor Type'
FROM sys.dm_database_encryption_keys
GO


After executing the above script, you need to copy the certificate to the secondary server in an always-on environment and create the certificate in a secondary server.  

This is also applicable if you are implementing the transparent database encryption in production and restoring it in test/development environment.

CREATE CERTIFICATE AZ_TDE_PRD
FROM FILE='C:\temp\AZ_TDE_PRD'
WITH PRIVATE KEY (
FILE = 'C:\temp\AZ_TDE_PRD_PVT',
DECRYPTION BY PASSWORD='Cer#Azdb_pRd@2017')

Please check my blog https://premkumardr.blogspot.com.au for similar posts.

Total article views: 340 | Views in the last 30 days: 19
 
Related Articles
FORUM

Encrypt the whole database

Encrypt the whole database

FORUM

Encrypting the entire database.

Encrypting the entire database.

FORUM

Database Encryption

Database Encryption tools for SQL and Oracle

FORUM

Alter Database DBNAME Set Recovery Full. Gives error.

Alter Database DBNAME Set Recovery Full. Gives error.

FORUM

query to get dbname,totalsize,reservedsize of all databases in sql 2000

query to get dbname,totalsize,reservedsize of all databases in sql 2000

Tags
database encryption    
encryption    
tde    
 
Contribute