Continuing with a description of the patterns introduced previously as the dog food stored procedure; and after the proc has completed a run-time inquisition to verify the authenticity and authority of the application’s actions in the booking phase the session and has won the cryptographic keys to the kingdom by running the gauntlet, the real work of the procedure can finally begin.
(Note that the SpokeInstall.sql script located in my github public repository may be easier to read or use than the in-line code below – be prepared for 8000 lines of T-SQL in the script. Simply search for the stored procedure name: BackupServiceMasterKey. Another option would be to refer back the somewhat simplified version of the procedure shown in the “dog food” post mentioned above.)
The first real work task involving cryptography is the generation of a fully qualified backup file name as cipher text. Of course, this assumes that there is business consensus that the backup file, and therefore the file path & name stored in the database, represent sensitive data and malicious use of the backup file name could potentially compromise any data obfuscated under the SQL instance Service Master Key’s Windows Data Protection rooted SQL Server Encryption Hierarchy. If a malicious user can access the Service Master Key then even TDE protected backups may be at risk. Even authorized use must not expose the path & name as clear text that can be queried, traced, sniffed, probed, intercepted or otherwise read from the database file.The “pattern” description begins with a few lines from the BackupServiceMasterKey stored procedure for generator functions to create values for the VARBINARY(8000) typed @BackupName and @BackupPath local variables along with a function to move a deterministic salted hash of the @BackupName‘s clear text value into the INT typed @BackupNameBucket variable. As was the case with the running of the gauntlet and will continue to be case as we traverse the sample procedure, the cryptographic operations are interleaved with authentication, authorization and business purposed application operations. The bucket, for example, will subsequently be used as an indexed access path for look up or reference without exposition of the clear text file name. The bucket will probably uniquely identify a row, but this is not guaranteed or important in this schema because we expect that the value will always be decoded after the bucket match to establish equality. The schema will support duplicate names, however the file system will not. Due to this potentiality, the bucket hash will never be used for any purpose other than the bucket match test. There will always be that little hole in the bucket that requires access to the deciphered name in order to assure value equality and.or uniqueness once the desired bucket is located.
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);
All generator functions in the example return sensitive data as cipher text to protect the meaningful data from snoops. T-SQL functions cannot open keys and certificates but they can encrypt and decrypt. The keys and certificates required must have already been opened in the current session. Furthermore, the core procs of the application from which the example is drawn – e.g., those white-listed in the running the gauntlet section of the Booking stored procedure – only accept sensitive values as cipher text. Encryption at the command line is required to prevent access to clear text both in memory – and on-the-wire if SSL is not properly configure. Consider that BackupServiceMasterKey requires a VARBINARY(8000) input parameter defining the backup file encryption phrase. To be sure, the interactions of the T-SQL login, the database user, the connection, the white-listing caller procs, and the generator functions is an interesting overarching security pattern. Here we will instead traverse the inner most cryptographic layer embodied by a few lines of code from BackupServiceMasterKey proc that securely assign a T-SQL generated and T-SQL obfuscated fully qualified file name to local variables in the proc that can be passed or stored without revealing the clear text.
Using the bucket hash authorized users can match the backup file in the file system to the logged backup activity row without the need to decode the column – at least not in every row of the table – each time a backup is completed. @BackupNameBucket will be indexed when persisted and can subsequently be used as a foreign key reference and as a look-up index key.
From an application functionality perspective, the bucket hash can optionally be used as the name of the file in the file system rather than using the clear text of the name generated. This will increase protection at the file system but reduce the obfuscation between the file system object and the database. The name is stored in the database only as cipher text preventing anyone browsing the file system from inferring the file content by the name when the hash is used as the name. Within the schema, a unique index creates a preferred error handling scenario, but only if the insert happens before a duplicate file name confounds a T-SQL mastered key or certificate export script/object.
(That highlights something of a special challenge for this application not really germane to the pattern under discussion: the export of a key or certificate to file cannot be rolled back in a T-SQL user transaction. Transactions are still important and valid, but it is also valid to pay attention to the order of work steps to prevent a file from being created by the execution of the stored procedure that later fails and rolls back all evidence of the now orphan export in the database.)
In this sample the values assigned to local variables could as easily have been generated in-line in a DML statement or a dynamic DDL statement without using local variables. That too would help protect exposure from tracing or snooping the execution context. In this stored procedure the values are needed more than once during execution so I am setting myself up for the need to decrypt the value more than once in the same proc! The @BackupName cipher text is decrypted first to hash a bucket value as shown above, then again to build a dynamic DDL key backup statement as in:
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;
where (MESSAGE_OFFSET)25 message_id has been populated in sys.messages with:
EXEC sp_addmessage $(MESSAGE_OFFSET)25 , 1 , 'USE master; BACKUP SERVICE MASTER KEY TO FILE = ''%s%s%s'' ENCRYPTION BY PASSWORD = ''%s''' , 'us_english' , 'FALSE' , 'replace';
By populating and passing the variable(s) as cipher text the assignment operation and the dynamically executed DDL statement are obfuscated in SQL Trace and monitored Extended Events. The clear text is never placed into a memory allocated variable – even when stashed in the variable registers within the SPID’s execution context.
I reckon it is possible that the clear text values could be mined from registers as they tick through the CPU during assignment – or more likely as as they are entered by the user. I mention this to highlight the reality that T-SQL cryptography must be part of a multi-faceted network+host+sql+social security strategy. Otherwise cryptographic obfuscation is merely an inconvenience to the malicious user.
Relying on any single mechanism to protect data is a slippery slope. It is important to recognize that CPU register mining – or whatever the latest hacker innovation – will happen, and will be exploited for honest and dishonest purposes and will become a necessary security consideration as exploits enabled by the innovation(s) become uncomfortably embarrassing.
It would be less resource intensive to wait to encrypt the value when inserted but also exposes the clear text value in the execution context buffers. Event engines (e.g., SQL Trace, DMVs, Alerts, Event Notifications, Extended Events, SQL Audit), Showplan, and a myriad of low level sniffers, process browsers and debuggers can snoop around in the execution context. Granted: elevated authority is needed for most of the exploits. If the organization fully trusts all personnel with elevated authority that will ever access the server or VM and is also confident that the authority hierarchy will not be breached then the organization may see no reason to worry about what can be seen in memory. Hope, luck and uninteresting data are that will protect that organization from going to the fire with an empty bucket.
File naming business rules are embedded within the NewMasterKeyBackupName function. By rule a representation of the backup time is concatenated to an otherwise deterministic literal. In the case of a Service Master Key backup, the descriptor will include the SQL instance name, the literal “ServiceMasterKey” and the date & time stamp. The value will be encrypted in the statement where generated. The time precision makes for a mighty small windows for a malicious user attempting to anticipate the value. However, knowledge of the logic in the function – or a good long contemplative look at the un-encrypted columns in the table might yet reveal enough information for a relatively precise dictionary attack vector to a trained eye.
CREATE FUNCTION $(EHA_SCHEMA).NewMasterKeyBackupName ( @DbName NVARCHAR(128) = NULL ) RETURNS VARBINARY(8000) $(WITH_OPTIONS) AS BEGIN DECLARE @NewName VARBINARY(8000); WHILE $(EHA_SCHEMA).CheckFile( @NewName ) <> 1 SET @NewName = ( SELECT ENCRYPTBYKEY( KEY_GUID('$(FILE_SYMMETRIC_KEY)') , CAST ( REPLACE(@@SERVERNAME,'\','$') + '__' + ISNULL ( @DbName + '__MasterKey' , 'ServiceMasterKey' ) + '__' + FORMAT( SYSUTCDATETIME(), 'yyyyMMddHHmmssfffffff') AS NVARCHAR(448) ) ) FROM sys.certificates AS c JOIN sys.crypt_properties AS cp ON c.thumbprint = cp.thumbprint WHERE c.name = '$(OBJECT_CERTIFICATE)' AND c.pvt_key_encryption_type = 'PW' AND cp.major_id = @@PROCID AND @@NESTLEVEL > 1 -- @DBName can be null here AND IS_OBJECTSIGNED( 'OBJECT' , @@PROCID, 'CERTIFICATE', c.thumbprint ) = 1 AND IS_ROLEMEMBER ( '$(EHADMIN_ROLE)', SYSTEM_USER ) = 1 AND SYSTEM_USER = ORIGINAL_LOGIN() ); RETURN ( @NewName ) ); END GO
If the FILE_CERTIFICATE that encrypts the FILE_SYMMETRIC_KEY is already open the cipher text of the generated name is returned by the function. If the certificate is not open or the name fails the validation of the CheckFile function, NewMasterKeyBackupName returns NULL.
The WHILE loop within the NewMasterKeyBackupName function works proactively to control access to sensitive information and at the same time prevent DRI and naming exception execution halts. The CheckFile function in the WHILE loop verifies the schema integrity, the T-SQL call stack depth, the user’s authenticity, the uniqueness of the name and the absence of selected SQL Injection substrings within the name.
CREATE FUNCTION $(EHA_SCHEMA).CheckFile ( @Name VARBINARY(8000) ) RETURNS BIT $(WITH_OPTIONS) AS BEGIN RETURN ( SELECT IIF (ba.Existing = 0, TRUE, FALSE) FROM sys.certificates AS c JOIN sys.crypt_properties AS cp ON c.thumbprint = cp.thumbprint OUTER APPLY ( SELECT COUNT(*) AS [Existing] FROM $(EHA_SCHEMA).$(BACKUP_ACTIVITY_TABLE) WHERE ba.BackupNameBucket = $(EHA_SCHEMA).AddSalt( '$(EHDB)' , '$(EHA_SCHEMA)' , '$(BACKUP_ACTIVITY_TABLE)' , 'BackupNameBucket' , DECRYPTBYKEY( @Name ) ) AS ba AND c.name = '$(OBJECT_CERTIFICATE)' AND c.pvt_key_encryption_type = 'PW' AND cp.major_id = @@PROCID AND @@NESTLEVEL > 1 AND @Name IS NOT NULL AND IS_OBJECTSIGNED('OBJECT', @@PROCID, 'CERTIFICATE', c.thumbprint) = 1 AND IS_ROLEMEMBER ( '$(EHADMIN_ROLE)', SYSTEM_USER ) = 1 AND SYSTEM_USER = ORIGINAL_LOGIN() AND ( PATINDEX('%[#,.;:"'']%', @Name) + PATINDEX('%--%', @Name) + PATINDEX('%*/%', @Name) + PATINDEX('%/*%', @Name) + PATINDEX('%DROP%', @Name) + PATINDEX('%CREATE%', @Name) + PATINDEX('%SELECT%', @Name) + PATINDEX('%INSERT%', @Name) + PATINDEX('%UPDATE%', @Name) + PATINDEX('%DELETE%', @Name) + PATINDEX('%GRANT%', @Name) + PATINDEX('%ALTER%', @Name) + PATINDEX('%AUX%', @Name) + PATINDEX('%CLOCK$%', @Name) + PATINDEX('%COM1-8%', @Name) + PATINDEX('%CON%', @Name) + PATINDEX('%LPT1-8%', @Name) + PATINDEX('%NUL%', @Name) + PATINDEX('%PRN%', @Name) ) = 0 ); END GO
To help prevent SQL injection that might be executed in SQL Server or perhaps even in the file system – since this is a file name – the injection filters include in CheckFile target both T-SQL and command line attack vectors.
The AddSalt function that we first saw invoked in the snippet we started with from BackupServiceMasterKey is again called in CheckFile. In BackupServiceMasterKey AddSalt produces a deterministic but randomized hash of the name that will be persisted. In CheckFile AddSalt re-produces the hash and compares it to the persisted value. This gives increased confidence that name has not been changed along the way.
CREATE FUNCTION $(EHA_SCHEMA).AddSalt ( @DbName NVARCHAR(128) , @SchemaName NVARCHAR(128) , @TableName NVARCHAR(128) , @ColumnName NVARCHAR(128) , @Word NVARCHAR(128) ) RETURNS INT $(WITH_OPTIONS) AS BEGIN DECLARE @SaltName NVARCHAR(443) = FORMATMESSAGE( $(MESSAGE_OFFSET)01 , @DbName , @SchemaName , @TableName , @ColumnName); RETURN (SELECT ABS( CHECKSUM( HASHBYTES( '$(HASHBYTES_ALGORITHM)' , @Word + CAST( DECRYPTBYKEY( Value , 1 , @SaltName ) AS NVARCHAR(128) ) ))) FROM ( SELECT Id, Name, Value FROM $(EHA_SCHEMA).$(NAMEVALUES_TABLE) WHERE NameBucket = ABS( CHECKSUM( HASHBYTES( '$(HASHBYTES_ALGORITHM)' , RIGHT( @SaltName, FLOOR( LEN( @SaltName ) / 2 ))))) ) AS derived ); END GO
Not only must the key used to encrypt the Value column be opened for this function to return anything but NULL, there is also no way to tell from looking at the function which key it is that must be opened.
In contrast to NewMasterKeyBackupName, the BackupPath function is deterministic. The BackupPath function verifies that the file encryption key is opened and that the function is signed by the schema’s signing certificate among the several security validations completed before returning the encrypted path.
BackupPath is the only T-SQL place that must be changed to use a different path. Hardcoding may expose the path to even high level sniffing (not just trace and extended events but also ad hoc catalog queries) if the function is not created WITH ENCRYPTION.
CREATE FUNCTION $(EHA_SCHEMA).BackupPath ( @DbName NVARCHAR(128) ) RETURNS VARBINARY(8000) $(WITH_OPTIONS) AS BEGIN RETURN ( SELECT ENCRYPTBYKEY( KEY_GUID( '$(FILE_SYMMETRIC_KEY)' ) , CAST( '$(VHD_LETTER):\' AS NVARCHAR(1024) ) , 1 , @DbName ) FROM sys.certificates AS c JOIN sys.crypt_properties AS cp ON c.thumbprint = cp.thumbprint CROSS JOIN ( SELECT TOP(1) KeyGuid FROM $(EH_SCHEMA).$(BOOKINGS_TABLE) ORDER BY RecCreatedDt DESC, Id DESC ) b WHERE b.KeyGuid = KEY_GUID( '$(SESSION_SYMMETRIC_KEY)' ) AND c.name = '$(OBJECT_CERTIFICATE)' AND c.pvt_key_encryption_type = 'PW' AND cp.major_id = @@PROCID AND @@NESTLEVEL > 1 AND DB_ID(@DBName) IS NOT NULL AND IS_OBJECTSIGNED('OBJECT', @@PROCID, 'CERTIFICATE', c.thumbprint) = 1 AND IS_ROLEMEMBER ( '$(EHADMIN_ROLE)', SYSTEM_USER ) = 1 AND SYSTEM_USER = ORIGINAL_LOGIN() ) ; END GO
Even when a SQL object is obfuscated by WITH ENCRYPTION the content – in the case of this function including the backup path – is visible in the database file and by querying the catalog through the Dedicated Admin Connection (DAC). If those security holes must be plugged, storing the value as a cipher text name value pair and deciphering it into the result may be more secure. As far as I can tell, legitimate use of the DAC includes the need to view system encrypted T-SQL objects to find out what is going wrong so probably better to not plug that hole. An encrypted file system drive and/or Transparent Data Encryption (TDE) coupled with hardened administrative access control can better plug the direct file access hole.
Perhaps the main take-away from the pattern described is that protecting a single piece of sensitive data – be that a password or a file path is no simple matter if the vulnerabilities created by elevated access are genuinely considered. Treating a complex and data rich application such as SQL Server as an endpoint is a flashing neon sign inviting malicious use.
anything below is advertising stuffed into the page by wordpress