Script – Encryption

Encryption related Scripts

Create a service master key

Create Master Key Encryption By Password = 'MyPassword'

Create a Master Certificate

CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Certificate'

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');

Restore Encryption Key

CREATE CERTIFICATE MyCertificate
    FROM FILE = 'c:\temp\MyCertificateBackup.bck' 
    WITH PRIVATE KEY (FILE = 'c:\Temp\MyPrivateKey.key', 
    DECRYPTION BY PASSWORD = 'MyPassword');

Generate a Key in a database

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

Switch on Encryption

ALTER DATABASE TestEncryption SET ENCRYPTION ON

Reverse

ALTER DATABASE TestEncryption SET ENCRYPTION OFF

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 Scripts. Bookmark the permalink.

Leave a Reply

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