SQL Server Encryption (TDE)

The following is basically a keep safe for myself of useful Encryption related scripts that I use on a daily basis when dealing with MSSQL Servers TDE Encryption.

If it’s any use. Feel free to copy and re-use any parts

5 Steps to encrypt your database

  1. Generate Master Key
  2. Generate Main Certificate
  3. Backup Keys
  4. Generate a key in a database
  5. Switch on encryption

Generate master keys and Certificates

Create a service master key

Create Master Key Encryption By Password = 'MyPassword'

Create a Master Certificate

CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Certificate'

** IMPORTANT**

Backup the Main Certificate. This is required for other servers and DR.. You cannot restore the database without it. Keep these files safe somewhere..

 

Backup Certificates to files

BACKUP CERTIFICATE MyCertificate TO FILE = 'c:\temp\MyCertificateBackup.bck'
WITH PRIVATE KEY (
  FILE = 'c:\Temp\MyPrivateKey.key',
  ENCRYPTION BY PASSWORD = 'MyPassword');
Always backup your certificate files to disk and store safely

Always backup your certificate files to disk and store safely

Generate a Key in a database

First Connect to the database you want to encrypt.

USE TestEncryption
GO

Now create a database encryption Key in the database using the certificate created earlier

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyCertificate

 

Switch on Encryption

To switch on the database encription run the command below

ALTER DATABASE TestEncryption SET ENCRYPTION ON

This will start the encryption engine running.

The database file is not immediately encrypted. The file is slowly encrypted as a background task by SQL server. The database is fully usable while the database is being encrypted and the encryption progress can be monitored using the command:

Select 
    db_name(database_id), 
    CASE 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'
        WHEN 6 THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
        ELSE 'Unknown State...'
        END  from sys.dm_database_encryption_keys

After the flag to enable encryption was changed the status changes to Encryption in progress

DB is not immediately encrypted

DB is not immediately encrypted

20 seconds later in the example database the encryption was completed.

After encryption the DB is marked as encrypted and you are finished

After encryption the DB is marked as encrypted and you are finished

 

 

 

Proof Encryption works

Just having a flag stating the database is encrypted is never enough.. So the following section will demonstrate that the database files are encrypted and that the data cannot be accessed.

 

A test database called “TestEncryption” was created with a single table “dbo.Table_1”

CREATE TABLE [dbo].[Table_1](

[col1] [int] NULL,
[col2] [nvarchar](20) NULL,
[col3] [nvarchar](40) NULL,
[col4] [nvarchar](80) NULL,
[col5] [nvarchar](120) NULL,
[col6] [nvarchar](240) NULL

)

The table has been populated with 3,000,000 rows containing padded incremental numbers

Here is an excerpt

Data as viewed in Management Studio

Data as viewed in Management Studio

The highlighted rows can easily be found when opening the raw SQL server data file in a hexadecimal editor as shown below.

Data viewed in hex editor before Encryption

Data viewed in hex editor before Encryption

 

After the encryption the same point in the file looks like this.

Data viewed in hex editor after encryption

Data viewed in hex editor after encryption

 

 

 

Useful statements

Show Encryption status and Certificate used

use master
GO

select @@servername, db_name(d.database_id) ,
CASE COALESCE(a.encryption_state, 99999)
        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'
        WHEN 6 THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
        WHEN 99999 THEN 'Not Encrypted'
        ELSE 'Unknown State...'
        END ,
        c.name AS 'CertificateName' 
from sys.databases d
left outer join sys.dm_database_encryption_keys a ON d.database_id = a.database_id
LEFT OUTER join sys.certificates c  ON a.encryptor_thumbprint = c.thumbprint
order by c.name  desc

CheckEncryptionResults

Show Encryption – Multi-server Edition

A more advanced version of the above that’s great if you are running against multiple servers at a time for audit purposes

This statement differs in that if checks to ensure the server supports encryption before checking but still includes server in results.

DECLARE @EngineVer int;
DECLARE @SQLVer int;
DECLARE @SQLString nvarchar(1024);

-- ensure teht server is newer 
SELECT @SQLVer = substring(cast(SERVERPROPERTY('ProductVersion') as varchar(126)),1,(charindex('.',cast(SERVERPROPERTY('ProductVersion') as varchar(126)))-1));
SELECT @EngineVer = convert(INT,SERVERPROPERTY ('EngineEdition'));


if @EngineVer = 3 and @SQLVer > 9
begin
SET @SQLString = '    
    select cast(@@servername as varchar(126)) as ''ServerName'', cast(db_name(d.database_id) as varchar(128)) as ''DatabaseName'', 
    CASE COALESCE(a.encryption_state, 99999)     
        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''         
        WHEN 6 THEN ''Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)''
        WHEN 99999 THEN ''Not Encrypted'' 
    ELSE ''Unknown State...''    
    END as ''EncryptionStatus'',  
    cast(c.name as varchar(128)) AS ''CertificateName'' 
    from 
        sys.databases d left outer join 
        sys.dm_database_encryption_keys a ON d.database_id = a.database_id    
        LEFT OUTER join sys.certificates c  ON a.encryptor_thumbprint = c.thumbprint'
    end
else
begin
SET @SQLString ='select cast(@@servername as varchar(128))as ''ServerName'',name as ''DatabaseName'',''Instance Does not support Encryption''as ''EncryptionStatus'',cast('''' as varchar(128)) AS ''CertificateName'' from sys.databases '
end;
EXECUTE sp_executesql @SQLString;

 

 

 

 

 

 

 

 

 

This entry was posted in DBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *