T-SQL Cryptographic Patterns – part 5: running the gauntlet

I’d like to revisit the sample script for the store procedure first pasted into part 3 with the focus on the patterns demonstrated. That script is taken from an administrative utility whose business purpose is to offer forward recovery and administrative key/phrase rotation services for any and all T-SQL Encryption Hierarchies. Readers responsible for a SQL Server with Transparent Data Encryption enabled and/or T-SQL certificates or keys deployed to a live environment are in need of such functionality.

A utility is helpful to standardize and automate such processing yielding a higher probability that key recovery and rotation will succeed.

Recall that the purpose of the sample stored procedure is to export the SQL Server’s Service Master Key to a backup file. The cleverly name sample procedure, BackupServiceMasterKey is a method from a utility schema designed to:

  1. securely store secrets
  2. export T-SQL master key and certificate backups to local files
  3. securely store copies of the exports offsite

Secrets are encrypted and cataloged by purpose and version.  Secrets then are available on demand for authorized admins to:

  • open the utilities encryption hierarchies
  • recall as needed to encrypt or decrypt export files
  • restore or (re) create existing T-SQL cryptographic artifacts if necessary
  • allow automation that assures the integrity of the secure offsite location to authenticate  in the distributed key backup environment
  • non-repudiation
  • audit

In part 3 I basically threw the sample procedure out there. I envisioned using the script as a device for describing patterns and I guessed openly that the script could also be used by the reader for a quick T-SQL cryptography self-assessment. At a least I figured I could expect any reader that spent a moment or two reviewing that script to be more aware of T-SQL cryptography in their world going forward. Now I must push for more depth. If in part 3 I succeeded in getting anyone to pay just a bit more attention to T-SQL cryptography, now I hope to enable T-SQL cryptography as a comfortably usable tool.  With this aim, the coming posts offer something of a walk-through narrative of the sample. Notice that code may change so perhaps best to find the procedure in the github project (see “.git my source” in the menu) for the most recent version.

An added complexity when using real-world applications to model concepts is that the patterns tend to be woven one into another and into other application work flow into a fabric of interleaved patterns in the real world.

A facetious (academic?) example is often better when describing a fundamental concept or syntax. Such examples can be adjusted to more clearly convey a concept or practice. Books Online’s Transact-SQL Reference, for instance, is where I go to see an example and to get the right syntax. In lazy mode I will often just go to the bottom of the BOL page and use the first example as a syntax template to get me started.

An industrial sample includes subtleties of the implementation use case. The industrial sample is far more likely to convey a measure of performance and usability. This is owed in large part to the architectural fabric. However, without an adequate understanding of what the industrial application does, the complexities can be distracting when used as an example, especially when the sample is not germane to the reader’s working environment. Because the purpose of this sample is to provide best practice backup and recovery services for the T-SQL encryption hierarchy, a useful relevance to all environments is anticipated.

The schema of the sample is (partially) insulated from SQL Server permission’s hierarchies with a phrase only encrypted database Master Key. This allows for an audit enforced authentication layer that employs SQL Server database roles – more precisely rows from sys.database_rolemembers – to restrict which admins are allowed to access the data.

Encrypt DMK by phrase only (from InstallSpoke.sql):

-- see security comments in sp_control_dbmasterkey_password documention 
-- it is when a secret is passed to a procedure/function that they can sneak through
-- however in-line crypto-functions will always obfuscate in the SQL Trace
IF NOT EXISTS (SELECT * 
               FROM sys.symmetric_keys 
               WHERE symmetric_key_id = 101)
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$(EHDB_DMK_ENCRYPTION_PHRASE)';
GO
-- remove SMK encryption of the DMK - phrase encryption only
OPEN MASTER KEY DECRYPTION BY PASSWORD = '$(EHDB_DMK_ENCRYPTION_PHRASE)'
GO
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY; 
GO
ALTER MASTER KEY REGENERATE 
WITH ENCRYPTION BY PASSWORD = '$(EHDB_DMK_ENCRYPTION_PHRASE)';
GO
OPEN MASTER KEY 
DECRYPTION BY PASSWORD = '$(EHDB_DMK_ENCRYPTION_PHRASE)';
GO

This prevents any user without access to the EHDB_DMK_ENCRYPTION_PHRASE – regardless if connected to the database as dbo or guest or someone in between –  from viewing any information encrypted by the DMK rooted encryption hierarchy. The phrase must be provided to unscramble the cipher text. No elevated authority can get past that requirement. There is still that risk that the phrase will be exposed or leaked.

It is a given that encryption hierarchies rooted at respectable external authorities are theoretically more secure than SQL Server DPAPI or phrase rooted hierarchies. Storing the keys with the data is tantamount to leaving the key in the ignition and heading into the bar for a night of heavy drinking it is reasoned. Not so sure I agree. Nonetheless, it is true that Master Key and phrase rooted hierarchies – and I suppose even cars with the key in the ignition – are more accessible for malicious use and for authorized use. However, today the vast sea of data is at risk not because database cryptography is so blasted vulnerable or weak but because data is stored as clear text.

T-SQL cryptography is worthy of consideration. For most applications, the database probably is the best place for some of the cryptographic efforts to protect sensitive data. It is a defense-in-depth best practice to not put all your eggs in one basket. It is a cryptographic best practice not to use a key or certificate for more than one purpose. T-SQL cryptography can help to achieve such best practice objectives. The cost to develop and maintain database cell encryption using phrases and DPAPI rooted hierarchies is hard to beat. Data originated at the database is well suited for cryptographic protections applied at the database. Audit trails, logging tables, schema object modifications, database error messages and configuration changes often include sensitive data that could properly be protected by cell encryption at the database.

The Encryption Hierarchy Administration schema from which the sample procedure was extracted encapsulates all database cell cryptography. This is helpful for command-line and automation integration when the driver/caller process is not able to encrypt sensitive data appropriately. Many legacy applications written with stored procedures can be retrofitted with encryption of sensitive data with no or minimal changes to the application code.

The sample procedure is one of several wrapper procedures in the Encryption Hierarchy Administration schema that are systematically authenticated during execution. The procedure names are compiled into an a priori white-list that is maintained in one location: the body of a signed system encrypted stored procedure. Very important to create the procedure WITH ENCRYPTION to protect this white-list from view. All procedures in the white list acquire the authority and prove the authenticity to begin their primary application tasks by calling the stored procedure that contains the white list. Within the the called stored procedure is a conditional gauntlet that the process must navigate to re-assert the necessary authority and authenticity. This called stored procedure is named Book if you are interested to have a look in the InstallSpoke.sql. The name a reservation metaphor where valid and properly authorized users, and only when using white-listed and signed methods, book an intent to open phrase based encryption hierarchies – without actually providing the phrase – after running the gauntlet.

running the gauntlet in BackupServerMasterKey

  BEGIN TRY
    EXEC $(EHA_SCHEMA).OpenAuditKey @@PROCID, @SchemaVerified OUTPUT;
    SET @Parameters = ENCRYPTBYKEY( Key_GUID('$(AUDIT_SYMMETRIC_KEY)')
                                  , FORMATMESSAGE( '@DbName = ''%s''' 
                                               + ', @BackupPhrase = ''%s''' 
                                               + ', @KeyPhrase = ''%s''' 
                                               + ', @ForceNew = %d'
                                                 , @DbName
                                                 , @BackupPhrase
                                                 , @KeyPhrase
                                                 , @ForceNew )
                                  , 1
                                  , CAST( KEY_GUID('$(SESSION_SYMMETRIC_KEY)') AS NCHAR(36) ) );
    CLOSE SYMMETRIC KEY [$(AUDIT_SYMMETRIC_KEY)];
    EXEC $(EHA_SCHEMA).Book @@PROCID
                          , @Parameters
                          , @SchemaVerified
                          , @Id OUTPUT
                          , @MAC OUTPUT; 
    IF NOT EXISTS ( SELECT * FROM $(EHA_SCHEMA).$(BOOKINGS_TABLE) 
                    WHERE Id = @Id
                    AND KeyGuid = CAST( KEY_GUID('$(SESSION_SYMMETRIC_KEY)') AS NCHAR(36) )
                    AND ObjectName = OBJECT_NAME(@@PROCID) 
                    AND VERIFYSIGNEDBYCERT( CERT_ID('$(AUTHENTICITY_CERTIFICATE)')
                                          , CAST(CHECKSUM( Id
                                                         , @@PROCID   
                                                         , ObjectName
                                                         , @Parameters
                                                         , KeyGuid
                                                         , Status ) AS NVARCHAR(128) )
                                          , @MAC ) = 1 ) 
      RAISERROR($(MESSAGE_OFFSET)34,16,1,@@PROCID,@Id);
    IF DB_ID(@DbName) IS NULL
      RAISERROR($(MESSAGE_OFFSET)35,16,1,'Database', 'database not found');
    -- booking complete, keys open

Notice that the communication between the white-listed procedure, here BackupServiceMasterKey and the called procedure, Book is authenticated by verifying the signed schema objects in the calling white-listed procedures before the Book procedure is called and then again when control is returned to the caller. Not seen here is the third, and most hardened, schema authentication that occurs in between – within the Book procedure. (more on that later…)

The complete battery – a few hundred lines of T-SQL in the Book stored procedure – includes:

  • Verify that no objects in the schema have changed since signed at create time
  • Verify that no objects are added or removed from the schema
  • Verify that the sys.messages rows added by the application at install are unchanged
  • Check for unexpected transactions to thwart padding oracle and error message mining exploits
  • Check for unexpected notifications, logging, events and audits, alerts and CLR objects that can leak clear text values
  • Verify that the currently logged on user is the ORIGINAL_LOGIN() and is authorized to use the schema. Authorization is enforced for all users including sysadmin and dbo
  • Validate all expected certificates and the “portable” symmetric key used by the utility to render error message viewable elsewhere in the data center
  • Verify that the caller is in the white list
  • Look for specific SQL Instance configuration option states

Both the caller and called procedure contribute ingredients for a hashed message authentication code (@MAC). The @MAC will also be saved to an activity history log row that is foreign keyed to the booking table when booked – or failed to book. Unauthorized requests are captured by the booking system before they are shown the trap door: a little T-SQL honeypot if you will.

The Book stored procedure from InstallSPoke.sql

-------------------------------------------------------------------------------
--    bwunder at yahoo dot com
--    Desc: authentication gauntlet    
--    ASSERT: the caller has opened the Database Master Key  
--    by rule the 0 row colophon is a checksum of sys.messages after install
--    add configuration filtering as appropriate. Be precise.
-------------------------------------------------------------------------------
CREATE PROCEDURE $(EHA_SCHEMA).Book 
  ( @ProcId INT 
  , @Parameters VARBINARY(8000)
  , @SchemaVerified BIT
  , @Id NCHAR(36) OUTPUT
  , @MAC VARBINARY(128) OUTPUT )
$(WITH_OPTIONS)
AS
BEGIN
DECLARE @Thumbprint VARBINARY(32)
      , @Reason NVARCHAR(30)
      , @ErrorData VARBINARY(8000);
  BEGIN TRY
    SET @Reason = 'objects';
    SET @Thumbprint = ( SELECT thumbprint
                        FROM sys.certificates 
                        WHERE name = '$(OBJECT_CERTIFICATE)' )
    -- maintain in lock step with schema changes and with CheckSig function
    -- by not using function here the scalar boolean function is verified 
    -- with minimal code, low cost to query sys.objects by id
    IF (SELECT @SchemaVerified                                        
        FROM sys.certificates c
        OUTER APPLY sys.fn_check_object_signatures ('CERTIFICATE', c.thumbprint) s
        INNER JOIN sys.objects o
        ON s.entity_id = o.object_id
        WHERE c.name = '$(OBJECT_CERTIFICATE)'
        AND c.pvt_key_encryption_type = 'PW'
        AND o.schema_id = SCHEMA_ID( '$(EHA_SCHEMA)' ) 
        AND (o.parent_object_id = 0 OR o.type = 'TR')       
        HAVING SUM(ISNULL(s.is_signature_valid,0) ) = $(OBJECT_COUNT) - $(TABLE_COUNT)  
        AND SUM ( CASE WHEN o.type = 'TR' THEN 1 ELSE 0 END ) = $(TABLE_COUNT) - 1 ) <> 1
      RAISERROR($(MESSAGE_OFFSET)30,16,1);
    SET @Reason = 'messages';
    -- text of any of our sys.messages is changed from the same value computed at install 
    -- in and stored in the colophon of the 0 row or the 0 row does not exist
    IF ( SELECT CHECKSUM_AGG( BINARY_CHECKSUM(text) )  
          FROM sys.messages
          WHERE message_id between $(MESSAGE_OFFSET)00 AND $(MESSAGE_OFFSET)50 ) 
         <>
        ( SELECT Colophon
          FROM $(EHA_SCHEMA).$(BACKUP_ACTIVITY_TABLE)  
          WHERE Id = '00000000-0000-0000-0000-000000000000' ) 
      RAISERROR($(MESSAGE_OFFSET)30,16,1);
    SET @Reason = 'probe';
    IF XACT_STATE() <> 0
      RAISERROR($(MESSAGE_OFFSET)31,16,1);
    SET @Reason = 'bridge'; -- a check for unexpected data exposures 
    IF EXISTS ( SELECT * 
                FROM sys.event_notifications
                WHERE name <> '$(EVENT_NOTIFICATION)Db' )
    OR EXISTS ( SELECT * 
                FROM sys.server_event_notifications 
                WHERE name NOT IN ( '$(EVENT_NOTIFICATION)Srv', 'SQLClueDDLEventNotification' ) )
    OR EXISTS ( SELECT * 
                FROM sys.triggers 
                WHERE is_ms_shipped = 0 
                AND OBJECT_SCHEMA_NAME(object_id) = '$(EHA_SCHEMA)'
                AND ( name NOT IN ( 'trg_$(BOOKINGS_TABLE)'               -- eha trigger whitelist (also in list below) 
                                  , 'trg_$(NAMEVALUES_TABLE)'             -- here check for unexpected triggers
                                  , 'trg_$(NAMEVALUE_ACTIVITY_TABLE)'      -- below check that all are signed
                                  , 'trg_$(CONTAINERS_TABLE)'
                                  , 'trg_$(CONTAINER_ACTIVITY_TABLE)'
                                  , 'trg_$(BACKUP_ACTIVITY_TABLE)'
                                  , 'trg_$(NOTIFICATION_ACTIVITY_TABLE)'
                                  , 'trg_$(OFFSITE_ACTIVITY_TABLE)'
                                  , 'trg_$(REPORT_ACTIVITY_TABLE)'
                                  , 'trg_ddl_$(EHDB)'
                                  , 'trg_trg_$(EHDB)' ) 
                      OR IS_OBJECTSIGNED( 'OBJECT'
                                        , object_id
                                        , 'CERTIFICATE' 
                                        , @Thumbprint ) <> 1 ) ) 
    OR EXISTS (SELECT * FROM sys.server_triggers
               WHERE is_ms_shipped = 0) 
    -- all secrets should be obfuscated in any trace events but better safe than sorry
    -- the automagic obfuscation proved inadequate in masking secrets when passed as 
    -- clear text in user defined stored procedure parameters, thus secrets are passed
    -- only as encrypted parameters. The install script produced more than 192,000 trace 
    -- events with TextData that were scrutinized for secret leaks. None leaks identified. 
    OR EXISTS (SELECT * FROM sys.traces 
               WHERE is_default <> 1)
    -- SQLAudit EVENTDATA is supposed to be obfuscated automajically but better safe than sorry
    OR EXISTS (SELECT * FROM sys.dm_xe_sessions
               WHERE name NOT IN ( 'system_health', 'sp_server_diagnostics session' )
               AND name NOT LIKE 'ehaSchemaAudit%' )
    OR EXISTS (SELECT * FROM sys.database_audit_specifications
               WHERE name <> 'ehaSchemaAuditDbSpecs' )
    OR EXISTS (SELECT * FROM sys.server_audits
               WHERE name <> 'ehaSchemaAudit' )
    OR EXISTS (SELECT * FROM msdb.dbo.sysalerts
               WHERE Name NOT LIKE 'SQLClue:%') -- filter all valid config that gets caught in gauntlet 
    OR EXISTS (SELECT * FROM sys.assemblies 
               WHERE name NOT IN ( 'Microsoft.SqlServer.Types', 'EHAdmin' ) )          
      RAISERROR($(MESSAGE_OFFSET)32, 16, 1);
    SET @Reason = 'authority';
    -- role membership is not recognized 
    -- sysadmin is always dbo so catch dbo to catch both 
    IF NOT EXISTS ( SELECT * 
                    FROM sys.database_role_members
                    WHERE role_principal_id = USER_ID('$(EHADMIN_ROLE)')
                    AND SYSTEM_USER = ORIGINAL_LOGIN()
                    AND USER_NAME(member_principal_id) = SYSTEM_USER   
                    AND SESSION_USER = 'dbo' )
    AND USER_NAME() <> 'cdc'                 
      RAISERROR($(MESSAGE_OFFSET)35, 16, 1, 'USER', @Reason);
    SET @Reason = 'DMK';
    IF EXISTS (SELECT * FROM sys.master_key_passwords)
    OR EXISTS (SELECT * 
               FROM sys.symmetric_keys sk 
               JOIN sys.key_encryptions ke 
               ON sk.symmetric_key_id = ke.key_id 
               WHERE sk.name = '##MS_DatabaseMasterKey##'
               AND ke.crypt_type <> 'ESKP' )
      RAISERROR($(MESSAGE_OFFSET)35,16,1,'MASTER KEY', @Reason);
    SET @Reason = 'cert'
    IF (SELECT pvt_key_encryption_type 
        FROM sys.certificates
        WHERE name = '$(FILE_CERTIFICATE)') <> 'MK'
    OR (SELECT pvt_key_encryption_type 
        FROM sys.certificates
        WHERE name = '$(NAME_CERTIFICATE)') <> 'MK'
    OR (SELECT pvt_key_encryption_type 
        FROM sys.certificates
        WHERE name = '$(VALUE_CERTIFICATE)') <> 'MK'
    OR (SELECT pvt_key_encryption_type 
        FROM sys.certificates
        WHERE name = '$(OBJECT_CERTIFICATE)') <> 'PW'
    OR (SELECT pvt_key_encryption_type 
        FROM sys.certificates
        WHERE name = '$(AUTHENTICITY_CERTIFICATE)') <> 'MK'
    OR (SELECT pvt_key_encryption_type 
        FROM sys.certificates
        WHERE name = '$(AUDIT_CERTIFICATE)') <> 'PW'
    OR NOT EXISTS (SELECT * 
                   FROM sys.symmetric_keys sk 
                   JOIN sys.key_encryptions ke 
                   ON sk.symmetric_key_id = ke.key_id 
                   WHERE sk.name = '$(ERROR_SYMMETRIC_KEY)'
                   AND ke.crypt_type = 'ESKP' ) -- Encryption by Password
       RAISERROR($(MESSAGE_OFFSET)35,16,1,'cert', @Reason);
    SET @Reason = 'whitelist'; 
    IF ( OBJECT_NAME(@ProcId) NOT IN ( 'AddNameValue'
                                     , 'BackupCertificate' 
                                     , 'BackupContainer' 
                                     , 'BackupDatabaseMasterKey' 
                                     , 'BackupServiceMasterKey' 
                                     , 'CertificateBackupsByThumbprint' 
                                     , 'GetPortableSymmetricKey'
                                     , 'GetPrivateValue'
                                     , 'MakeSalt' 
                                     , 'OpenAuditKey'
                                     , 'RecallContainer' 
                                     , 'ReportActivityHistory' 
                                     , 'ReportErrors' 
                                     , 'ReportServerSummary'
                                     , 'RestoreCertificate' 
                                     , 'RestoreContainer' 
                                     , 'RestoreDatabaseMasterKey' 
                                     , 'RestoreServiceMasterKey' 
                                     , 'SavePortableSymmetricKey'
                                     , 'SavePrivateValue'
                                     , 'SendOffsiteCDC' 
                                     , 'SendOffsiteTC' 
                                     , 'SelectNameValue' 
                                     , 'ValidateNameValue' 
                                     , '$(EVENT_NOTIFICATION)Activation'
                                     , 'trg_$(BOOKINGS_TABLE)'
                                     , 'trg_$(CONTAINERS_TABLE)'
                                     , 'trg_$(CONTAINER_ACTIVITY_TABLE)'
                                     , 'trg_$(NAMEVALUES_TABLE)'
                                     , 'trg_$(NAMEVALUE_ACTIVITY_TABLE)'
                                     , 'trg_$(BACKUP_ACTIVITY_TABLE)'
                                     , 'trg_$(NOTIFICATION_ACTIVITY_TABLE)'
                                     , 'trg_$(OFFSITE_ACTIVITY_TABLE)'
                                     , 'trg_$(REPORT_ACTIVITY_TABLE)' ) )
    OR OBJECT_SCHEMA_NAME(@ProcId) <> '$(EHA_SCHEMA)' 
    OR (SELECT 1                                                  -- maintain in lock step with schema changes
        FROM sys.objects t                                        -- and with CheckSig function used by callers
        WHERE OBJECT_SCHEMA_NAME( t.object_id ) = '$(EHA_SCHEMA)' -- by not using the CheckSig function here the 
        AND (parent_object_id = 0 OR type = 'TR')                 -- validity of the function is assured.
        HAVING COUNT(*) = $(OBJECT_COUNT)
        AND SUM (IS_OBJECTSIGNED( 'OBJECT', t.object_id, 'CERTIFICATE', @Thumbprint ) )  = $(TABLE_COUNT)
        AND SUM ( CASE WHEN type = 'SN' THEN 1 ELSE 0 END ) = $(TABLE_COUNT)
        AND SUM ( CASE WHEN type = 'TR' THEN 1 ELSE 0 END ) = $(TABLE_COUNT) ) <> 1           
      RAISERROR($(MESSAGE_OFFSET)35,16,1,'@ProcId', @Reason);    
    SET @Reason = 'config';
    IF SESSIONPROPERTY('ANSI_PADDING') <> 1
    OR EXISTS ( SELECT * FROM sys.databases
                WHERE database_id = DB_ID()
                AND (   is_trustworthy_on = 1
                    OR is_db_chaining_on = 1
                    OR is_ansi_padding_on = 1) )                   
          RAISERROR($(MESSAGE_OFFSET)37,16,1);
    SET @Reason = 'insert';
    -- assure this is last insert until Id is fetched
    BEGIN TRANSACTION
      INSERT INTO $(EHA_SCHEMA).$(BOOKINGS_TABLE) WITH(TABLOCK, HOLDLOCK)
        ( ProcId
        , ObjectName
        , Parameters
        , KeyGuid
        , Status )
      VALUES ( @ProcId   
             , OBJECT_NAME(@ProcId)
             , @Parameters
             , CAST( KEY_GUID('$(SESSION_SYMMETRIC_KEY)') AS NCHAR(36) )
             , 'OK');
      SET @Reason = 'sign';
      SELECT @Id = Id 
           , @MAC = MAC
      FROM ( SELECT TOP(1) Id
                         , SignByCert( CERT_ID('$(AUTHENTICITY_CERTIFICATE)')
                                     , CAST(CHECKSUM( Id
                                                    , ProcId   
                                                    , ObjectName
                                                    , Parameters
                                                    , KeyGuid
                                                    , Status ) AS NVARCHAR(128) ) ) MAC
             FROM $(EHA_SCHEMA).$(BOOKINGS_TABLE)
             WHERE KeyGuid = CAST( KEY_GUID('$(SESSION_SYMMETRIC_KEY)') AS NCHAR(36) ) 
             ORDER BY Id DESC ) AS derived;  
    COMMIT TRANSACTION;      
    SET @Reason = 'keys';
    -- First need for DMK - use DMK encrypted authenticity certificate to make the MAC 
    -- it remains open until the first of the @MAC is saved by caller or the session ends
    IF NOT EXISTS ( SELECT * FROM sys.openkeys
                    WHERE key_name = '##MS_DatabaseMasterKey##'
                    AND database_name = DB_NAME() )
      OPEN MASTER KEY DECRYPTION BY PASSWORD = '$(EHDB_DMK_ENCRYPTION_PHRASE)';
    -- open data column keys
    IF NOT EXISTS ( SELECT * FROM sys.openkeys
                    WHERE key_name = '$(FILE_SYMMETRIC_KEY)'
                    AND database_name = DB_NAME() )
      OPEN SYMMETRIC KEY [$(FILE_SYMMETRIC_KEY)]
      DECRYPTION BY CERTIFICATE [$(FILE_CERTIFICATE)];
    IF NOT EXISTS (SELECT * FROM sys.openkeys
                   WHERE key_name = '$(NAME_SYMMETRIC_KEY)')
      OPEN SYMMETRIC KEY [$(NAME_SYMMETRIC_KEY)]
      DECRYPTION BY CERTIFICATE [$(NAME_CERTIFICATE)];
    IF NOT EXISTS (SELECT * FROM sys.openkeys
                   WHERE key_name = '$(VALUE_SYMMETRIC_KEY)')
      OPEN SYMMETRIC KEY [$(VALUE_SYMMETRIC_KEY)]
      DECRYPTION BY CERTIFICATE [$(VALUE_CERTIFICATE)];
  END TRY
  BEGIN CATCH
    IF ERROR_NUMBER() <> $(MESSAGE_OFFSET)31
      ROLLBACK TRANSACTION;
    OPEN SYMMETRIC KEY $(ERROR_SYMMETRIC_KEY)
    DECRYPTION BY PASSWORD = '$(ERROR_KEY_ENCRYPTION_PHRASE)';
    -- handle insert or signing excptns without a dup key ( an incomplete booking )
    INSERT INTO $(EHA_SCHEMA).$(BOOKINGS_TABLE) 
      ( ProcId
      , ObjectName
      , Parameters
      , KeyGuid
      , Status
      , ErrorData )
    SELECT @ProcId
         , OBJECT_NAME(@ProcId)
         , @Parameters
         , CAST( KEY_GUID('$(SESSION_SYMMETRIC_KEY)') AS NCHAR(36) )
         , ISNULL(@Reason,'Error') 
         , ENCRYPTBYKEY( KEY_GUID('$(ERROR_SYMMETRIC_KEY)')
                       , ErrorInfo 
                       , 1
                       , @Id )
    FROM (SELECT FORMATMESSAGE( $(MESSAGE_OFFSET)02
                              , ERROR_NUMBER()
                              , ERROR_SEVERITY()
                              , ERROR_STATE()
                              , ISNULL(ERROR_PROCEDURE(), 'ad hoc')
                              , ERROR_LINE()
                              , ERROR_MESSAGE() ) AS ErrorInfo ) as derived;
    CLOSE SYMMETRIC KEY $(ERROR_SYMMETRIC_KEY);
    IF @@NESTLEVEL > 1
      THROW;
  END CATCH
END
GO
ADD SIGNATURE TO $(EHA_SCHEMA).Book 
BY CERTIFICATE $(OBJECT_CERTIFICATE)
WITH PASSWORD = '$(OBJECT_CERTIFICATE_ENCRYPTION_PHRASE)';
GO

Of course all this must happen quickly with minimal impact to system resources.  The system catalog and other memory resident SQL Server values are exploited as much as possible during the running of the gantlet. If a white list procedure survives the gauntlet – and I would expect this to be the rule not the exception for the white list procedures – the phrase encrypted keys and certificates needed to complete the primary task are opened and a row is inserted in a Bookings table. Without the Bookings row the primary operation will not be attempted. In all cases the attempt should be recorded.

The Bookings row includes a cipher text column containing the parameter values (@Parameters) passed to the white listed stored procedure. The parameters are encrypted into the cipher text column using a symmetric key that can be controlled by a person or group other than the DBA or a subgroup of DBAs. In fact the DBA can be excluded from using any key or the certificate used to encrypt the key into the database if desired. Just don’t give ’em the phrase. The certificate is encrypted into the database by a user supplied phrase. Note that any automated build processing must support and honor said separation of responsibilities.

Perhaps you noticed that the first operation in the sample stored procedure assigns a value to the @Parameters local variable? @Parameters is a VARBINARY(8000) cipher text value encrypted by the AUDIT_SYMMETRIC_KEY. The assignment operation demonstrates a fundamental T-SQL method for alignment of encryption key ownership and division of responsibility business rules. The value will be persisted in the Bookings row after the gauntlet is run within the Book procedure’s execution. Later, persons with knowledge of the phrase can open the @Parameters.Others that view the row will see only the cipher text.

Each white-listed procedure is tooled to collect it’s own audit trail in this way and commit a row to the Bookings table before completing a primary task. A symmetric key under the (optionally exclusive) control of an auditing authority i.e, Accounting, Legal, Security Officer, etc. and dedicated to encrypting passed parameters is used to protect the audit trail from DBAs and data readers alike.

This audit style also implicitly enables on demand identification of unauthorized ad hoc data modifications by the absence of audit trail entries to defend the current state of the data. Not the best in data access monitoring but a useful failsafe. Simply find the booking row when the value was last touched and decrypt the @parameters column. Should match what is in the data table.

Notice that the GUID of a session bound temporary symmetric key is used as the @authenticator of the symmetric encryption method to protect against substitution and dictionary attacks. Since that session bound key will disappear when the SPID disconnects from the database it is important to save the Key’s GUID near the @Parameter value for use during any subsequent requirement to review or verify the values passed.  Using the GUID bound to the session removes any hacking benefit from  guessing the next GUID value that NEWID() or NEWSEQUENTIALID() might spew. It also allows for the GUID to be reused in all of the few possible white list invocation in the session with a common value to give us a preferred blend of uniqueness and randomness from the @authenticator as discussed in part 4.

The AUDIT_SYMMETRIC_KEY is encrypted into the database by a certificate. In turn the certificate is encrypted into the database using a phrase. This establishes the certificate’s phrase as the root of the encryption hierarchy. A sysadmin or dbo can drop the key or change the phrase but they cannot see the encrypted data as clear text without that phrase. Someone might steal the phrase but would still need to be able to open the database Master Key assuming they were able to hijack the encrypted data or backup files!

a translucent symmetric key from InstallSpoke.sql

-- audit certificate is independent FROM DMK 
IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = '$(AUDIT_CERTIFICATE)') 
  CREATE CERTIFICATE $(AUDIT_CERTIFICATE) 
  ENCRYPTION BY PASSWORD = '$(AUDIT_CERTIFICATE_ENCRYPTION_PHRASE)' 
  WITH SUBJECT = 'Encryption Hierarchy Administrator Audit Trail'; 
-- audit key is sticky to the database 
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '$(AUDIT_SYMMETRIC_KEY)') 
  CREATE SYMMETRIC KEY $(AUDIT_SYMMETRIC_KEY) 
  WITH ALGORITHM = $(AUDIT_KEY_ENCRYPTION_ALGORITHM) 
  ENCRYPTION BY CERTIFICATE $(AUDIT_CERTIFICATE);

The configuration carries a formidable defense-in-depth capability. The symmetric key can only exist in the current database or a copy of the current database. The certificate can only be opened with it's encryption phrase. The symmetric key can only be used when the certificate is open and both are in the current database. When the cipher text is stored using TDE or file system encryption there is some overlap of protection – and consequently an on overlap of ownership boundaries – that requires at least a triangular cooperation among trusted resources to render the cipher text as clear text.

The OpenAuditKey stored procedure verifies the schema authenticity by checking that all objects are properly signed. If there is any discrepancy in the number of signed objects versus an expected count, any unsigned and unexpected objects in the schema or if the current user is not enrolled in the application, the OpenAuditKey procedure will return a value indicating a failure. The key will still be opened so that the audit record can be created before the white list procedure is later aborted – if @SchemaVerified is not 1 when the real work begins.

OpenAuditKey stored procedure from InstallSpoke.sql

CREATE PROCEDURE $(EHA_SCHEMA).OpenAuditKey
  ( @ProcId INT 
  , @SchemaVerified BIT OUTPUT )
$(WITH_OPTIONS)
AS
BEGIN
  IF @@NESTLEVEL > 1
    SET @SchemaVerified = $(EHA_SCHEMA).VerifySchema(@ProcId);
  OPEN SYMMETRIC KEY [$(AUDIT_SYMMETRIC_KEY)]
  DECRYPTION BY CERTIFICATE [$(AUDIT_CERTIFICATE)]
  WITH PASSWORD = '$(AUDIT_CERTIFICATE_ENCRYPTION_PHRASE)';
END
GO
ADD SIGNATURE TO $(EHA_SCHEMA).OpenAuditKey 
BY CERTIFICATE $(OBJECT_CERTIFICATE)
WITH PASSWORD = '$(OBJECT_CERTIFICATE_ENCRYPTION_PHRASE)';

As a result, it does not matter even that the current user is a member of the sysadmin role or the dbo role or otherwise unprivileged. Without knowledge of the phrase the key cannot be used to un-cloak any cipher text.

The use of a called stored procedures to open the keys and certificates removes developer need to know from the phrases. The procedure also provides a convenient container that can be used for any key management, phrase rotations, division of responsibility and build processing.

The called procedure must be created using the WITH ENCRYPTION option in live environments. The goal of the verify is to perform the deepest check for evidence of unauthorized use possible without adding unnecessary CPU cycles or even necessarily failing the thread of execution. We already know that the cryptography will add CPU overhead. Here the signature verification for all objects in the schema queries the system catalog. This is intended to assure consistently lightweight and  speedy execution.

The VerifySchema function from InstallSpoke.sql

CREATE FUNCTION $(EHA_SCHEMA).VerifySchema 
  ( @ObjectId INT )
RETURNS BIT
$(WITH_OPTIONS)
AS
BEGIN 
  RETURN (SELECT CASE WHEN COUNT(*) = $(OBJECT_COUNT) - $(TABLE_COUNT) 
                      THEN 1 ELSE 0 END
          FROM sys.certificates c
          CROSS APPLY sys.fn_check_object_signatures ( 'certificate'
                                                     , c.thumbprint) s
          WHERE c.name = '$(OBJECT_CERTIFICATE)'
          AND c.pvt_key_encryption_type = 'PW'
          AND OBJECT_SCHEMA_NAME (entity_id) = '$(EHA_SCHEMA)'
          AND EXISTS (SELECT *              
                      FROM sys.database_role_members
                      WHERE role_principal_id = USER_ID( '$(EHADMIN_ROLE)' )
                      AND SYSTEM_USER = ORIGINAL_LOGIN()
                      AND USER_NAME(member_principal_id) = ORIGINAL_LOGIN() )
          AND s.is_signed = 1 
          AND s.is_signature_valid = 1 );    
END
GO
ADD SIGNATURE TO $(EHA_SCHEMA).VerifySchema 
BY CERTIFICATE $(OBJECT_CERTIFICATE)
WITH PASSWORD = '$(OBJECT_CERTIFICATE_ENCRYPTION_PHRASE)';
GO

Notice that the VerifySchema function relies upon hard-coded values provided at install. Hard-coding is normally undesirable because of resulting reduced maintainability. In this scenario the static values assure that no schema objects have been added, removed or modified in the schema since the procedure was created. Furthermore, the static filter expression assures that the certificate is signed by a phrase as opposed to the DMK or an external key manager. Without the certificate’s password and without grinding out another signature digest to compare to the existing signature, the function is able to elevate confidence that the current SPID is – or is not – a valid execution instance without failing the SPID. Keeping the invalid SPID alive allows the harvest of parameters and other in-row audit details (DateTime, UserId, etc.) and full control of what – if any – information will be returned to the caller.

Running the gauntlet is a pattern that involves cryptography rather than a cryptographic pattern. A pattern of authentication that is not bound to the SQL Server security hierarchy. A pattern of proactive schema authentication. A pattern to assure the integrity of important application events and sensitive data in the data store.

Not all data needs gauntlet level data protection and overhead. Only the stuff that is important enough to encrypt, sign or hash.

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

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