SQL Server: Backup and Restore Credentials, and Test Encryption

mssql_encryption_backup_restore_encrypt_test.sql

-- To run this script more than once, make sure to delete c:\temp\test.dmk,
-- c:\temp\test.smk, c:\temp\test.crt, and c:\temp\test.crt.key .

DECLARE @ExpectedSignature VARBINARY(8000);
DECLARE @ActualSignature VARBINARY(8000);
DECLARE @Encrypted VARBINARY(8000);
DECLARE @Decrypted VARBINARY(8000);

DECLARE @TestString VARCHAR(100) = 'protected string';

-- ## RESET STATE (required to run more than once).

IF EXISTS(SELECT 1 from sys.symmetric_keys where name = 'TestSymmetricKey')
BEGIN
    DROP SYMMETRIC KEY [TestSymmetricKey]
END

IF EXISTS(select 1 from sys.certificates where name = 'TestCertificate')
BEGIN
    DROP CERTIFICATE [TestCertificate]
END

IF EXISTS(select 1 from sys.symmetric_keys where name like '%DatabaseMasterKey%')
BEGIN
    DROP MASTER KEY  
END

-- ## INITIALIZE NEW SYSTEM

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '27c381e5-27cf-483f-81bf-143845911a5f';

-- NOTE: Operations requiring the key will work automatically if the service 
-- key is used to encrypt the master key. Otherwise, it needs to be explicitly 
-- opened and closed.

OPEN MASTER KEY DECRYPTION BY PASSWORD = '27c381e5-27cf-483f-81bf-143845911a5f';

-- NOTE: Needs the master key to be open. Idempotent.
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
-- ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;

-- NOTE: Idempotent.
CLOSE MASTER KEY;


-- ## ENCRYPTION TEST

-- NOTE: This will still fail if it doesn't exist whether the master-key is 
-- open or not.
-- DROP CERTIFICATE [TestCertificate];

-- Not necessary because we're using an SMK.
-- OPEN MASTER KEY DECRYPTION BY PASSWORD = '27c381e5-27cf-483f-81bf-143845911a5f';

CREATE CERTIFICATE [TestCertificate] AUTHORIZATION [dbo]
    WITH SUBJECT = 'DMK Restore Test certificate';

CREATE SYMMETRIC KEY [TestSymmetricKey]
    AUTHORIZATION [dbo]
    WITH
        KEY_SOURCE = 'b77d32a0-51cd-4ccc-9d4d-6a1f5441278f',
        IDENTITY_VALUE = 'ee6f3b4f-e161-421f-9f4f-e32b56e6b836',
        ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE [TestCertificate];

-- NOTE: Both of these will return NULL if the key isn't opened (and can't be 
-- automatically opened).
SET @ExpectedSignature = SIGNBYCERT(CERT_ID('TestCertificate'), @TestString);

OPEN SYMMETRIC KEY [TestSymmetricKey] DECRYPTION BY CERTIFICATE [TestCertificate];
SET @Encrypted = ENCRYPTBYKEY(KEY_GUID('TestSymmetricKey'), @TestString);
CLOSE SYMMETRIC KEY [TestSymmetricKey];

IF @Encrypted IS NULL
    RAISERROR('Encryption failed.', 16, 0);

-- CLOSE MASTER KEY;  


-- ## BACKUP STATE.

BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\test.smk'
    ENCRYPTION BY PASSWORD = 'e83c658c-f5dd-4c2d-95fe-6b2f142adf98'

BACKUP MASTER KEY TO FILE = 'c:\temp\test.dmk'
    ENCRYPTION BY PASSWORD = '37a7f2a5-029d-42ae-98dc-ce7a3d95ff9a'

BACKUP CERTIFICATE [TestCertificate] TO FILE = 'c:\temp\test.crt'
WITH PRIVATE KEY ( 
    ENCRYPTION BY PASSWORD = '673cae70-a7bc-44ba-9ebb-30e95da7dfc9',
    FILE = 'c:\temp\test.crt.key'
); 

-- ## RESTORE TEST

-- Drop SMK protection.

OPEN MASTER KEY DECRYPTION BY PASSWORD = '27c381e5-27cf-483f-81bf-143845911a5f';

-- NOTE: Idempotent.
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;  

-- NOTE: You can't drop the master key if there are any encrypted certificates 
-- (and this interlocks with the fact that we won't be able to restore a 
-- different one without passing FORCE).

DROP SYMMETRIC KEY [TestSymmetricKey];
DROP CERTIFICATE [TestCertificate];
DROP MASTER KEY;

-- NOTE: We force the restore since there appears to be no way to drop it beforehand.
RESTORE SERVICE MASTER KEY FROM FILE = 'c:\temp\test.smk'   
    DECRYPTION BY PASSWORD = 'e83c658c-f5dd-4c2d-95fe-6b2f142adf98';

-- NOTE: If a master-key already exists, you must open it before restoring over it.
--OPEN MASTER KEY DECRYPTION BY PASSWORD = '27c381e5-27cf-483f-81bf-143845911a5f';

RESTORE MASTER KEY FROM FILE = 'c:\temp\test.dmk'
    DECRYPTION BY PASSWORD = '37a7f2a5-029d-42ae-98dc-ce7a3d95ff9a'
    ENCRYPTION BY PASSWORD = '27c381e5-27cf-483f-81bf-143845911a5f';

-- NOTE: The restore seems to implicitly close the open DMK. Gotta reopen to 
-- add the SMK protection.
OPEN MASTER KEY DECRYPTION BY PASSWORD = '27c381e5-27cf-483f-81bf-143845911a5f';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;  

CREATE CERTIFICATE [TestCertificate] 
FROM FILE = 'c:\temp\test.crt'
WITH PRIVATE KEY ( 
    FILE = 'c:\temp\test.crt.key' ,   
    DECRYPTION BY PASSWORD = '673cae70-a7bc-44ba-9ebb-30e95da7dfc9'
); 

CREATE SYMMETRIC KEY [TestSymmetricKey]
    AUTHORIZATION [dbo]
    WITH 
        KEY_SOURCE = 'b77d32a0-51cd-4ccc-9d4d-6a1f5441278f',
        ALGORITHM = AES_256,
        IDENTITY_VALUE = 'ee6f3b4f-e161-421f-9f4f-e32b56e6b836'
    ENCRYPTION BY CERTIFICATE [TestCertificate];


-- ## VALIDATE

SET @ActualSignature = SIGNBYCERT(CERT_ID('TestCertificate'), @TestString);

IF @ExpectedSignature = @ActualSignature
    SELECT  'OK' 'Signature', NULL 'Expected', NULL 'Actual';
ELSE
    SELECT 'FAIL' 'Signature', @ExpectedSignature 'Expected', @ActualSignature 'Actual';

OPEN SYMMETRIC KEY [TestSymmetricKey] DECRYPTION BY CERTIFICATE [TestCertificate];
SET @Decrypted = DECRYPTBYKEY(@Encrypted);
CLOSE SYMMETRIC KEY [TestSymmetricKey];

IF @Decrypted = @TestString
    SELECT 'OK' 'Encryption', NULL 'Expected', NULL 'Actual', NULL 'Encrypted';
ELSE
    SELECT 'FAIL' 'Encryption', @TestString 'Expected', @Decrypted 'Actual', @Encrypted 'Encrypted';