Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save dsoprea/ab98c60db83375d139eaf13bcfbdd063 to your computer and use it in GitHub Desktop.
Save dsoprea/ab98c60db83375d139eaf13bcfbdd063 to your computer and use it in GitHub Desktop.
SQL Server: Backup and Restore Credentials, and Test Encryption
-- 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';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment