T-SQL Cryptographic Patterns – part 3: Dog Food

The intention in part 1 and part 2 was primarily to convey a fundamental understanding of SQL Server’s encryption technology to an unknown audience. The biggest problem is that there is always something else to say. While I am thankful for the readers of this blog, I cannot be certain I have provided an adequate context to begin looking for meaningful patterns. I find some solace in the notion that no matter what experience and knowledge an individual may have with this topic – as with most current computing technologies – there is always something new and different to know. It is keeping up with the change that is most likely to assure successful implementations – where ever the change will take us next.

The fundamental responsibility for the DBA during design and development usually evolves to includes participation in multiple strategic initiatives to architect and implement the organizational computing environment. The development DBA gets assignments through the project charter(s) and the shared time-line. This is directly dependent upon the organizational context and the tenacity of the DBA. In the range of development contexts I am familiar with, there are more differences than similarities among them. I am however convinced that to the extent the data layer elements of the strategy are not adequately considered or provisioned during design and implementation, the DBA can anticipate the highly visible role of fire-fighter and ad hoc query master once the application goes live. I’ll leave it to you to measure the impact and value of fire-fighting and ad hoc queries in your environment(s) as well as the relative benefits to your career of high DBA visibility within the organization. In my experience, fir-fighting is the most common point at which most organizations bring on a DBA and the selected DBA must be an adept database firefighter and ad hoc query master. I give highest priority to eliminating fire-fighting and ad hoc queries in live environments as quickly and thoroughly as humanly possible. Only then am I truly able to chip away at the flaming interruptions and time consuming one-off queries and put my energy into becoming useful to the development team(s) in reaching decisions earlier in the process that do not lead to additional fire-fighting and increased ad hoc query requirements. Any scenario becomes self-effacing over time, though with patience and perseverance you can expect to eventually get your life back only by eliminating unplanned work.

For inspiration when in fire-fighting mode I highly recommend: Cheyenne Autumn (ISBN:978-0803292123) by Mari Sandoz (1896-1966).  Probably have to buy it used…

To sharpen your query mastery attend your local SQL Server user group regularly and bring a succinct T-SQL problem with you each time. Second best – much faster but far more hit-or-miss – option would be to Google MSDN  on the matter. There are a ton of other good forums too – most way faster than MSDN. I can’t help but wonder how many of the forum experts would be available if they also had the option to walk or mountain bike or golf or work-out or shoot pool or play video games with the free time once they have taken ownership of the fire-fighter and/or query master role or evolved their job into a mostly live environment support scenario? Just wondering… No idea what the truth is on that one is for anyone other than myself.     

Once the design moves to a live environment the DBA role is quite different and be more routine. The DBA’s mandate transmogrifies – excuse me for ‘shoulding’ on you, at least should morph in a perfect world – to become support of the product delivered by the sum total of the development effort. And of course without taking the database server offline in the process or degrading performance.

More often what happens is that maintenance of the live environment is added to the role of the one-size-fits-all DBA. It is easier to predict what a live environment DBA will be doing than what amount of his time can be given to those tasks.

For example. I see the live environment DBAs tasks as including:

  1. Support and Maintain the Organization’s Network Security Strategy.
    1. SQL Server Logins
    2. SQL Server Credentials
    3. Database Users
    4. Database Permissions
    5. SQL Instance Configuration
    6. SQL Server Encryption Hierarchy(ies)
    7. Storage Encryption
    8. Patches and Upgrades
    9. Development Releases to live environment(s)
  • Support and Maintain the Organization’s High Availability Strategy.
    1. Backups
    2. Replication
    3. Mirroring
    4. Log Shipping
    5. Window’s Clusters
    6. Delete/Archive
    7. Post-Production Feeds
  • Monitor for Performance, Exceptions, and Growth/Scalability.
    1. SSMS
    2. BIDS/Visual Studio 
    3. SQL ErrorLog
    4. DMVs
    5. SQL Trace
    6. Windows Performance Monitor
    7. Windows Event Logs
    8. ad hoc Queries
  • Prioritize and Tune for Optimum Database Performance.
    1. Indexing
    2. Query refactoring
    3. Platform Bottlenecks
    4. SQL Configuration Utility
  • Most of the time when I have looked for DBA tools in the 3rd party marketplace it is because I wasn’t getting everything done, not because it was an improvement over T-SQL. However, I have worked with DBA’s that shun TSQL. ?

    The last thing the organization wants from the point of going live forward is to have to redesign any part of the application’s code base. It is not a good place to put your boss in when he must turn the budget meeting topic toward “what we really needed to do was…”. The DBA’s onus is first to investigate, understand and tune query performance;  automate suitable monitoring;  maintain and rehearse the identified forward recovery tactics; support n business analysts, a data warehouse architect, and n simultaneous application maintenance iterations as provided by development and n new development projects. For better or worse, too many applications do not include a data layer perspective in the design process because there just isn’t enough DBA to go around. That is further complicated these days by the just-in-time nature of the ubiquitous agile process and the still rigid ORM database interface requirements. The DBA must operate always in learning mode and ever under, but with full awareness of – the ORM’s abstraction. That combination assures that T-SQL will remain indispensible to the SQL Server DBA for the foreseeable future, even if the application speaks only in some translation to SQL. 

    Cryptography is something of a come-lately to T-SQL. SQL Azure doesn’t even do cryptography… yet… The best way to learn about T-SQL cryptography is to use it. Sound familiar? Using the T-SQL implementation of a technology is one of the ways I prefer to learn the technology. I have to admit, I do almost always find advantages with T-SQL DBA tools over Microsoft’s UI’s and the many fine 3 party user interfaces. Not unlike my long standing preference for application data access via stored procedures or building a tested rollback script before applying any change script to a shared database. Occasionally those things don’t matter, but when they do, they really matter. My infantile Encryption Hierarchy Administration T-SQL tool is an example of spending too much of my time learning about the ins and outs of T-SQL encryption. (I’m currently working on getting the backup to and restore local from an ODBC linked SQL Azure offsite during forward recovery to work the way I want it to work.) With the exception of the one ODBC DSN documented but not included in the scripts, the current scripts checked into the github are 100% TSQL using a SQLCMD connection for the install script, 50 or so lines of T-SQL for the uninstall script and a few rdl reports that use embedded TSQL queries so that they can be used in SSMS, VS/BIDS, or deployed to a Report Server.

    Most of the encryption patterns that will be considered in subsequent posts in the series are used during the execution of the stored procedure that backs up the Service Master Key (SMK). Administration of the SMK key is something that needs to be done on every SQL Server instance so would seem a widely interesting introduction to the patterns. Reader’s comfortable with the syntax and usage in the stored procedure are well prepared to continue on with the series. It will be useful to refer back to this script in subsequent pots to get a context for the patterns described. Conversely, reader’s that cannot make heads or tails of this stored procedure may want to invest the time required to review this procedure line by line in consultation with the SQL Server 2012 RC0 online documentation. This will ultimately save time if you intend to stay with me through this series of posts and/or improve your T-SQL cryptographic skills.

    Please keep in mind this represents my latest iteration of eating my own dog food. I don’t expect you to eat any. I am very interested in your feedback. The complete install script can be found in my github EHAdmin repository. A snapshot of script for the stored procedure is posted here for you’re your convenience.

    BackupServiceMasterKey Stored Procedure From InstallSpoke.sql

    -------------------------------------------------------------------------------
    --    bwunder at yahoo dot com
    --    Desc: backup the service master key of the SQL Server Instance
    -------------------------------------------------------------------------------
    CREATE PROCEDURE $(EHA_SCHEMA).BackupServiceMasterKey 
     ( @BackupPhrase VARBINARY(8000) 
     , @UseHash TINYINT = 0    -- use Name (0) or NameBucket (1) as the file name
     , @ForceNew TINYINT = 0 ) -- backup even if crypto object already in archive
    $(WITH_OPTIONS)
    AS
    BEGIN 
    DECLARE @ActionType NVARCHAR(10) = 'Backup'
          , @BackupDDL NVARCHAR(MAX)
          , @BackupName VARBINARY(8000)
          , @BackupNameBucket INT
          , @BackupPath VARBINARY(8000)
          , @BackupPhraseName NVARCHAR(448)
          , @BackupPhraseVersion SMALLINT
          , @CipherType NCHAR(2)
          , @Colophon INT
          , @DbName NVARCHAR(128) = 'master'
          , @Edition SMALLINT
          , @ErrorData VARBINARY(8000)  
          , @Id NCHAR(36)
          , @MAC VARBINARY(128) 
          , @Node HIERARCHYID
          , @NodeName NVARCHAR(128) = 'Service Master Key'
          , @ObjectInfoDDL NVARCHAR(512)
          , @Parameters VARBINARY(8000)
          , @ParentName NVARCHAR(128) = 'root'
          , @ReturnCode INT
          , @SchemaVerified BIT
          , @tvp NAMEVALUETYPE;
    SET NOCOUNT ON;
      BEGIN TRY
        EXEC $(EHA_SCHEMA).OpenAuditKey @@PROCID, @SchemaVerified OUTPUT;
        SET @Parameters = ENCRYPTBYKEY( Key_GUID('$(AUDIT_SYMMETRIC_KEY)' )
                                      , FORMATMESSAGE( '@BackupPhrase = ''%s''' 
                                                     + ', @UseHash = %d' 
                                                     + ', @ForceNew = %d'
                                                     , @BackupPhrase
                                                     , @UseHash
                                                     , @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);
        -- DMK and File, Name & Value certs opened by successful book so OK to set encrypted values now 
        SET @Node = $(EHA_SCHEMA).GetNode ( @NodeName, @DbName, @@SERVERNAME )
        SET @BackupName = $(EHA_SCHEMA).NewMasterKeyBackupName( @NodeName );
        SELECT @BackupNameBucket =  $(EHA_SCHEMA).AddSalt( '$(EHDB)'
                                                         , '$(EHA_SCHEMA)'
                                                         , '$(BACKUP_ACTIVITY_TABLE)'
                                                         , 'BackupNameBucket' 
                                                         , Word )
        FROM ( SELECT CAST( DECRYPTBYKEY( @BackupName ) AS NVARCHAR(128) ) AS Word ) AS derived;
        SET @BackupPath = $(EHA_SCHEMA).BackupPath(@DbName);
        SET @ObjectInfoDDL = FORMATMESSAGE( $(MESSAGE_OFFSET)22
                                          , FORMATMESSAGE( $(MESSAGE_OFFSET)21
                                                         , '$(EHDB)'
                                                         , '$(EHA_SCHEMA)'
                                                         , '$(BACKUP_ACTIVITY_TABLE)'
                                                         , 'Colophon'
                                                         , 'key_guid' ) 
                                          ,'##MS_ServiceMasterKey##' );
        EXEC @ReturnCode = sp_executesql @ObjectInfoDDL
                                       , N'@CipherType NCHAR(2) OUTPUT, @Colophon INT OUTPUT'
                                       , @CipherType OUTPUT
                                       , @Colophon OUTPUT;
        IF (SELECT TOP(1) Colophon
            FROM $(EHA_SCHEMA).$(BACKUP_ACTIVITY_TABLE) 
            WHERE ServerName = @@SERVERNAME
            AND Action = OBJECT_NAME(@@PROCID)
            AND Status = 'Complete'
            ORDER BY CreateUTCDT DESC ) = @Colophon   
          BEGIN
            IF @ForceNew <> 1
              RAISERROR($(MESSAGE_OFFSET)38, 16, 1, @DbName, @NodeName ); 
            ELSE
              SET @Edition = (SELECT MAX(Edition) + 1
                              FROM  $(EHA_SCHEMA).$(BACKUP_ACTIVITY_TABLE) 
                              WHERE ServerName = @@SERVERNAME
                              AND Action = OBJECT_NAME(@@PROCID)
                              AND Status = 'Complete'
                              AND Colophon = @Colophon );
          END   
        ELSE
          SET @Edition = 1;       
        INSERT @tvp 
          ( Name
          , Value) 
        SELECT EncryptedName
             , ENCRYPTBYKEY( KEY_GUID('$(VALUE_SYMMETRIC_KEY)')
                           , CAST( DECRYPTBYKEY( @BackupPhrase ) AS NVARCHAR(128) ) -- #SessionKey
                           , 1
                           , CAST( DECRYPTBYKEY( EncryptedName ) AS NVARCHAR(448) ) ) 
        FROM (SELECT $(EHA_SCHEMA).GetEHPhraseName( @DbName
                                                  , @NodeName
                                                  , @ActionType
                                                  , @Id ) AS EncryptedName ) AS derived
        EXEC $(EHA_SCHEMA).AddNameValue @tvp, @BackupPhraseVersion OUTPUT;   
        SELECT @BackupDDL = FORMATMESSAGE ( $(MESSAGE_OFFSET)25
                                          , CAST( DECRYPTBYKEY( @BackupPath
                                                              , 1
                                                              , @DbName ) AS NVARCHAR(1024) ) 
                                         , CASE WHEN @UseHash = 1 
                                                THEN CAST( @BackupNameBucket AS NVARCHAR(448) ) 
                                                ELSE CAST( DECRYPTBYKEY( @BackupName ) AS NVARCHAR(448) )  
                                                END    
                                          , '$(MASTER_KEY_BACKUP_EXT)'
                                          , CAST( DECRYPTBYKEY( Value
                                                              , 1
                                                              , CAST( DECRYPTBYKEY( Name ) AS NVARCHAR(448) )
                                                               ) AS NVARCHAR(128) ) ) 
        FROM @tvp;       
        EXEC @ReturnCode = sp_executesql @BackupDDL;
        IF @ReturnCode <> 0
          RAISERROR($(MESSAGE_OFFSET)12, 16, 1, @NodeName, '', '', '', @ActionType, @ReturnCode );
        ELSE
          RAISERROR($(MESSAGE_OFFSET)11, 0, 0, @NodeName, '', '', '', @ActionType );
        INSERT $(EHA_SCHEMA).$(BACKUP_ACTIVITY_TABLE) 
          ( Id
          , DbName
          , Node
          , NodeName
          , BackupName
          , BackupNameBucket
          , UseHash
          , BackupPath
          , BackupPhraseVersion 
          , Action
          , Status
          , Colophon
          , Edition
          , MAC
          , CipherType )
        VALUES ( @Id
               , @DbName
               , @Node
               , @NodeName
               , @BackupName
               , @BackupNameBucket
               , @UseHash 
               , @BackupPath
               , @BackupPhraseVersion
               , OBJECT_NAME(@@PROCID)
               , 'Complete'
               , @Colophon
               , @Edition
               , @MAC
               , @CipherType );  
        CLOSE ALL SYMMETRIC KEYS;
      END TRY
      BEGIN CATCH
        OPEN SYMMETRIC KEY $(ERROR_SYMMETRIC_KEY)
        DECRYPTION BY PASSWORD = '$(ERROR_KEY_ENCRYPTION_PHRASE)';
        INSERT $(EHA_SCHEMA).$(BACKUP_ACTIVITY_TABLE) 
          ( Id
          , DbName
          , Node
          , NodeName
          , BackupName
          , BackupNameBucket
          , UseHash
          , BackupPath 
          , BackupPhraseVersion
          , Action
          , Status
          , Colophon
          , Edition
          , MAC
          , CipherType
          , ErrorData )
        SELECT @Id 
             , ISNULL( @DbName, '' )
               , @Node
             , ISNULL( @NodeName, '' ) 
             , ISNULL( @BackupName, 0x0 ) 
             , ISNULL( @BackupNameBucket, 0 )
             , @UseHash 
             , ISNULL( @BackupPath, 0x0 )
             , ISNULL( @BackupPhraseVersion, 0 ) 
             , OBJECT_NAME( @@PROCID )
             , 'Error'
             , ISNULL( @Colophon, 0 )
             , ISNULL( @Edition, 0 )
             , ISNULL( @MAC, 0x0 )
             , ISNULL( @CipherType, '' )
             , 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 ALL SYMMETRIC KEYS;  
      END CATCH 
    END
    GO
    ADD SIGNATURE TO $(EHA_SCHEMA).BackupServiceMasterKey
    BY CERTIFICATE $(OBJECT_CERTIFICATE)
    WITH PASSWORD = '$(OBJECT_CERTIFICATE_ENCRYPTION_PHRASE)';
    GO

    To call the procedure requires a little extra effort to assure that sniffers and SQL Trace cannot easily steal the passed secret in clear text.  SQL Trace Assignment obfuscation, as detailed in this SQL Server Security blog post, is ineffective for call stored procedures and sp_executesql. The Starting included in the of the Common Criteria trace will reveal the secrets in SQL Trace. Attempting to fool SQL Trace with dynamic SQL will reveal the secrets in the StmtStarting events. The BackupServiceMasterKey procedure expects the secret to be already encrypted by a symmetric key that did not exist until there was a need to pass the secret. In the Administration tool a session scoped temporary symmetric key is used to encrypt all secrets before passing.

    This relies upon SQL Trace Cryptographic DDL event obfuscation and implicitly on SQL Trace Audit event secret obfuscation. Can’t tell you where SQL Trace obfuscation is documented but it is SQL Trace.  The SQL script and all called objects in the batch will have access to this tempdb key and when the batch completes the key is gone. Even if the bits of the encrypted secret are stolen on-the-wire the key is no longer available for anyone to decrypt.  (authentic even if the SSL is cracked?)

    To create the key:

    IF KEY_GUID('$(SESSION_SYMMETRIC_KEY)') IS NULL
      CREATE SYMMETRIC KEY $(SESSION_SYMMETRIC_KEY)
      WITH ALGORITHM = AES_256
         , KEY_SOURCE = 'Encryption Hierarchy Administration Cell Transport Encryption'
         , IDENTITY_VALUE = 'Install'
      ENCRYPTION BY PASSWORD = '$(PRIVATE_ENCRYPTION_PHRASE)';
    OPEN SYMMETRIC KEY $(SESSION_SYMMETRIC_KEY) 
    DECRYPTION BY PASSWORD = '$(PRIVATE_ENCRYPTION_PHRASE)';

    Then securely call the key backup procedure at anytime before the database connection that created the key is disconnected. The assignment operation will be obfuscated by SQL Trace. The phrase is only in a SQLCMD variable as clear text. It is never exposed a clear text within SQL Server. 

    DECLARE @BackupPhrase AS VARBINARY(8000) = 
                        (SELECT ENCRYPTBYKEY( KEY_GUID('$(SESSION_SYMMETRIC_KEY)')
                                            , CAST( '$(SMK_BACKUP_PHRASE)' AS NVARCHAR(128) ) ) );
    EXEC $(EHA_SCHEMA).BackupServiceMasterKey @BackupPhrase = @BackupPhrase
                                            , @UseHash = $(USE_HASH_FOR_FILENAME)
                                            , @ForceNew = DEFAULT;    
    
    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