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

Even for the laggards among us, the time to recognize that we have not been building adequate security into most data stores is long past. Now is the time to set things straight. Predictably, mastery of the fundamentals is almost all of what is needed.

Consider the rudimentary principles that are consistently left undercooked in database applications in pursuit of an elusive performance and usability Sasquatch:

  • set-based processing
  • audit
  • principle of least privilege
  • defense-in-depth
  • division of responsibility
  • protection for sensitive data

This greedy claim of an application ruining bigfoot is an indefensible justification for reduced security. Moreover, data security need not inhibit performance, usability or innovation while these quality principles are far too important to a good outcome to forego. We so easily get caught up in the manufactured organizational bias between strong security and best usability that we loose sight of the real possibility to have both. I am confident that most of us would prefer to wait for the careful transaction at the bank or store or pharmacy  – even if impatiently and at some inconvenience – than to find out we are 5 bucks short or have just swallowed a capsule of wart remover compound expecting it to be prescription pain medication. About the same is true for software. A repeatable and methodical procedure is the most direct route to consistently desirable results.

(Instead, deviations from our base ancestral instincts expose our economic system as unsustainable. Profit motives value Capitalist dogma (e.g. cash flow, P/E, ROI and lazy me too features) above security obligations toward those who fund the profiteers. Compliance becomes an ever more ineffective stand-in for security. The difference between leaders and shady followers, swindlers and psychopaths blurs, but I digress…)

Cryptographic methods must be leavened, kneaded and then baked into applications to realize the security disciplines listed above. Instead we have become addicted to sugar coated deep fried concoctions that fool the sensibilities buttered in denial of the obvious.

Cryptography from the Windows perspective is a set of low level system services known collectively as the CryptoAPI. SQL Server relies upon the CryptoAPI for it’s baked in encryption capabilities. The implementation specifics of these services are application design decision. Furthermore – and almost without exception – if there is a choice between encrypting at the application layer or encrypting at the data layer using T-SQL then encrypting at the application layer has at least a couple of advantages in security.

  1. Encrypting sensitive data at the moment of origination is the moment of highest confidence that leaks of the clear text value is precluded.
  2. Encryption certificates, especially private keys, can be better isolated and secured when used at the application layer yet must be stored with the encrypted data in SQL Server.

Data that originates at the database may indeed be better encrypted at the database. That is simply not where most data originates. It falls short on the test of proximity to origin when data is left for encryption at the database server. While data that enters the system in bulk may be candidate for encryption at the database layer, I have to wonder why that data isn’t encrypted already if it needs to be encrypted upon bulk load? If it is already encrypted, the larger security burden may be in assuring the secure import of an encryption key needed to first decant such data before resolution with a database provided key.  User entered data, the most common example, is best encrypted and subsequently decoded only while in the context of a man-in-the-middle protected session where an authorized user enters, requests or otherwise requires access to the clear text data. To be in the moment of origination all transactions are best committed or rolled-back at or within the context of that originator’s authentic and secured session of interaction.

On the flip side, if a T-SQL encrypted database is stolen or compromised, the thief gets the keys and the data in one grab. EKM, and to a lesser extent keys that require a very strong and well protected phrase to open weaken the argument favoring application layer cell encryption via a secure encryption services layer (HSM, encryption appliance, certificate authority, etc.) but T-SQL cryptogtaphy where the keys stored in the database and encrypted under the Service Master Key are a data thief’s fast food. Furthermore, keys that live with the data rotten the restore-recovery scenario. Restores are undermined and made more complex by necessary best practice key rotation practices. Unresolvable breakdown in the division of responsibility arise when keys live with the database. Sure, key rotation is easily automated for off hours and there will be no gaps in responsibility, but always at least some of the database administrative staff control all keys and all data.

The CryptoAPI functionality available for T-SQL consumption is narrow when compared to the universe of properties, methods and objects exposed by the CryptoAPI. That is good and bad. In addition to what is already mentioned on the deficit side, this means there are limits to what can be done in T-SQL. On the plus side, most developers will not find the learning curve to get productive and effective with T-SQL encryption too steep. T-SQL encryptionallows and supports piecemeal implementations and is easy to use as a cookie cutter in a software factory. Some features that may seem appealing include:

  • Familiar T-SQL object administration and DDL syntax
  • Built-in T-SQL cryptographic functions
  • Limited selection of encryption algorithms
  • Auto-decryption of the Database Master Key for authorized users by default
  • Keys and certificates included in all full database backups
  • More efficient utilization of CPU resources on the typically underutilized CPUs of dedicated SQL Server database servers
  • Optional passphrase only decryption of the Database Master Key for division of responsibility fidelity that overrides both database and local administrator authority

What I most wish to convey in this series, are some recipes for baking encryption into T-SQL. This is done with the presumption that you have fairly sifted through all application layer alternatives and somehow come to the empirical decision as a wise data chef that T-SQL encryption is a viable ingredient in your recipe for application success.

All examples are from my Encryption Hierarchy Backup & Restore utility that may or may not include SQL Azure for secure offsite storage. I’ll include snippets from the install script to demonstrate several cryptographic implementation patterns. To see how the pattern is fully implemented, review the T-SQL or install the Encryption Hierarchy Backup & Restore component script in an evaluation SQL Server 2012 environment to create the schema. You can download a zip file or grab files from my SSMS project at https://github.com/bwunder/EHAdmin.

Most data other than cryptographic secrets use by the tool originates in the database so it more or less meets the sniff test for appropriate using T-SQL cryptography. The schema is based upon the script I have previously described here in the Encryption Hierarchy Administration post. That script made only limited use of encryption. In the version now under consideration, cryptographic dog food yields a translucent database state: where even the sysadmin role members need not have a clear text view of all data. This is very powerful with regard to establishing an enforceable separation of responsibilities.


The example application is also interesting because it uses SQL Azure for secure offsite storage. It does involve a trick: exporting certificate and key backups to an encrypted VHD and then importing the VHD’s container file into the database to move it to a central SQL Azure hub for safekeeping and recall on demand. SQL Azure is a reasonably secure offsite repository for the heterogeneous layers of encryption bundled around the data before it is sent offsite:

  • The container file for the VHD is encrypted by a key file or phrase. Once dismounted and loaded into the database it cannot be mounted from the database location. It must be restored to the file system and the phrase or key file required supplied just to get visibility of the backup files.
  • The certificate and key export files are encrypted onto the VHD by a phrase with a strong – and configurable – phrase strength policy. Once written to the VHD the phrase is required to reopen the file.
  • The copy of the user database’s Database Master Key is dropped from the master database. When this copy is removed, an application enforced strength policy compliant phrase must be provided to open that key.
  • All key and certificate exports created are encrypted into the output file using a key that meets the application defined and enforced strength policy.
  • All keys and certificates used by the application require a key or certificate to be open that can only be opened by providing a strength policy compliant phrase to open the Database Master Key, a certificate or a symmetric key.

The sysadmin running the install can select either change tracking or change data capture on supported SKUs along with a linked server to an ODBC DSN. The DSN can point directly to a SQL Azure instance or to a locally configured hub and then SQL Azure’s Sync services used to maintain the offsite mirror of all keys and certificates in local environment(s) or even another unmentioned off-site storage. With the ODBC option the secure offsite can as easily be SQL Azure as another database on the local network or a private cloud location that may serve as a hub for n number of SQL Server’s. The offsite schema does not require any ability to encrypt or decrypt data. In fact the data on the hub may be comingled with rows from other SQL instances that use different keys and/or different secrets. This allows a common storage and recovery scheme at the hub to support multiple SQL instances with an ability to prevent one instance from accessing artifacts taken from another instance – unless you explicitly ask for it that way and possess the credentials and secrets needed to make it so (to be sure, that is a feature in the classic and often derogatory context of any software feature.)

some proposed encryption rules of thumb

Use symmetric keys for cell encryption/decryption in T-SQL. Symmetric keys add far less overhead to IO processing than public key/private key encryption/decryption with a certificate or asymmetric key. Instead use the certificate or asymmetric key to encrypt a symmetric key.

Use each key and certificate for only one purpose. Sure you can do it all with one key but if that key is compromised, the entire data set is compromised. In this usage you might have one certificate with a sole purpose of encrypting symmetric keys used for cell encryption and under that certificate might be a symmetric key for each encrypted column.

Never trust a DML object to verify its own signature. There is no way to get a monolithic procedure to reliably tell you if its signature is in place. If hacked in an elevation of authority or insider attack, what’s to stop the hacker that might modify the stored procedure from saying the signature is just fine even if now gone? For slightly elevated security at the database to validate the authenticity of stored procedure – and to impress/thwart the uninitiated –  all stored procedures are built WITH ENCRYPTION, each stored object, e.g., stored procedure, function, trigger, view, is signed and all stored procedures call a second stored procedure also protected by WITH ENCRYPTION and a signature from the same certificate as the called stored procedure that shares the validation processing with the primary called stored procedures. The processing cannot proceed unless this two part schema authentication processing succeeds.

design objectives

The main purpose of this utility is in making sure no database objects are surreptitiously added, modified or removed from the schema to assure the integrity of the data.  The aim is to create a background server side application that is able to enforce a division of responsibilities among administrators. Unauthorized users, even if they can connect to the SQL instance as a member of the sysadmin server role, cannot access stored secrets used to create the key and certificate backups because they are unable to open the Database Master Key without the secret. Authorized administrators that know the secret can also chose to store additional secrets in the secure passphrase manager and to password protect any private secrets as desired. File system paths are also encrypted to thwart unauthorized user’s from mapping unintended files to database objects.

Even if someone does figure out which files(s) they are after, they will still need the phrases used for backup encryption of each file to load the key and then perhaps even another phrase to open keys that are encrypted by a phrase.

patterns covered in upcoming posts

  • Column encryption using the primary key column as authenticator
  • Column encryption with clear text of another encrypted column in the row as authenticator
  • Schema object signing run-time verification
  • Using WITH ENCRYPTION to embedded certificate phrases in stored procedures
  • Hashing a random seed to generate a random salt
  • Re-using a salt persisted to an encrypted column (secret salt)
  • Encryption of passed variables into the audit trail that data administrators and developers cannot decrypt because they cannot access the phrase encrypted key
  • In-flight row signature for message authentication and schema validation
  • TRY-CATCH error event data encryption to prevent unauthorized access and attack vector exploitation of exception information
  • Share-able symmetric key that can be implemented on many instances and securely conveyed by a combination of phrase, fob and/or file
  • In-flight encryption in a table variable/table valued parameter
  • Signing rows in sys.messages (or any other system or user table)
  • Signing a file’s VARBINARY(MAX) image

Bon apetit!

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