Encryption Hierarchy Administration – a T-SQL Template

This is a SQL Server template to standardize and track backup and  restore of SQL Server 2005/2008 Encryption Hierarchy (EH) Master Keys and Certificates. Unlike most templates, this one creates a complete utility with several interdependent database objects – including the database if it does not already exist. Any existing user database can be used as long as the consequences of executing a script from this template are understood and acceptable.

January 25, 2012 Update – A nicely hardened version of the Encryption Hierarchy Administrator is now available. I would still strongly recommend that you become familiar with the technologies demonstrated in the script posted here if you do not already administer an encryption hierarchy. This script demonstrates the fundamentals required for sound EH administration. The new version adds extensive cryptographic support that will be documented through a few blog posts.

https://github.com/bwunder/EHAdmin.

and now back to our regularly scheduled program…

keyback Administrator.sql
  1. — Encryption Hierarchy Administration Template
  2. — WARNING! script changes master database and creates a database if it does not already exist 
  3. — run in SSMS SQLCMD mode or from a sqlcmd command line
  4. — best practice is to use SSMS and NEVER save the script once the secrets have been entered.
  5. — PASSPHRASE cannot contain any of these symbols or substrings # , . ; : _  ” ‘
  6. — or the words GO DROP CREATE ALTER SELECT INSERT UPDATE DELETE GRANT DBO EXEC USE (regardless of case)
  7. :setvar DB_NAME                             “<Db to create or use if exists,SYSNAME,keyback>”                                   
  8. :setvar SMK_BACKUP_PHRASE                   “<Secret for Service Master Key backup file encryption,PASSPHRASE – NVARCHAR(128),>”
  9. — if master already has a DMK you must provide the existing encryption passphrase – don’t make one up – it will corrupt keyback
  10. :setvar master_DMK_ENCRYPTION_PHRASE        “<Secret for master Database Master Key encryption,PASSPHRASE – NVARCHAR(128),>”    
  11. :setvar master_DMK_BACKUP_PHRASE            “<Secret for master Database Master Key backup file,PASSPHRASE – NVARCHAR(128),>”   
  12. :setvar keyback_DMK_ENCRYPTION_PHRASE       “<Secret for keyback Database Master Key encryption,PASSPHRASE – NVARCHAR(128),>”   
  13. :setvar keyback_DMK_BACKUP_PHRASE           “<Secret for keyback Database Master Key backup file,PASSPHRASE – NVARCHAR(128),>”  
  14. :setvar TDE_CERTIFICATE_NAME                “<Name of cert in master for keyback db TDE Encryption Key ,SYSNAME,TDECertificate>”
  15. :setvar TDE_CERTIFICATE_BACKUP_PHRASE       “<Secret for master db TDE Cert backup file encryption,PASSPHRASE – NVARCHAR(128),>”
  16. :setvar VALUE_CERTIFICATE_NAME              “<Name of cert in keyback db used to encrypt column values,SYSNAME,ValueCertificate>”
  17. :setvar VALUE_CERTIFICATE_ENCRYPTION_PHRASE “<Secret for keyback db value certificate encryption,PASSPHRASE – NVARCHAR(128),>”  
  18. :setvar VALUE_CERTIFICATE_BACKUP_PHRASE     “<Secret for keyback db value certificate backup file,PASSPHRASE – NVARCHAR(128),>” 
  19. http://blogs.msdn.com/b/ace_team/archive/2007/09/07/aes-vs-3des-block-ciphers.aspx : use AES
  20. :setvar DEK_ALGORITHM                       “<Database Encryption Key Algorithm,AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY,AES_256>”
  21. :setvar COLUMN_ENCRYPTION_ALGORITHM         “<Symmetric Key Algorithm,AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY | Other (most already broken), AES_256>”  
  22. :setvar DB_SYMMETRIC_KEY                    “<Symmetric key used for data value encryption,SYSNAME,ValueKey>”                   
  23. :setvar ROLE_NAME                           “<Role -membership required to access NameValues,SYSNAME,keybackAdmin>”             
  24. :setvar MASTER_KEY_BACKUP_EXT               “<file extension for key backups,FILE_EXTENSION,.keybak>”                           
  25. :setvar PUBLIC_KEY_BACKUP_EXT               “<file extension for certificate backups,FILE_EXTENTION,.cerbak>”                   
  26. :setvar PRIVATE_KEY_BACKUP_EXT              “<file extension for certificate private key backups,FILE_EXT,.prvbak>”             
  27. :setvar SPARSE                              “<SPARSE XML error columns if 2008 otherwise blank,SPARSE | blank,SPARSE>”          
  28. RAISERROR(‘Protect all Encryption Hierarchy backup files and maintain a copy in a secure offsite location.’,0,0);
  29. GO
  30. SET NOCOUNT ON;
  31. USE [master];
  32. GO
  33. IF DB_ID(‘$(DB_NAME)’) IS NULL
  34. CREATE DATABASE [$(DB_NAME)];
  35. GO
  36. USE $(DB_NAME);
  37. GO
  38. IF DB_NAME() <> ‘$(DB_NAME)’
  39.   RAISERROR(‘Database $(DB_NAME) not found. Script aborted.’,20,1) WITH LOG;
  40. GO
  41. ALTER DATABASE [$(DB_NAME)] SET TRUSTWORTHY OFF;    
  42. GO
  43. ALTER DATABASE [$(DB_NAME)] SET DB_CHAINING OFF;
  44. GO
  45. ALTER DATABASE [$(DB_NAME)] SET RECOVERY FULL;  
  46. GO
  47. ALTER DATABASE [$(DB_NAME)] SET READ_COMMITTED_SNAPSHOT ON;
  48. GO
  49. — db_owner, dbcreator, sysadmin.
  50. ALTER DATABASE [$(DB_NAME)] SET RESTRICTED_USER;
  51. GO
  52. USE [master];
  53. — encryption hierarchy backups
  54. EXEC sp_addmessage 2147483631,  1, ‘keyback info – database %s certificate %s %s complete.’, ‘us_english’,‘FALSE’ ,‘replace’
  55. EXEC sp_addmessage 2147483632,  1, ‘keyback error – database %s certificate %s %s failed with return_code %d.’, ‘us_english’,‘FALSE’ ,‘replace’
  56. EXEC sp_addmessage 2147483633,  1, ‘keyback info – database %s Master Key %s complete.’, ‘us_english’,‘FALSE’ ,‘replace’
  57. EXEC sp_addmessage 2147483634,  1, ‘keyback error – database %s Master Key %s failed with return_code %d.’, ‘us_english’,‘FALSE’ ,‘replace’
  58. EXEC sp_addmessage 2147483635,  1, ‘keyback info – Service Master Key backup complete.’, ‘us_english’,‘FALSE’ ,‘replace’
  59. EXEC sp_addmessage 2147483636,  1, ‘keyback error – Service Master Key backup failed with return_code %d.’, ‘us_english’,‘FALSE’ ,‘replace’
  60. — encrypted name value secrets
  61. EXEC sp_addmessage 2147483641,  1, ‘namevalue info – %s complete.’, ‘us_english’,‘FALSE’ ,‘replace’
  62. EXEC sp_addmessage 2147483642, 16, ‘namevalue error – %s failed with @return_code = %d.’, ‘us_english’,‘FALSE’ ,‘replace’
  63. EXEC sp_addmessage 2147483643, 16, ‘namevalue error – Unable to verify save of secret “%s”.’, ‘us_english’,‘FALSE’ ,‘replace’
  64. EXEC sp_addmessage 2147483644, 16, ‘namevalue error – Invalid “%s”.’, ‘us_english’,‘FALSE’ ,‘replace’
  65. EXEC sp_addmessage 2147483645,  1, ‘namevalue info – the %s is valid.’, ‘us_english’,‘FALSE’ ,‘replace’
  66. GO
  67. — Certificate in master for TDE is encrypted by master Database Master Key
  68. IF NOT EXISTS (SELECT * FROM [sys].[symmetric_keys] WHERE [symmetric_key_id] = 101)
  69. CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘$(master_DMK_ENCRYPTION_PHRASE)’;
  70. GO
  71. —  private key of cert is autogenerated and encrypted by DMK
  72. — if no private key specified and no Master Key exists this create will fail
  73. IF NOT EXISTS (SELECT * FROM sys.certificates WHERE [name] = ‘$(TDE_CERTIFICATE_NAME)’)
  74.   CREATE CERTIFICATE $(TDE_CERTIFICATE_NAME) WITH SUBJECT = ‘$(DB_NAME)_DatabaseEncryptionKey’;
  75. GO
  76. USE $(DB_NAME);
  77. — move dbo to sa so no conflicts when current user added to role
  78. — sa should also be disabled, and could also be renamed
  79. — SQL authentication should also be disabled.
  80. IF (SELECT owner_sid FROM sys.databases where name = DB_NAME()) <> 0x01
  81. EXEC sp_changedbowner ‘sa’;
  82. GO
  83. IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ORIGINAL_LOGIN())
  84. BEGIN
  85.   DECLARE @CreateUserDDL NVARCHAR(MAX);
  86.   SET @CreateUserDDL = ‘CREATE USER [‘ + ORIGINAL_LOGIN() + ‘] FROM LOGIN [‘ + ORIGINAL_LOGIN() + ‘];’
  87.   EXEC sp_executesql @CreateUserDDL;
  88. END
  89. GO
  90. IF NOT EXISTS (SELECT *
  91.                FROM sys.database_principals
  92.                WHERE name = ‘$(ROLE_NAME)’)  
  93. BEGIN
  94.   DECLARE @CreateRoleDDL NVARCHAR(MAX);
  95.   SET @CreateRoleDDL = ‘CREATE ROLE $(ROLE_NAME) AUTHORIZATION [‘ + ORIGINAL_LOGIN() + ‘];’
  96.   EXEC sp_executesql @CreateRoleDDL;
  97. END
  98. GO
  99. IF IS_MEMBER(‘$(ROLE_NAME)’) <> 1
  100. BEGIN
  101.   DECLARE @AddToRoleDDL NVARCHAR(MAX);
  102.   SET @AddToRoleDDL = ‘EXEC sp_addrolemember ”$(ROLE_NAME)”,[‘ + ORIGINAL_LOGIN() + ‘]’;
  103.   EXEC sp_executesql @AddToRoleDDL;
  104. END  
  105. GO
  106. — sys.dm_database_encryption_keys unknown to 2005 and lesser 2008 SKUs – if true will skip this
  107. — has to spexecuted to avoid failed parse in 2005.
  108. IF ISNULL(PARSENAME ( CONVERT(NVARCHAR(128), SERVERPROPERTY(‘ProductVersion’)) , 4 ), 0) > 9
  109. AND SERVERPROPERTY(‘Edition’) IN (‘Developer Edition’,‘Enterprise Edition’,‘Enterprise Evaluation Edition’)
  110. BEGIN
  111.   IF NOT EXISTS (SELECT * FROM sys.dm_database_encryption_keys
  112.                  WHERE [database_id] = DB_ID()
  113.                  AND DB_NAME() = ‘$(DB_NAME)’)
  114.      EXEC sp_executesql N’CREATE DATABASE ENCRYPTION KEY
  115.                           WITH ALGORITHM = $(DEK_ALGORITHM)
  116.                           ENCRYPTION BY SERVER CERTIFICATE [$(TDE_CERTIFICATE_NAME)];
  117.                           ALTER DATABASE $(DB_NAME)
  118.                           SET ENCRYPTION ON;’;
  119. END
  120. GO
  121. IF NOT EXISTS (SELECT *
  122.                FROM [sys].[symmetric_keys]
  123.                WHERE [symmetric_key_id] = 101)
  124.   CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘$(keyback_DMK_ENCRYPTION_PHRASE)’;
  125. GO
  126. — certs need not have a password, most probably should not. here password is better because
  127. — malicious hackers are prevented from accessing the secrets in an elevation of authority attack 
  128. — only those able to view the secret can open the key required to view the secrets 
  129. — the password will be encrypted in the necessary stored procedures so will be required
  130. — only when opening the symmetric in ad hoc query
  131. IF NOT EXISTS (SELECT * FROM [sys].[certificates] WHERE [name] = ‘$(VALUE_CERTIFICATE_NAME)’)
  132. CREATE CERTIFICATE [$(VALUE_CERTIFICATE_NAME)]
  133.     ENCRYPTION BY PASSWORD = ‘$(VALUE_CERTIFICATE_ENCRYPTION_PHRASE)’
  134.     WITH SUBJECT = ‘NameValue Column Encryption’;
  135. GO
  136. IF NOT EXISTS (SELECT *
  137.                FROM [sys].[symmetric_keys]
  138.                WHERE [name] = ‘$(DB_SYMMETRIC_KEY)’)
  139.     CREATE SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)]
  140.     WITH ALGORITHM = $(COLUMN_ENCRYPTION_ALGORITHM)
  141.     ENCRYPTION BY CERTIFICATE [$(VALUE_CERTIFICATE_NAME)];
  142. GO
  143. IF OBJECT_ID(‘dbo.NameValues’) IS NULL
  144.     CREATE TABLE [dbo].[NameValues]
  145.         ( [Name] [NVARCHAR](128) NOT NULL
  146.         , [Version] INT NOT NULL
  147.           CONSTRAINT dft_NameValues__Version
  148.           DEFAULT (1)
  149.         , [Value] [VARBINARY] (256) NOT NULL
  150.         , CreateDT [DATETIME] NOT NULL
  151.           CONSTRAINT dft_NameValues__CreateDT
  152.           DEFAULT (CURRENT_TIMESTAMP)
  153.         , CreateUser [NVARCHAR](128) NOT NULL
  154.           CONSTRAINT dft_NameValues__CreateUser
  155.           DEFAULT (ORIGINAL_LOGIN())  
  156.         , CONSTRAINT pk_NameValues__Name__Version
  157.           PRIMARY KEY ([Name], [Version]));
  158. GO
  159. IF OBJECT_ID(‘dbo.itrg_NameValues’) IS NOT NULL
  160. DROP TRIGGER dbo.itrg_NameValues
  161. GO
  162. CREATE TRIGGER dbo.itrg_NameValues
  163. ON  dbo.NameValues
  164. WITH ENCRYPTION
  165. INSTEAD OF UPDATE, DELETE
  166.   AS
  167.   BEGIN
  168.    SET NOCOUNT ON;
  169.    IF EXISTS (SELECT * FROM inserted)
  170.     INSERT dbo.NameValueHistory
  171.      ( Name
  172.      , Version
  173.      , Action
  174.      , Status)
  175.     SELECT d.Name
  176.          , d.Version
  177.          , ‘UPDATE’
  178.          , ‘NO-OP’  
  179.     FROM deleted d;
  180.    ELSE
  181.     INSERT dbo.NameValueHistory
  182.      ( Name
  183.      , Version
  184.      , Action
  185.      , Status)
  186.     SELECT d.Name
  187.          , d.Version
  188.          , ‘DELETE’
  189.          , ‘NO-OP’  
  190.     FROM deleted d;
  191.   END
  192. GO
  193. IF OBJECT_ID(‘dbo.NameValueHistory’) IS NULL
  194.     CREATE TABLE [dbo].[NameValueHistory]
  195.         ( [Id] INT IDENTITY(1,1) NOT NULL
  196.         , [Name] [NVARCHAR](128) NOT NULL — implicit FK
  197.         , [Version] INT NULL — if add fails there may be no version to insert
  198.         , [Action] [VARCHAR] (30) NOT NULL
  199.         , [Status] [VARCHAR] (30) NOT NULL
  200.     CONSTRAINT [ck_NameValueHistory__Status]
  201.     CHECK (Status IN (‘Complete’, ‘Error’, ‘NO-OP’, ‘Valid’, ‘Invalid’))
  202.         , [ErrorData] [XML] $(SPARSE) NULL
  203.         , [CreateDT] [DATETIME] NOT NULL
  204.           CONSTRAINT [dft_NameValueHistory__CreateDT]
  205.           DEFAULT (CURRENT_TIMESTAMP)
  206.         , [CreateUser] [NVARCHAR](128)
  207.           CONSTRAINT [dft_NameValueHistory__CreateUser]
  208.           DEFAULT (ORIGINAL_LOGIN())  
  209.         , CONSTRAINT [pk_NameValueHistory__Id]
  210.           PRIMARY KEY ([Id]));
  211. GO
  212. IF OBJECT_ID(‘dbo.itrg_NameValueHistory’) IS NOT NULL
  213. DROP TRIGGER dbo.itrg_NameValueHistory
  214. GO
  215. CREATE TRIGGER dbo.itrg_NameValueHistory
  216. ON dbo.NameValueHistory
  217. WITH ENCRYPTION
  218. INSTEAD OF UPDATE, DELETE
  219.   AS
  220.   BEGIN
  221.    SET NOCOUNT ON;
  222.    IF EXISTS (SELECT * FROM inserted)
  223.     INSERT dbo.NameValueHistory
  224.      ( Name
  225.      , Version
  226.      , Action
  227.      , Status )
  228.     SELECT d.Name
  229.          , d.Version
  230.          , ‘UPDATE of Id ‘ + CAST(Id as NVARCHAR(10))
  231.          , ‘NO-OP’  
  232.     FROM deleted d
  233.    ELSE
  234.     INSERT dbo.NameValueHistory
  235.      ( Name
  236.      , Version
  237.      , Action
  238.      , Status )
  239.     SELECT d.Name
  240.          , d.Version
  241.          , ‘DELETE of Id ‘ + CAST(Id as NVARCHAR(10))
  242.          , ‘NO-OP’  
  243.     FROM deleted d
  244.   END
  245. GO
  246. IF OBJECT_ID(‘dbo.MasterKeyBackupHistory’) IS NULL
  247.     CREATE TABLE [dbo].[MasterKeyBackupHistory]
  248.         ( [Id] INT IDENTITY(1,1) NOT NULL
  249.         , [ServerName] [NVARCHAR](128) NOT NULL
  250.           CONSTRAINT [dft_MasterKeyBackupHistory__ServerName]
  251.           DEFAULT (@@SERVERNAME)
  252.         , [DbName] [NVARCHAR](128) NULL
  253.         , [KeyName] [NVARCHAR] (128) NOT NULL
  254.         , [BackupName] [NVARCHAR](128) NOT NULL
  255.         , [BackupPath] [NVARCHAR](1024) NOT NULL
  256.         , [Action] [NVARCHAR] (128) NOT NULL
  257.         , [Status] [NVARCHAR] (30) NOT NULL
  258.     CONSTRAINT [ck_MasterKeyBackupHistory__Status]
  259.     CHECK (Status IN (‘Complete’, ‘Error’))
  260.         , [ErrorData] [XML] $(SPARSE) NULL
  261.         , [CreateDT] [DATETIME] NOT NULL
  262.           CONSTRAINT [dft_MasterKeyBackupHistory__CreateDT]
  263.           DEFAULT (CURRENT_TIMESTAMP)
  264.         , [CreateUser] [NVARCHAR](128)
  265.           CONSTRAINT [dft_MasterKeyBackupHistory__CreateUser]
  266.           DEFAULT (ORIGINAL_LOGIN())  
  267.         , CONSTRAINT [pk_MasterKeyBackupHistory__Id]
  268.           PRIMARY KEY ([Id]));
  269. GO
  270. IF OBJECT_ID(‘dbo.CertificateBackupHistory’) IS NULL
  271. BEGIN
  272.      CREATE TABLE [dbo].[CertificateBackupHistory]
  273.         ( [Id] INT IDENTITY(1,1) NOT NULL
  274.          , [ServerName] [NVARCHAR](128) NOT NULL
  275.            CONSTRAINT [dft_CertificateBackupHistory__ServerName]
  276.            DEFAULT (@@SERVERNAME)
  277.          , [DbName] [NVARCHAR](128) NOT NULL
  278.          , [CertificateName] [NVARCHAR] (128) NOT NULL
  279.          , [BackupName] [NVARCHAR](128) NOT NULL
  280.          , [BackupPath] [NVARCHAR](1024) NOT NULL
  281.          , [Action] [VARCHAR] (30) NOT NULL
  282.          , [Status] [VARCHAR] (30) NOT NULL
  283.      CONSTRAINT [ck_CertificateBackupHistory__Status]
  284.      CHECK (Status IN (‘Complete’, ‘Error’))
  285.    , [Thumbprint] [VARBINARY] (32) NOT NULL
  286.    , [PrivateKeyEncryptionType] [CHAR] (2) NOT NULL
  287.      CONSTRAINT [ck_CertificateBackupHistory__PrivateKeyEncryptionType]
  288.      CHECK (PrivateKeyEncryptionType IN (‘NA’, ‘MK’, ‘SK’, ‘PW’, ))
  289.          , [ErrorData] [XML] $(SPARSE) NULL
  290.          , [CreateDT] [DATETIME] NOT NULL
  291.            CONSTRAINT [dft_CertificateBackupHistory__CreateDT]
  292.            DEFAULT (CURRENT_TIMESTAMP)
  293.          , [CreateUser] [NVARCHAR](128) NOT NULL
  294.            CONSTRAINT [dft_CertificateBackupHistory__CreateUser]
  295.            DEFAULT (ORIGINAL_LOGIN())   
  296.          , CONSTRAINT [pk_CertificateBackupHistory__Id]
  297.            PRIMARY KEY ([Id]));
  298.   CREATE NONCLUSTERED INDEX ixn_CertificateBackupHistory__ServerName__DbName__CertificateName
  299.   ON dbo.CertificateBackupHistory(ServerName, DbName, CertificateName) INCLUDE (Action, Status);
  300.   CREATE NONCLUSTERED INDEX ixn_CertificateBackupHistory__Thumbprint
  301.   ON dbo.CertificateBackupHistory(Thumbprint) INCLUDE (Action, Status);
  302. END
  303. GO
  304. IF OBJECT_ID (N’dbo.CheckFile’) IS NOT NULL
  305.    DROP FUNCTION dbo.CheckFile
  306. GO
  307. /******************************************************************************
  308. **    Auth: Bill Wunder
  309. **    Desc: make sure nothing flakey about a file name
  310. *******************************************************************************/
  311. CREATE FUNCTION dbo.CheckFile
  312. ( @Name NVARCHAR(128) )
  313. RETURNS BIT
  314. WITH EXECUTE AS CALLER, ENCRYPTION
  315. AS
  316. BEGIN
  317.   DECLARE @Result BIT;
  318.   SET @Name = UPPER(@Name);
  319.   — SQL Injection prevention
  320.   IF ( PATINDEX(‘%[#,.;:””’, @Name)
  321.      + PATINDEX(‘%–%’, @Name)
  322.      + PATINDEX(‘%*/%’, @Name)
  323.      + PATINDEX(‘%/*%’, @Name)
  324.      + PATINDEX(‘%DROP%’, @Name)
  325.      + PATINDEX(‘%CREATE%’, @Name)
  326.      + PATINDEX(‘%SELECT%’, @Name)
  327.      + PATINDEX(‘%INSERT%’, @Name)
  328.      + PATINDEX(‘%UPDATE%’, @Name)
  329.      + PATINDEX(‘%DELETE%’, @Name)
  330.      + PATINDEX(‘%GRANT%’, @Name)
  331.      + PATINDEX(‘%ALTER%’, @Name)
  332.      + PATINDEX(‘%AUX%’, @Name)
  333.      + PATINDEX(‘%CLOCK$%’, @Name)
  334.      + PATINDEX(‘%COM[1-8]%’, @Name)
  335.      + PATINDEX(‘%CON%’, @Name)
  336.      + PATINDEX(‘%LPT[1-8]%’, @Name)
  337.      + PATINDEX(‘%NUL%’, @Name)
  338.      + PATINDEX(‘%PRN%’, @Name) ) = 0
  339.    SET @Result = 1;
  340.   ELSE
  341.    SET @Result = 0;
  342.   RETURN (@Result);
  343. END
  344. GO
  345. IF OBJECT_ID (N’dbo.CheckPhrase’) IS NOT NULL
  346.    DROP FUNCTION dbo.CheckPhrase
  347. GO
  348. /******************************************************************************
  349. **    Auth: Bill Wunder
  350. **    Desc: see if the unicode Password/Passphrase meets the policy
  351. *******************************************************************************/
  352. CREATE FUNCTION dbo.CheckPhrase
  353. ( @Phrase NVARCHAR(128) )
  354. RETURNS BIT
  355. WITH EXECUTE AS CALLER, ENCRYPTION
  356. AS
  357. BEGIN
  358. DECLARE @Result BIT;
  359. — dft password policy as described in 2008R2 BOL + SQL Injection prevention
  360. — a password can be generated as: SELECT CAST(newid() AS VARCHAR(128));
  361. — sp_ and xp_ are covered by the match on underscore
  362. — sp_ and xp_ are included by the match on underscore
  363. IF LEN(@Phrase) > 7                                                       — at leaset 8 characters
  364. AND PATINDEX(‘%[#,.;:__””]%’, @Phrase) = 0                               — none of these symbols (added _ ‘ “)
  365. AND ( CASE WHEN PATINDEX(‘%[A-Z]%’, @Phrase) > 0 THEN 1 ELSE 0 END        — at least 3 of 4
  366.      + CASE WHEN PATINDEX(‘%[a-z]%’, @Phrase) > 0 THEN 1 ELSE 0 END        — uppercase, lowercase 
  367.      + CASE WHEN PATINDEX(‘%[0-9]%’, @Phrase) > 0 THEN 1 ELSE 0 END        — numeric, symbol
  368.      + CASE WHEN PATINDEX(‘%[^A-Z,^a-z,^0-9]%’, @Phrase) > 0 THEN 1 ELSE 0 END) > 2
  369.   BEGIN
  370.    SET @Phrase = UPPER(@Phrase);
  371.    IF ( PATINDEX(‘%DROP%’, @Phrase)
  372.       + PATINDEX(‘%CREATE%’, @Phrase)
  373.       + PATINDEX(‘%SELECT%’, @Phrase)
  374.       + PATINDEX(‘%INSERT%’, @Phrase)
  375.       + PATINDEX(‘%UPDATE%’, @Phrase)
  376.       + PATINDEX(‘%DELETE%’, @Phrase)
  377.       + PATINDEX(‘%GRANT%’, @Phrase)
  378.       + PATINDEX(‘%ALTER%’, @Phrase)
  379.       + PATINDEX(‘%–%’, @Phrase)
  380.       + PATINDEX(‘%*/%’, @Phrase)
  381.       + PATINDEX(‘%/*%’, @Phrase) ) = 0
  382.    SET @Result = 1;
  383.   ELSE
  384.    SET @Result = 0;   
  385.   END   
  386. RETURN (@Result);
  387. END
  388. GO
  389. IF OBJECT_ID (N’dbo.IsAuthorized’) IS NOT NULL
  390.    DROP FUNCTION dbo.IsAuthorized
  391. GO
  392. /******************************************************************************
  393. **    Auth: Bill Wunder
  394. **    Desc: deterine if the user is authorize to use the system
  395. *******************************************************************************/
  396. CREATE FUNCTION dbo.IsAuthorized()
  397. RETURNS BIT
  398. WITH EXECUTE AS CALLER, ENCRYPTION
  399. AS
  400. BEGIN
  401.   DECLARE @Result BIT;
  402.   — sysadmins will always be dbo in the database
  403.   IF USER_NAME() = ‘dbo’
  404.   AND ORIGINAL_LOGIN() = SUSER_SNAME() — SYSTEM_USER
  405.   AND IS_SRVROLEMEMBER(‘sysadmin’) = 1
  406.   AND EXISTS (SELECT *
  407.               FROM sys.database_role_members
  408.               WHERE role_principal_id = USER_ID(‘$(ROLE_NAME)’)
  409.               AND USER_NAME(member_principal_id) = SUSER_SNAME() )  
  410.    SET @Result = 1;
  411.   ELSE
  412.    SET @Result = 0;  
  413.   RETURN (@Result);
  414. END
  415. GO
  416. IF OBJECT_ID (N’dbo.NewBackupPath’) IS NOT NULL
  417.    DROP FUNCTION dbo.NewBackupPath
  418. GO
  419. /******************************************************************************
  420. **    Auth: Bill Wunder
  421. **    Desc: get the path where the backups will be stored
  422. **    ASSERT: the backup is stored in same folder as the .mdf of the database
  423. **            no ACL changes are required, if the local backup copy is kept here
  424. **            the permissions could be locked down further  
  425. *******************************************************************************/
  426. CREATE FUNCTION dbo.NewBackupPath (@DbName NVARCHAR(128))
  427. RETURNS NVARCHAR(1024)
  428. WITH EXECUTE AS CALLER, ENCRYPTION
  429. AS
  430. BEGIN
  431. — backup to the .mdf’s folder,
  432. —  no ACL change is required – svc acct writes the files
  433. —  no dependency on specific local configurations to write file
  434.     RETURN (SELECT LEFT( physical_name
  435.                        , LEN(physical_name) CHARINDEX( ‘\’
  436.                                                        , REVERSE(physical_name)) + 1)
  437.          FROM sys.master_files
  438.          WHERE database_id = DB_ID(@DbName)
  439.          AND file_id = 1
  440.          AND type = 0);   
  441. END
  442. GO
  443. IF OBJECT_ID (N’dbo.NewCertificateBackupName’) IS NOT NULL
  444.    DROP FUNCTION dbo.NewCertificateBackupName
  445. GO
  446. /******************************************************************************
  447. **    Auth: Bill Wunder
  448. **    Desc: get the name of the file the certificate backup will use
  449. *******************************************************************************/
  450. CREATE FUNCTION dbo.NewCertificateBackupName (@DbName NVARCHAR(128), @CertificateName NVARCHAR(128))
  451. RETURNS NVARCHAR(1024)
  452. WITH EXECUTE AS CALLER, ENCRYPTION
  453. AS
  454. BEGIN
  455.     RETURN (REPLACE(@@SERVERNAME,‘\’,‘$’) + ‘__’ + @DbName + ‘__’ + @CertificateName + ‘__’
  456.          + REPLACE(REPLACE(CONVERT(NVARCHAR(20), CURRENT_TIMESTAMP, 20),SPACE(1),‘__’),‘:’,‘-‘));   
  457. END
  458. GO
  459. IF OBJECT_ID (N’dbo.NewMasterKeyBackupName’) IS NOT NULL
  460.    DROP FUNCTION dbo.NewMasterKeyBackupName
  461. GO
  462. /******************************************************************************
  463. **    Auth: Bill Wunder
  464. **    Desc: get the name of the file a service master key backup
  465. **    or a database master key backup
  466. *******************************************************************************/
  467. CREATE FUNCTION dbo.NewMasterKeyBackupName (@DbName NVARCHAR(128))
  468. RETURNS NVARCHAR(1024)
  469. WITH EXECUTE AS CALLER, ENCRYPTION
  470. AS
  471. BEGIN
  472.     RETURN (REPLACE(@@SERVERNAME,‘\’,‘$’) + ‘__’ +
  473.            + CASE WHEN @DbName is NULL
  474.                   THEN ‘Server__SMK’
  475.                   ELSE @DbName + ‘__DMK’ END + ‘__’ +
  476.      + REPLACE(REPLACE(CONVERT(NVARCHAR(20), CURRENT_TIMESTAMP, 20),SPACE(1),‘__’),‘:’,‘-‘));   
  477. END
  478. GO
  479. IF OBJECT_ID(‘dbo.AddNameValue’,‘P’) IS NOT NULL
  480.     DROP PROCEDURE [dbo].[AddNameValue]
  481. GO
  482. /******************************************************************************
  483. **    Auth: Bill Wunder
  484. **    Desc: encrypt sensitive data
  485. *******************************************************************************/
  486. CREATE PROCEDURE [dbo].[AddNameValue]
  487. ( @Name [NVARCHAR] (128)
  488. , @Value [NVARCHAR] (128) )
  489. WITH EXECUTE AS CALLER, ENCRYPTION
  490. AS
  491. BEGIN
  492. SET NOCOUNT ON;
  493. DECLARE @Version INT;
  494. DECLARE @Err XML;
  495. OPEN SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)]
  496. DECRYPTION BY CERTIFICATE [$(VALUE_CERTIFICATE_NAME)]
  497. WITH PASSWORD = ‘$(VALUE_CERTIFICATE_ENCRYPTION_PHRASE)’;
  498.     BEGIN TRY
  499.   IF (dbo.IsAuthorized()) = 0
  500.       RAISERROR(2147483644,16,1,‘User’);
  501.   — password policy + SQL Injection prevention
  502.   IF (dbo.CheckPhrase(@Value) = 0)
  503.    RAISERROR(2147483644,16,1,@Name);
  504.   BEGIN TRANSACTION  
  505.    — negative magnitude means attempt to insert existing value for name
  506.    SET @Version = (SELECT TOP(1) VERSION * CASE WHEN DecryptByKey(Value, 1, Name) =  @Value THEN 1 ELSE 1 END
  507.                    FROM [dbo].[NameValues] — WITH(ROWLOCK,XLOCK)
  508.                    WHERE Name = @Name
  509.                    ORDER BY Version DESC);              
  510.    IF ISNULL(@Version,1) > 0
  511.     BEGIN
  512.      INSERT [dbo].[NameValues] ([Name], [Version], [Value])
  513.      SELECT @Name, ISNULL(@Version+1,1), EncryptByKey(Key_GUID(‘$(DB_SYMMETRIC_KEY)’), @Value, 1, @Name);
  514.      INSERT dbo.NameValueHistory (Name, Version, Action, Status)
  515.      SELECT @Name, ISNULL(@Version+1,1), OBJECT_NAME(@@PROCID), ‘Complete’;    
  516.     END  
  517.    COMMIT TRANSACTION;
  518.   END TRY
  519.   BEGIN CATCH
  520.    WHILE @@TRANCOUNT > 0
  521.     ROLLBACK TRANSACTION;
  522.    INSERT dbo.NameValueHistory
  523.     ( Name
  524.     , Version
  525.     , Action
  526.     , Status
  527.     , ErrorData)
  528.    SELECT @Name
  529.         , ISNULL(@Version+1,1)
  530.         , OBJECT_NAME(@@PROCID)
  531.         , ‘Error’    
  532.         , (SELECT ERROR_NUMBER() AS ErrorNumber
  533.                 , ERROR_SEVERITY() AS ErrorSeverity
  534.                 , ERROR_STATE() as ErrorState
  535.                 , ERROR_PROCEDURE() as ErrorProcedure
  536.                 , ERROR_LINE() as ErrorLine
  537.                 , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
  538.   END CATCH;
  539.   CLOSE SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)];
  540. END
  541. GO
  542. IF OBJECT_ID(‘dbo.GetValueByName’,‘P’) IS NOT NULL
  543.     DROP PROCEDURE [dbo].[GetValueByName];
  544. GO
  545. /******************************************************************************
  546. **    Auth: Bill Wunder
  547. **    Desc: fetch encrypted value
  548. *******************************************************************************/
  549. CREATE PROCEDURE [dbo].[GetValueByName]
  550. ( @Name [NVARCHAR] (128)
  551. , @Value [NVARCHAR] (128) OUTPUT)
  552. WITH EXECUTE AS CALLER, ENCRYPTION
  553. AS
  554. BEGIN
  555. DECLARE @Version INT;
  556. SET NOCOUNT ON;
  557. OPEN SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)]
  558. DECRYPTION BY CERTIFICATE [$(VALUE_CERTIFICATE_NAME)]
  559. WITH PASSWORD = ‘$(VALUE_CERTIFICATE_ENCRYPTION_PHRASE)’;
  560. BEGIN TRY
  561.   IF (dbo.IsAuthorized()) = 0
  562.    RAISERROR(2147483644,16,1,‘User’);
  563.   — password policy + SQL Injection prevention
  564.   IF (dbo.CheckPhrase(@Value) = 0)
  565.    RAISERROR(2147483644,16,1,@Name);
  566.   SELECT TOP(1) @Version = Version
  567.               , @Value = DecryptByKey( Value, 1, Name)
  568.   FROM [dbo].[NameValues]
  569.   WHERE [Name] = @Name
  570.   ORDER BY [Version] DESC;
  571.   INSERT dbo.NameValueHistory
  572.    ( Name
  573.    , Version
  574.    , Action
  575.    , Status )
  576.   SELECT @Name
  577.        , @Version
  578.        , OBJECT_NAME(@@PROCID)
  579.        , ‘Complete’;    
  580. END TRY
  581. BEGIN CATCH
  582.   INSERT dbo.NameValueHistory
  583.    ( Name
  584.    , Version
  585.    , Action
  586.    , Status
  587.    , ErrorData)
  588.   SELECT @Name
  589.        , ISNULL(@Version+1,1)
  590.        , OBJECT_NAME(@@PROCID)
  591.        , ‘Error’    
  592.        , (SELECT ERROR_NUMBER() AS ErrorNumber
  593.                , ERROR_SEVERITY() AS ErrorSeverity
  594.                , ERROR_STATE() as ErrorState
  595.                , ERROR_PROCEDURE() as ErrorProcedure
  596.                , ERROR_LINE() as ErrorLine
  597.                , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
  598. END CATCH
  599. CLOSE SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)];
  600. END
  601. GO
  602. IF OBJECT_ID (N’dbo.ValidateNameValue’) IS NOT NULL
  603.    DROP PROCEDURE dbo.ValidateNameValue
  604. GO
  605. /******************************************************************************
  606. **    Auth: Bill Wunder
  607. **    Desc: validate encrypted value
  608. *******************************************************************************/
  609. CREATE PROCEDURE dbo.ValidateNameValue
  610. ( @Name NVARCHAR(128)
  611. , @Value NVARCHAR(128)
  612. , @IsValid BIT OUTPUT )
  613. WITH EXECUTE AS CALLER, ENCRYPTION
  614. AS
  615. BEGIN
  616. SET NOCOUNT ON;
  617. DECLARE @Version INT;
  618. SET @IsValid = 0
  619. OPEN SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)]
  620. DECRYPTION BY CERTIFICATE [$(VALUE_CERTIFICATE_NAME)]
  621. WITH PASSWORD = ‘$(VALUE_CERTIFICATE_ENCRYPTION_PHRASE)’;
  622. BEGIN TRY
  623.   IF (dbo.IsAuthorized()) = 0
  624.    RAISERROR(2147483644,16,1,‘User’);
  625.   — password policy + SQL Injection prevention
  626.   IF (dbo.CheckPhrase(@Value) = 0)
  627.    RAISERROR(2147483644,16,1,@Name);
  628.   SELECT TOP(1) @Version = Version
  629.               , @IsValid = 1
  630.   FROM [dbo].[NameValues]
  631.   WHERE [Name] = @Name
  632.   AND DecryptByKey( Value, 1, Name) = @Value
  633.   ORDER BY [Version] DESC;
  634.   INSERT dbo.NameValueHistory
  635.     ( Name
  636.     , Version
  637.     , Action
  638.     , Status)
  639.   SELECT @Name
  640.        , @Version
  641.        , OBJECT_NAME(@@PROCID)
  642.        , CASE WHEN @IsValid = 1 THEN  ‘Valid’ ELSE ‘Invalid’ END;    
  643.   END TRY
  644.   BEGIN CATCH
  645.    INSERT dbo.NameValueHistory
  646.     ( Name
  647.     , Version
  648.     , Action
  649.     , Status
  650.     , ErrorData)
  651.    SELECT @Name
  652.         , ISNULL(@Version+1,1)
  653.         , OBJECT_NAME(@@PROCID)
  654.         , ‘Error’    
  655.         , (SELECT ERROR_NUMBER() AS ErrorNumber
  656.                 , ERROR_SEVERITY() AS ErrorSeverity
  657.                 , ERROR_STATE() as ErrorState
  658.                 , ERROR_PROCEDURE() as ErrorProcedure
  659.                 , ERROR_LINE() as ErrorLine
  660.                 , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
  661.   END CATCH
  662. CLOSE SYMMETRIC KEY [$(DB_SYMMETRIC_KEY)];
  663. END
  664. GO
  665. IF OBJECT_ID (N’dbo.BackupServiceMasterKey’) IS NOT NULL
  666.    DROP PROCEDURE dbo.BackupServiceMasterKey
  667. GO
  668. /******************************************************************************
  669. **    Auth: Bill Wunder
  670. **    Desc: backup the service master key of the SQL Server Instance
  671. *******************************************************************************/
  672. CREATE PROCEDURE dbo.BackupServiceMasterKey
  673. ( @BackupPhrase NVARCHAR(128) )
  674. WITH EXECUTE AS CALLER, ENCRYPTION
  675. AS
  676. BEGIN
  677. DECLARE @Name NVARCHAR(128)
  678.       , @BackupDDL NVARCHAR(MAX)
  679.       , @BackupName NVARCHAR(128)
  680.       , @BackupPath NVARCHAR(128)
  681.       , @return_code INT;
  682. SET NOCOUNT ON;
  683. BEGIN TRY
  684.   BEGIN TRANSACTION
  685.    SET @Name = ‘SMK_BACKUP_PHRASE’;
  686.    EXEC dbo.AddNameValue @Name, @BackupPhrase;   
  687.    SET @BackupName = dbo.NewMasterKeyBackupName(NULL);
  688.    SET @BackupPath = dbo.NewBackupPath(‘master’);
  689.    SET @BackupDDL = ‘USE MASTER;’ + SPACE(1)
  690.       + ‘BACKUP SERVICE MASTER KEY’ + SPACE(1)
  691.       + ‘TO FILE = ”’ + @BackupPath + @BackupName + ‘$(MASTER_KEY_BACKUP_EXT)”’ + SPACE(1)
  692.       + ‘ENCRYPTION BY PASSWORD = ”’ + @BackupPhrase + ”’;’;
  693.    EXEC @return_code = sp_executesql @BackupDDL;
  694.    IF @return_code <> 0
  695.      RAISERROR(2147483636,16,1,‘backup’,@return_code);
  696.    ELSE
  697.      RAISERROR(2147483635,0,0,‘backup’);
  698.    INSERT MasterKeyBackupHistory
  699.      ( DbName
  700.      , KeyName
  701.      , BackupName
  702.      , BackupPath
  703.      , Action
  704.      , Status)
  705.    SELECT NULL
  706.           , ‘Service Master Key’
  707.           , @BackupName
  708.           , @BackupPath
  709.           , OBJECT_NAME(@@PROCID)
  710.           , ‘Complete’;
  711.   COMMIT TRANSACTION;
  712. END TRY
  713. BEGIN CATCH
  714.   IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
  715.   INSERT MasterKeyBackupHistory
  716.     ( DbName
  717.     , KeyName
  718.     , BackupName
  719.     , BackupPath
  720.     , Action
  721.     , Status
  722.     , ErrorData)
  723.   SELECT NULL
  724.          , ‘Service Master Key’
  725.          , @BackupName
  726.          , @BackupPath
  727.          , OBJECT_NAME(@@PROCID)
  728.          , ‘Error’
  729.    , (SELECT ERROR_NUMBER() AS ErrorNumber
  730.            , ERROR_SEVERITY() AS ErrorSeverity
  731.            , ERROR_STATE() as ErrorState
  732.            , ERROR_PROCEDURE() as ErrorProcedure
  733.            , ERROR_LINE() as ErrorLine
  734.            , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
  735. END CATCH
  736. END
  737. GO
  738. IF OBJECT_ID (N’dbo.RestoreServiceMasterKey’) IS NOT NULL
  739.    DROP PROCEDURE dbo.RestoreServiceMasterKey
  740. GO
  741. /******************************************************************************
  742. **    Auth: Bill Wunder
  743. **    Desc: restore the service master key of the SQL Server Instance from backup
  744. *******************************************************************************/
  745. CREATE PROCEDURE dbo.RestoreServiceMasterKey
  746. WITH EXECUTE AS CALLER, ENCRYPTION
  747. AS
  748. BEGIN
  749. DECLARE @RestoreDDL NVARCHAR(MAX)
  750.       , @BackupPhrase NVARCHAR(128)
  751.       , @BackupName NVARCHAR(128)
  752.       , @BackupPath NVARCHAR(128)
  753.       , @return_code INT;
  754. SET NOCOUNT ON;
  755. BEGIN TRY
  756.   EXEC [dbo].[GetValueByName] ‘SMK_BACKUP_PHRASE’, @BackupPhrase OUTPUT;
  757.   SELECT TOP(1) @BackupName = BackupName
  758.               , @BackupPath = BackupPath
  759.   FROM dbo.MasterKeyBackupHistory
  760.   WHERE KeyName = ‘Service Master Key’
  761.   AND Action = ‘BackupServiceMasterKey’
  762.   AND Status = ‘Complete’
  763.   ORDER BY Id DESC;           
  764.   SET @RestoreDDL = ‘USE MASTER;’ + SPACE(1)
  765.      + ‘RESTORE SERVICE MASTER KEY’ + SPACE(1)
  766.      + ‘FROM FILE = ”’ + @BackupPath + @BackupName + ‘$(MASTER_KEY_BACKUP_EXT)”’ + SPACE(1)
  767.      + ‘DECRYPTION BY PASSWORD = ”’ + @BackupPhrase + ”’;’;
  768.   EXEC @return_code = sp_executesql @RestoreDDL;
  769.   IF @return_code <> 0
  770.     RAISERROR(2147483636,16,1,‘restore’,@return_code);
  771.   ELSE
  772.     RAISERROR(2147483635,0,0,‘restore’);
  773.   INSERT MasterKeyBackupHistory
  774.     ( DbName
  775.     , KeyName
  776.     , BackupName
  777.     , BackupPath
  778.     , Action
  779.     , Status)
  780.   SELECT NULL
  781.    , ‘Service Master Key’
  782.    , @BackupName
  783.    , @BackupPath
  784.    , OBJECT_NAME(@@PROCID)
  785.    , ‘Complete’;
  786. END TRY
  787. BEGIN CATCH
  788.   INSERT MasterKeyBackupHistory
  789.     ( DbName
  790.     , KeyName
  791.     , BackupName
  792.     , BackupPath
  793.     , Action
  794.     , Status
  795.     , ErrorData)
  796.   SELECT NULL
  797.          , ‘Service Master Key’
  798.          , @BackupName
  799.          , @BackupPath
  800.    , OBJECT_NAME(@@PROCID)
  801.          , ‘Error’
  802.    , (SELECT ERROR_NUMBER() AS ErrorNumber
  803.            , ERROR_SEVERITY() AS ErrorSeverity
  804.            , ERROR_STATE() as ErrorState
  805.            , ERROR_PROCEDURE() as ErrorProcedure
  806.            , ERROR_LINE() as ErrorLine
  807.            , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
  808. END CATCH
  809. END
  810. GO
  811. IF OBJECT_ID (N’dbo.BackupDatabaseMasterKey’) IS NOT NULL
  812.    DROP PROCEDURE dbo.BackupDatabaseMasterKey
  813. GO
  814. /******************************************************************************
  815. **    Auth: Bill Wunder
  816. **    Desc: backup a database master key
  817. *******************************************************************************/
  818. CREATE PROCEDURE dbo.BackupDatabaseMasterKey
  819. ( @DbName NVARCHAR(128)
  820. , @BackupPhrase NVARCHAR(128)
  821. , @KeyPhrase NVARCHAR(128) = NULL )
  822. WITH EXECUTE AS CALLER, ENCRYPTION
  823. AS
  824. BEGIN
  825. DECLARE @OpenDDL NVARCHAR(184)
  826.        , @BackupDDL NVARCHAR(MAX)
  827.        , @CloseDDL NVARCHAR(184)
  828.        , @BackupPhraseName NVARCHAR(128)
  829.        , @KeyPhraseName NVARCHAR(128)
  830.        , @BackupName NVARCHAR(128)
  831.        , @BackupPath NVARCHAR(128)
  832.        , @return_code INT;
  833. SET NOCOUNT ON;
  834. BEGIN TRY
  835.   BEGIN TRANSACTION;
  836.    IF DB_ID(@DbName) IS NULL
  837.      RAISERROR(2147483644,16,1,@DbName);
  838.    SET @BackupPhraseName = @DbName + N’_DMK_BACKUP_PHRASE’;
  839.    EXEC dbo.AddNameValue @BackupPhraseName, @BackupPhrase;   
  840.    SET @BackupName = dbo.NewMasterKeyBackupName (@DbName);
  841.    SET @BackupPath = dbo.NewBackupPath(@DbName)
  842.    SET @KeyPhraseName = @DbName + N’_DMK_ENCRYPTION_PHRASE’;
  843.    EXEC GetValueByName @KeyPhraseName, @KeyPhrase OUTPUT;
  844.    SET @BackupDDL = ‘USE [‘ + @DbName + ‘];’
  845.       + ‘OPEN MASTER KEY DECRYPTION BY PASSWORD = ”’ + @KeyPhrase + ”’;’
  846.       + ‘BACKUP MASTER KEY’ + SPACE(1)
  847.       + ‘TO FILE = ”’ + @BackupPath + @BackupName + ‘$(MASTER_KEY_BACKUP_EXT)”’ + SPACE(1)
  848.       + ‘ENCRYPTION BY PASSWORD = ”’ + @BackupPhrase + ”’;’
  849.       + ‘CLOSE MASTER KEY;’;
  850.    EXEC @return_code = sp_executesql @BackupDDL;
  851.    IF @return_code <> 0
  852.      RAISERROR(2147483634,16,1,@DbName, ‘backup’, @return_code);
  853.    ELSE
  854.      RAISERROR(2147483633,0,0,@DbName, ‘backup’);
  855.    INSERT MasterKeyBackupHistory
  856.      ( DbName
  857.      , KeyName
  858.      , BackupName
  859.      , BackupPath
  860.      , Action
  861.      , Status)
  862.    SELECT @DbName
  863.           , ‘Database Master Key’
  864.           , @BackupName
  865.           , @BackupPath
  866.           , OBJECT_NAME(@@PROCID)
  867.           , ‘Complete’;
  868.   COMMIT TRANSACTION;
  869. END TRY
  870. BEGIN CATCH
  871.   WHILE @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
  872.   INSERT MasterKeyBackupHistory
  873.     ( DbName
  874.     , KeyName
  875.     , BackupName
  876.     , BackupPath
  877.     , Action
  878.     , Status
  879.     , ErrorData)
  880.   SELECT @DbName
  881.          , ‘Database Master Key’
  882.          , ISNULL(@BackupName,)
  883.          , ISNULL(@BackupPath,)
  884.          , OBJECT_NAME(@@PROCID)
  885.          , ‘Error’
  886.    , (SELECT ERROR_NUMBER() AS ErrorNumber
  887.            , ERROR_SEVERITY() AS ErrorSeverity
  888.            , ERROR_STATE() as ErrorState
  889.            , ERROR_PROCEDURE() as ErrorProcedure
  890.            , ERROR_LINE() as ErrorLine
  891.            , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
  892. END CATCH
  893. END
  894. GO
  895. IF OBJECT_ID (N’dbo.RestoreDatabaseMasterKey’) IS NOT NULL
  896.    DROP PROCEDURE dbo.RestoreDatabaseMasterKey
  897. GO
  898. /******************************************************************************
  899. **    Auth: Bill Wunder
  900. **    Desc: restore a database master key from a backup
  901. *******************************************************************************/
  902. CREATE PROCEDURE dbo.RestoreDatabaseMasterKey
  903. ( @DbName NVARCHAR(128) )
  904. WITH EXECUTE AS CALLER, ENCRYPTION
  905. AS
  906. BEGIN
  907. DECLARE @DMKRestoreDDL NVARCHAR(MAX)
  908.       , @BackupValueName NVARCHAR(128)
  909.       , @KeyValueName NVARCHAR(128)
  910.       , @BackupName NVARCHAR(128)
  911.       , @BackupPath NVARCHAR(128)
  912.       , @BackupPhrase NVARCHAR(128)
  913.       , @KeyPhrase NVARCHAR(128)
  914.       , @return_code INT;
  915. SET NOCOUNT ON;
  916. BEGIN TRY
  917.   SET @BackupValueName = @DbName + ‘_DMK_BACKUP_PHRASE’;
  918.   EXEC [dbo].[GetValueByName] @BackupValueName, @BackupPhrase OUTPUT;
  919.   SET @KeyValueName = @DbName + ‘_DMK_ENCRYPTION_PHRASE’;
  920.   EXEC [dbo].[GetValueByName] @KeyValueName, @KeyPhrase OUTPUT;
  921.   SELECT TOP (1) @BackupPath = BackupPath
  922.                , @BackupName = BackupName                 
  923.   FROM dbo.MasterKeyBackupHistory
  924.   WHERE KeyName = ‘Database Master Key’
  925.   AND DbNAme = @DbName
  926.   AND Action = ‘BackupDatabaseMasterKey’
  927.   AND Status = ‘Complete’
  928.   ORDER BY Id DESC;  
  929.   SET @DMKRestoreDDL = ‘USE ‘ + @DbName + ‘;’ + SPACE(1)
  930.      + ‘RESTORE MASTER KEY’ + SPACE(1)
  931.      + ‘FROM FILE = ”’ + @BackupPath + @BackupName + ‘$(MASTER_KEY_BACKUP_EXT)”’ + SPACE(1)
  932.      + ‘DECRYPTION BY PASSWORD = ”’ + @BackupPhrase + ””   
  933.      + CASE WHEN @KeyPhrase IS NULL
  934.             THEN
  935.             ELSE ‘ENCRYPTION BY PASSWORD = ”’ + @KeyPhrase + ””
  936.             END + ‘;’
  937.   EXEC @return_code = sp_executesql @DMKRestoreDDL;
  938.   IF @return_code <> 0
  939.     RAISERROR(2147483634,16,1,@DbName, ‘restore’, @return_code);
  940.   ELSE
  941.     RAISERROR(2147483633,0,0,@DbName, ‘restore’);
  942.   INSERT MasterKeyBackupHistory
  943.     ( DbName
  944.     , KeyName
  945.     , BackupName
  946.     , BackupPath
  947.     , Action
  948.     , Status)
  949.   SELECT @DbName
  950.          , ‘Database Master Key’
  951.          , @BackupName
  952.          , @BackupPath
  953.          , OBJECT_NAME(@@PROCID)
  954.          , ‘Complete’;
  955. END TRY
  956. BEGIN CATCH
  957.   INSERT MasterKeyBackupHistory
  958.     ( DbName
  959.     , KeyName
  960.     , BackupName
  961.     , BackupPath
  962.     , Action
  963.     , Status
  964.     , ErrorData)
  965.   SELECT ISNULL(@DbName,)
  966.          , ‘Database Master Key’
  967.          , ISNULL(@BackupName,)
  968.          , ISNULL(@BackupPath,)
  969.          , OBJECT_NAME(@@PROCID)
  970.          , ‘Error’
  971.    , (SELECT ERROR_NUMBER() AS ErrorNumber
  972.            , ERROR_SEVERITY() AS ErrorSeverity
  973.            , ERROR_STATE() as ErrorState
  974.            , ERROR_PROCEDURE() as ErrorProcedure
  975.            , ERROR_LINE() as ErrorLine
  976.            , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
  977. END CATCH
  978. END
  979. GO
  980. IF OBJECT_ID (N’dbo.BackupCertificate’) IS NOT NULL
  981.    DROP PROCEDURE dbo.BackupCertificate
  982. GO
  983. /******************************************************************************
  984. **    Auth: Bill Wunder
  985. **    Desc: backup a certificate 
  986. *******************************************************************************/
  987. CREATE PROCEDURE dbo.BackupCertificate
  988. ( @CertificateName NVARCHAR(128)
  989. , @DbName NVARCHAR(128)
  990. , @BackupPhrase NVARCHAR(128) = NULL — use stored value if exists
  991. , @KeyPhrase NVARCHAR(128) = NULL) — value needed only when type = PW
  992. WITH EXECUTE AS CALLER, ENCRYPTION
  993. AS
  994. BEGIN
  995. DECLARE @CertificateList TABLE (name NVARCHAR(128));
  996. DECLARE @CertificateListDDL NVARCHAR(165)
  997.       , @ThumbprintDDL NVARCHAR(MAX)
  998.       , @Thumbprint VARBINARY(32)
  999.       , @PrivateKeyEncryptionType CHAR(2)
  1000.       , @BackupDDL NVARCHAR(MAX)
  1001.       , @BackupName NVARCHAR(128)
  1002.       , @BackupPath NVARCHAR(128)
  1003.       , @BackupValueName NVARCHAR(128)
  1004.       , @KeyValueName NVARCHAR(128)
  1005.       , @return_code INT;
  1006. SET NOCOUNT ON;
  1007. BEGIN TRY
  1008.   BEGIN TRANSACTION
  1009.    SET @BackupValueName = @DbName + ‘_’ + @CertificateName + ‘_CERTIFICATE_BACKUP_PHRASE’
  1010.    IF @BackupPhrase IS NULL
  1011.     EXEC [dbo].[GetValueByName] @BackupValueName, @BackupPhrase OUTPUT;
  1012.    ELSE
  1013.      EXEC dbo.AddNameValue @BackupValueName, @BackupPhrase;   
  1014.    SET @KeyValueName = @DbName + ‘_’ + @CertificateName + ‘_CERTIFICATE_ENCRYPTION_PHRASE’
  1015.    IF @KeyPhrase IS NULL
  1016.     EXEC [dbo].[GetValueByName] @KeyValueName, @KeyPhrase OUTPUT;
  1017.    ELSE
  1018.      EXEC dbo.AddNameValue @KeyValueName, @KeyPhrase;   
  1019.   — verify @DbName and @CertificateName before parsing into any query
  1020.   IF DB_ID(@DbName) IS NOT NULL   
  1021.    BEGIN
  1022.     SET @CertificateListDDL = ‘SELECT name from ‘ + @DbName + ‘.sys.certificates’
  1023.     INSERT @CertificateList (name)    
  1024.     EXEC sp_executesql @CertificateListDDL;
  1025.    END
  1026.   — @CertificateName is used in the where clause first to complete sql injection prevention
  1027.   IF NOT EXISTS (SELECT name FROM @CertificateList WHERE name = @CertificateName)
  1028.    RAISERROR(2147483644,16,1,‘Certificate’);
  1029.   SET @ThumbprintDDL = ‘USE ‘ + @DbName + ‘;’ + SPACE(1)
  1030.                + ‘SELECT @Thumbprint = Thumbprint’ + SPACE(1)
  1031.                + ‘     , @PrivateKeyEncryptionType = pvt_key_encryption_type’ + SPACE(1)
  1032.                + ‘FROM sys.certificates’ + SPACE(1)
  1033.                + ‘WHERE name = @CertificateName’;
  1034.   EXEC sp_executesql @ThumbprintDDL
  1035.                , N’@CertificateName NVARCHAR(128), @Thumbprint VARBINARY(32) OUTPUT, @PrivateKeyEncryptionType CHAR(2) OUTPUT’
  1036.                , @CertificateName, @Thumbprint OUTPUT, @PrivateKeyEncryptionType OUTPUT;
  1037.   SET @BackupName = dbo.NewCertificateBackupName (@DbName, @CertificateName);
  1038.   SET @BackupPath = dbo.NewBackupPath(@DbName);
  1039.   SET @BackupDDL = ‘USE ‘ + @DbName + ‘;’
  1040.      + ‘BACKUP CERTIFICATE ‘ + @CertificateName + SPACE(1)
  1041.      + ‘TO FILE = ”’ + @BackupPath + @BackupName + ‘$(PUBLIC_KEY_BACKUP_EXT)”’ + SPACE(1)
  1042.   IF @PrivateKeyEncryptionType <> ‘NA’
  1043.    BEGIN
  1044.     SET @BackupDDL = @BackupDDL + ‘WITH PRIVATE KEY’ + SPACE(1)
  1045.        + ‘( FILE = ”’ + @BackupPath + @BackupName + ‘$(PRIVATE_KEY_BACKUP_EXT)”’
  1046.        + ‘, ENCRYPTION BY PASSWORD = ”’ + @BackupPhrase + ””;
  1047.     IF @PrivateKeyEncryptionType = ‘PW’
  1048.      SET @BackupDDL = @BackupDDL + ‘, DECRYPTION BY PASSWORD = ”’ + @KeyPhrase + ””;
  1049.     SET @BackupDDL = @BackupDDL + ‘)’;
  1050.    END
  1051.   EXEC @return_code = sp_executesql @BackupDDL;
  1052.   IF @return_code <> 0
  1053.     RAISERROR(2147483632,16,1,@DbName,@CertificateName,@return_code,‘backup’);
  1054.   ELSE
  1055.     RAISERROR(2147483631, 0,0,@DbName,@CertificateName,‘backup’);
  1056.   INSERT dbo.CertificateBackupHistory
  1057.     ( DbName
  1058.     , CertificateName
  1059.     , BackupName
  1060.     , BackupPath
  1061.     , Action
  1062.     , Status
  1063.     , Thumbprint
  1064.     , PrivateKeyEncryptionType)
  1065.   SELECT @DbName
  1066.        , @CertificateName
  1067.        , @BackupName
  1068.        , @BackupPath
  1069.        , OBJECT_NAME(@@PROCID)
  1070.        , ‘Complete’
  1071.        , @Thumbprint
  1072.        , @PrivateKeyEncryptionType;
  1073.   COMMIT TRANSACTION
  1074. END TRY
  1075. BEGIN CATCH
  1076.   WHILE @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
  1077.   INSERT dbo.CertificateBackupHistory
  1078.    ( DbName
  1079.    , CertificateName
  1080.    , BackupName
  1081.    , BackupPath
  1082.    , Action
  1083.    , Status
  1084.    , Thumbprint
  1085.    , PrivateKeyEncryptionType
  1086.    , ErrorData)
  1087. SELECT ISNULL(@DbName,)
  1088.       , ISNULL(@CertificateName, )
  1089.       , ISNULL(@BackupName,)
  1090.       , ISNULL(@BackupPath,)
  1091.       , OBJECT_NAME(@@PROCID)
  1092.       , ‘Error’
  1093.       , ISNULL(@Thumbprint,0x0)
  1094.       , ISNULL(@PrivateKeyEncryptionType,)
  1095.       , (SELECT ERROR_NUMBER() AS ErrorNumber
  1096.               , ERROR_SEVERITY() AS ErrorSeverity
  1097.               , ERROR_STATE() as ErrorState
  1098.               , ERROR_PROCEDURE() as ErrorProcedure
  1099.               , ERROR_LINE() as ErrorLine
  1100.               , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
  1101. END CATCH      
  1102. END
  1103. GO
  1104. IF OBJECT_ID (N’dbo.RestoreCertificate’) IS NOT NULL
  1105.    DROP PROCEDURE dbo.RestoreCertificate
  1106. GO
  1107. /******************************************************************************
  1108. **    Auth: Bill Wunder
  1109. **    Desc: restore a certificate
  1110. **    ASSERT: the backup is located in same folder as the .mdf of the database
  1111. **            and that the .mdf is never moved from that folder
  1112. *******************************************************************************/
  1113. CREATE PROCEDURE dbo.RestoreCertificate
  1114. ( @CertificateName NVARCHAR(128)
  1115. , @DbName NVARCHAR(128) )
  1116. WITH EXECUTE AS CALLER, ENCRYPTION
  1117. AS
  1118. BEGIN
  1119. DECLARE @RestoreDDL NVARCHAR(4000)
  1120.       , @LookupDDL NVARCHAR(165)
  1121.       , @Thumbprint VARBINARY(32)
  1122.       , @PrivateKeyEncryptionType CHAR(2)
  1123.       , @CertificateId INT
  1124.       , @BackupName NVARCHAR(128)
  1125.       , @BackupPath NVARCHAR(1024)
  1126.       , @BackupPhrase NVARCHAR(128)
  1127.       , @BackupValueName NVARCHAR(128)
  1128.       , @KeyPhrase NVARCHAR(128)
  1129.       , @KeyValueName NVARCHAR(128)
  1130.       , @return_code INT;
  1131. SET NOCOUNT ON;
  1132. BEGIN TRY
  1133.    — sql injections should blow up here
  1134.    SELECT TOP(1)
  1135.       @PrivateKeyEncryptionType = PrivateKeyEncryptionType
  1136.     , @Thumbprint = Thumbprint
  1137.     , @BackupPath = BackupPath
  1138.     , @BackupName = BackupName
  1139.    FROM dbo.CertificateBackupHistory
  1140.    WHERE ServerName = @@SERVERNAME
  1141.    AND DbName = @DbName
  1142.    AND CertificateName = @CertificateName
  1143.    AND Action = ‘BackupCertificate’
  1144.    AND Status = ‘Complete’
  1145.    ORDER BY Id DESC;   
  1146.    IF DB_ID(@DbName) IS NULL
  1147.     RAISERROR(2147483644,16,1,‘Database’);
  1148.    SET @LookupDDL = ‘SELECT @CertificateId = certificate_id’ + SPACE(1)
  1149.                   + ‘FROM ‘ + @DbName + ‘.sys.certificates’ + SPACE(1)
  1150.                   + ‘WHERE name = @CertificateName ‘ + SPACE(1)  
  1151.                   + ‘AND Thumbprint = @Thumbprint’;
  1152.    EXEC sp_executesql @LookupDDL
  1153.                     , N’@CertificateName NVARCHAR(128), @Thumbprint VARBINARY(32), @CertificateId INT OUTPUT’
  1154.                     , @CertificateName, @Thumbprint, @CertificateId OUTPUT;
  1155.    IF (@CertificateId IS NOT NULL) — certificate exist – cannot restore
  1156.     RAISERROR(2147483637,16,1,@DbName,@CertificateName,‘cannot restore over existing certificate’);
  1157.    SET @BackupValueName = @DbName + ‘_’ + @CertificateName + ‘_CERTIFICATE_BACKUP_PHRASE’
  1158.    EXEC dbo.GetValueByName @BackupValueName, @BackupPhrase OUTPUT;  
  1159.    SET @KeyValueName = @DbName + ‘_’ + @CertificateName + ‘_CERTIFICATE_ENCRYPTION_PHRASE’
  1160.    EXEC dbo.GetValueByName @KeyValueName, @KeyPhrase OUTPUT;  
  1161.    SET @RestoreDDL = ‘USE [‘ + @DbName + ‘];’ + SPACE(1)
  1162.                    + ‘CREATE CERTIFICATE ‘ + @CertificateName + SPACE(1)
  1163.                    + ‘FROM FILE = ”’ + @BackupPath + @BackupName + ‘$(PUBLIC_KEY_BACKUP_EXT)”’;
  1164.    IF @PrivateKeyEncryptionType <> ‘NA’
  1165.     BEGIN
  1166.      SET @RestoreDDL  = @RestoreDDL  + SPACE(1) + ‘WITH PRIVATE KEY’ + SPACE(1)
  1167.                       + ‘(FILE = ”’ + @BackupPath + @BackupName + ‘$(PRIVATE_KEY_BACKUP_EXT)”’ + SPACE(1)
  1168.                       + ‘, DECRYPTION BY PASSWORD = ”’ + @BackupPhrase + ”” ;
  1169.      IF @PrivateKeyEncryptionType = ‘PW’
  1170.       SET @RestoreDDL = @RestoreDDL + ‘, ENCRYPTION BY PASSWORD = ”’ + @KeyPhrase + ””;
  1171.      SET @RestoreDDL = @RestoreDDL + ‘);’;
  1172.     END
  1173.    EXEC @return_code = sp_executesql @RestoreDDL;
  1174.    IF @return_code <> 0
  1175.     RAISERROR(2147483632,16,1,@DbName,@CertificateName,@return_code,‘restore’);
  1176.    ELSE
  1177.     RAISERROR(2147483631, 0,0,@DbName,@CertificateName,‘restore’);
  1178.    INSERT dbo.CertificateBackupHistory
  1179.      ( DbName
  1180.      , CertificateName
  1181.      , BackupName
  1182.      , BackupPath
  1183.      , Action
  1184.      , Status
  1185.      , Thumbprint
  1186.      , PrivateKeyEncryptionType)
  1187.    SELECT @DbName
  1188.         , @CertificateName
  1189.         , @BackupName
  1190.         , @BackupPath
  1191.         , OBJECT_NAME(@@PROCID)
  1192.         , ‘Complete’
  1193.         , @Thumbprint
  1194.         , @PrivateKeyEncryptionType;
  1195.   END TRY
  1196.   BEGIN CATCH
  1197.     INSERT dbo.CertificateBackupHistory
  1198.       ( DbName
  1199.       , CertificateName
  1200.       , BackupName
  1201.       , BackupPath
  1202.       , Action
  1203.       , Status
  1204.       , Thumbprint
  1205.       , PrivateKeyEncryptionType
  1206.       , ErrorData)
  1207.     SELECT ISNULL(@DbName,)
  1208.          , ISNULL(@CertificateName, )
  1209.          , ISNULL(@BackupName,)
  1210.          , ISNULL(@BackupPath,)
  1211.          , OBJECT_NAME(@@PROCID)
  1212.          , ‘Error’
  1213.          , ISNULL(@Thumbprint,0x0)
  1214.          , ISNULL(@PrivateKeyEncryptionType,)
  1215.          , (SELECT ERROR_NUMBER() AS ErrorNumber
  1216.                  , ERROR_SEVERITY() AS ErrorSeverity
  1217.                  , ERROR_STATE() as ErrorState
  1218.                  , ERROR_PROCEDURE() as ErrorProcedure
  1219.                  , ERROR_LINE() as ErrorLine
  1220.                  , ERROR_MESSAGE() as ErrorMessage FOR XML RAW);  
  1221.   END CATCH
  1222. END
  1223. GO
  1224. IF OBJECT_ID (N’dbo.CertificateBackupsByThumbprint’) IS NOT NULL
  1225.    DROP PROCEDURE dbo.CertificateBackupsByThumbprint
  1226. GO
  1227. /******************************************************************************
  1228. **    Auth: Bill Wunder
  1229. **    Desc: list all certificate backups for the provided thumbprint
  1230. *******************************************************************************/
  1231. CREATE PROCEDURE dbo.CertificateBackupsByThumbprint
  1232. ( @Thumbprint VARBINARY(32) )
  1233. WITH EXECUTE AS CALLER, ENCRYPTION
  1234. AS
  1235. BEGIN
  1236. SELECT ServerName
  1237.       , DbName
  1238.       , CertificateName
  1239.       , BackupName
  1240.       , BackupPath
  1241.       , Action
  1242.       , Status
  1243.       , Thumbprint
  1244.       , PrivateKeyEncryptionType
  1245.       , ErrorData
  1246. FROM dbo.CertificateBackupHistory
  1247. WHERE Thumbprint = @Thumbprint;
  1248. END
  1249. GO
  1250. — dogfood certificate encryption secrets to dbo.NameValue
  1251. — backup the hierearchy, verify the secrets
  1252. DECLARE @Name NVARCHAR(128)
  1253.       , @Value [NVARCHAR] (128)
  1254.       , @IsValid BIT
  1255.       , @DbName NVARCHAR(128);
  1256. SELECT @DbName = DB_NAME();
  1257.  
  1258. SELECT @Name = ‘SMK_BACKUP_PHRASE’
  1259.     , @Value = ‘$(SMK_BACKUP_PHRASE)’;
  1260. EXEC dbo.BackupServiceMasterKey @Value;
  1261. SET @Value = NULL
  1262. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
  1263. SET @IsValid = NULL
  1264. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
  1265. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
  1266.  
  1267. SELECT @Name = ‘master_DMK_ENCRYPTION_PHRASE’
  1268.      , @Value = ‘$(master_DMK_ENCRYPTION_PHRASE)’;
  1269. EXEC [dbo].[AddNameValue] @Name, @Value;
  1270. SET @Value = NULL
  1271. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
  1272. SET @IsValid = NULL
  1273. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
  1274. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
  1275.  
  1276. SELECT @Name = ‘master_DMK_BACKUP_PHRASE’
  1277.      , @Value = ‘$(keyback_DMK_BACKUP_PHRASE)’;
  1278. EXEC dbo.BackupDatabaseMasterKey ‘master’, @Value;
  1279. SET @Value = NULL
  1280. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
  1281. SET @IsValid = NULL
  1282. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
  1283. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
  1284.  
  1285. SELECT @Name = DB_NAME() + ‘_DMK_ENCRYPTION_PHRASE’
  1286.      , @Value = ‘$(keyback_DMK_ENCRYPTION_PHRASE)’;
  1287. EXEC [dbo].[AddNameValue] @Name, @Value;
  1288. SET @Value = NULL
  1289. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
  1290. SET @IsValid = NULL
  1291. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
  1292. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
  1293.  
  1294. SELECT @Name = DB_NAME() + ‘_DMK_BACKUP_PHRASE’
  1295.      , @Value = ‘$(keyback_DMK_BACKUP_PHRASE)’
  1296. EXEC dbo.BackupDatabaseMasterKey @DbName, @Value;
  1297. SET @Value = NULL
  1298. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
  1299. SET @IsValid = NULL
  1300. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
  1301. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
  1302.  
  1303. — Master Key encrypted certs need the back file encryption password
  1304. SET @Name = ‘master_$(TDE_CERTIFICATE_NAME)_CERTIFICATE_BACKUP_PHRASE’;
  1305. SET @Value = ‘$(TDE_CERTIFICATE_BACKUP_PHRASE)’;
  1306. EXEC dbo.BackupCertificate ‘$(TDE_CERTIFICATE_NAME)’, ‘master’, @Value;
  1307. SET @Value = NULL;
  1308. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
  1309. SET @IsValid = NULL;
  1310. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
  1311. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
  1312.  
  1313. — password encrypted certs also need the private key encryption password at time of export
  1314.  
  1315. SET @Name = DB_NAME() + ‘_$(VALUE_CERTIFICATE_NAME)_CERTIFICATE_ENCRYPTION_PHRASE’;
  1316. SET @Value = ‘$(VALUE_CERTIFICATE_ENCRYPTION_PHRASE)’;
  1317. EXEC [dbo].[AddNameValue] @Name, @Value;
  1318. SET @IsValid = NULL;
  1319. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT;
  1320. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
  1321.  
  1322. SET @Name = DB_NAME() + ‘_$(VALUE_CERTIFICATE_NAME)_CERTIFICATE_BACKUP_PHRASE’;
  1323. SET @Value = ‘$(VALUE_CERTIFICATE_BACKUP_PHRASE)’;
  1324. EXEC dbo.BackupCertificate ‘$(VALUE_CERTIFICATE_NAME)’, @DBName, @Value, ‘$(VALUE_CERTIFICATE_ENCRYPTION_PHRASE)’;
  1325. SET @Value = NULL;
  1326. EXEC [dbo].[GetValueByName] @Name, @Value OUTPUT;
  1327. SET @IsValid = NULL;
  1328. EXEC [dbo].[ValidateNameValue] @Name, @Value, @IsValid OUTPUT ;
  1329. IF @IsValid = 0 RAISERROR(2147483643,16,1,@Name);
  1330.  
  1331. GO
  1332. /*
  1333. SELECT * FROM [dbo].[MasterKeyBackupHistory];
  1334. SELECT * FROM [dbo].[CertificateBackupHistory];
  1335. SELECT * FROM [dbo].[NameValues];
  1336. SELECT * FROM [dbo].[NameValueHistory];
  1337. SELECT * FROM sys.dm_database_encryption_keys
  1338. */
  1339.  
  1340. /* UNINSTALL
  1341. remove any backup files created – find path + name
  1342. SELECT * FROM [dbo].[MasterKeyBackupHistory];
  1343. SELECT * FROM [dbo].[CertificateBackupHistory];
  1344.  
  1345. USE keyback;
  1346. ALTER DATABASE keyback SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  1347.  
  1348. USE master;
  1349. DROP DATABASE keyback;
  1350. EXEC sp_dropmessage 2147483631;
  1351. EXEC sp_dropmessage 2147483632;
  1352. EXEC sp_dropmessage 2147483633;
  1353. EXEC sp_dropmessage 2147483634;
  1354. EXEC sp_dropmessage 2147483635;
  1355. EXEC sp_dropmessage 2147483636;
  1356. EXEC sp_dropmessage 2147483641;
  1357. EXEC sp_dropmessage 2147483642;
  1358. EXEC sp_dropmessage 2147483643;
  1359. EXEC sp_dropmessage 2147483644;
  1360. EXEC sp_dropmessage 2147483645;
  1361. DROP CERTIFICATE TDECertificate;
  1362. DROP MASTER KEY; — only drop master’s Master Key if it was added by this script!
  1363. */

(The template can also be downloaded here if you’d like to check it out but would rather not copy/paste 1000+ lines of T-SQL. An auxiliary test script is here.)

SQL Server Encryption Hierarchy Overview

In a basic configuration, a SQL Server’s Database Master Keys (DMK) are encrypted by the Service Master Key (SMK).  Certificates – always at a database scope – are encrypted by the DMK of the containing database. The SMK is always encrypted by the SQL Server Service Account user name and password. The EH may also properly include certificates originated externally from Assemblies or Hardware Security Devices HSD – either by loading certificates from files or using Extensible Key Management (EKM) in more advanced configurations.


If the SQL Server service account is changed, plan for enough time to decrypt and re-encrypt the Service Master Key and everything encrypted from it!  Use ALTER SERVICE MASTER KEY to move the key to the new account. When Database Master Keys are changed, plan for adequate time for the encryption hierarchy within the database to refresh using the new key. Use the REGENERATE option of  ALTER SERVICE MASTER KEY or ALTER MASTER KEY to begin the refresh of the hierarchy of encrypted data using the changed key. 


A password is required to encrypt any Encryption Hierarchy backup file. When backups are done using the stored procedures created from the template, the passwords are encrypted and stored along with the audit history in the selected user database. Members of the keybackAdmin database role can export, import or regenerate master keys at will – being a sysadmin alone is not enough, you must also be a member of the keybackAdmin role. Carefully restrict the membership. Non keybackAdmin sysadmins can still corrupt the database and they can still backup encryption hierarchy nodes using ad hoc methods, but the ability to recover secrets is far less likely. Consistently storing secrets used to encrypt the encryption hierarchy objects and secrets used to backup the object are both necessary in order to be able to restore using the keyback stored procedures. 

BACKUP and RESTORE syntax for the SMK and any DMKs are pretty straight forward. The password or phrase to encrypt the backup is always required. Database Master Keys may also have been encrypted with a password – instead of – or in addition to – the Service Master Key. Certificates’ have even more options to consider. The certificate may have been imported and have no dependency upon the database Master Key. The private key may have been intentionally dropped.

I recommend Master Keys and Certificates are not backed up routinely.  There is nothing in the template that should be scheduled to run once a day, week or even month in every database on the server. Instead, Encryption Hierarchy Management should be a part of the software lifecycle change process. Save any passwords/phrases to the encrypted NameValue store at the time the keys and certificates are created or changed.  Backup keys and certificates when created or changed, then monitor for unexpected change, get that secure offsite location up and running, and establish a test environment for use to restore and validate your keys held in backup. You may even want to consider detaching the database when not in use. Under TDE or file system encryption this will be more secure than leaving the idle database for window shoppers…

As you use the template you may find a need to customize it to suit your needs. Go for it. It is a template. As with restores, just be sure to test your changes before you throw them at a live SQL Server or at an existing keyback data set.

Executing a script built from this template will establish an Encryption Hierarchy secret store and logging database – keyback is the database name I use – and then dog food backups of the pieces of the Encryption Hierarchy of the master and keyback database that are used by the tool to the same folder as the .mdf for the database. The Encryption Hierarchy is backed up, the secret store is validated, and you get to see the tool in action.

Suggested Template Deployment

Add the script to the template library of the clients used to work from the Central Management Server for the environment.

Add template

When you load the script into a SQL Server Management Studio query window – whether or not is has been saved to the template library, hit CTRL+SHFT+M to see the parameters you must provide to complete the template.

keybackAdminParameters

The values with defaults can be changed or you can use the default, the secrets have no defaults. Enter words or phrases that meet the password strength requirements of the local environment.

Choose any existing database or specify a database name that does not exist and the database will be created.

In addition to creating the database the script will:

  • Backup the Service Master Key
  • Create – if necessary – and backup the Database Master Key for the master
  • Create and backup a certificate in master for use in TDE of the keyback database
  • Create and backup the Database Master Key for the keyback database
  • Create a Database Encryption Key in the keyback database (SQL 2008 Enterprise, DataCenter and Developer editions only) You my want to use file system encryption such as TrueCrypt if your SQL Server instance is not TDE capable. I wrote a bit about that in a previous post. With file system encryption the key or certificate backup is better secured from the moment it is created.
  • Create and backup a certificate used to encrypt the password/passphrases use in administration of the Encryption Hierarchy.
  • Encrypt and save all secrets used in the script. This means you do not need (ought never?) to save the script once your secrets are entered into the template.

The keyback database also includes some executable objects all of which are encrypted to discourage tourists. You can remove the encryption if you like before you replace the template parameter tokens.

Scalar functions to support a standard backup file naming standard

--returns the path to the .mdf of the Db but not the .mdf file name
dbo.NewBackupPath @DbName
--returns a string with embedded timestamp to uniquely identify backup files 
dbo.NewCertificateBackupName @DbName, @CertificateName
--returns a string with embedded timestamp to uniquely identify backup files 
dbo.MasterKeyBackupName @DbName

 

Scalar functions for SQL Injection filtering and elevation of authority attacks. You can flush them out in the template.

Stored procedures for saving and retrieving secrets. You can make the secrets as strong as you like ’em! I limit to NVARCHAR(128) but that can be changed easily if you need more.

-- every secret needs a name and a value
-- the @Value is stored in an encrypted columns 
-- the @Name is always used as the encryption authenticator  
dbo.AddNameValue @Name, @Value
dbo.GetValueByName @Name, @Value OUTPUT
dbo.ValidateNameValue @Name, @Value, @IsValid OUTPUT

 

Stored procedures for use in backup up any and all Master Keys and Certificates on the server.

-- @BackupPhrase encrypts the backup file
-- @KeyPhrase decrypts a certificate's private key before backup
dbo.BackupCertificate @CertificateName, @DbName, @BackupPhrase, @KeyPhrase(optional)
dbo.BackupDatabaseMasterKey @DbName, @BackupPhrase, @KeyPhrase(optional)
dbo.BackupServiceMasterKey @BackupPhrase

 

Stored procedures for use in restoring Master Keys and Certificates that have been previously backed up by the tool . This tool is intended for use only when restoring a backup taken using the tool. Keeping the backups up-to-date is quite important. The thing is, you will never know what day you will need them until the day you need one.

-- often all the information a message provides about a missing certificate is the thumbprint
dbo.CertificateBackupsByThumbprint @Thumbprint

  -- these guys are made simple to helpful in an emergency
  -- always gets the most recent backup of an object
  dbo.RestoreCertificate @CertificateName, @DbName
  dbo.RestoreDatabaseMasterKey @DbName
  dbo.RestoreServiceMasterKey

Successful Execution of the script produces output something like this. The warning message (2) is a system generated message that will only be seen when the database Encryption Key is created. If, for example, the key already exists, this message is not seen.

Typical Script Output
Protect all Encryption Hierarchy backup files and maintain a copy in a secure offsite location.

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

keyback info – Service Master Key backup complete.

keyback info – database master Master Key backup complete.

keyback info – database keyback Master Key backup complete.

keyback info – database master certificate TDECertificate backup complete.

keyback info – database keyback certificate ValueCertificate backup complete.

 

Creates backups of the newly created Encryption Hierarchy:

EncryptionHierachyBackups

And records a complete history of all actions taken or attempted (clicking the image should open it in a bigger view).

EncryptionHierarchyAdminHistory

In environments where the sysadmin membership is greater than 2 or 3, the storage location (BackupPath and BackupName) should also be encrypted. The membership in the keybackAdmin role should definitely be limited to 1 individual in all cases. Accountability is essential. Instead of two people sharing one keyback, consider having both establish separate keyback monitoring protocols. Other means to improve security when considered in the context of the local environment where the template is applied should also be considered. The stronger the layers of security around the secret store database, the better.

If you can recreate the TDE and reliably produce the keyback_ValueCertificate_CERTIFICATE_ENCRYPTION_PHRASE when needed, the likelihood that any Encryption Hierarchy Backup in the collection can be reliably restored is quite high. Under normal operation there will be no need to either recreate the TDE or manually enter the ValueCertificates’s  encryption password.

Advertisements
This entry was posted in Code Review, Encryption Hierarchies, Secure Data. Bookmark the permalink.

One Response to Encryption Hierarchy Administration – a T-SQL Template

  1. Pingback: T-SQL Cryptographic Patterns – Part 1: hashed, scrambled or over easy? | YABVE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s