T-SQL Cryptographic Patterns – part 8: leaving the key under the doormat

As if preventing sensitive data leaks using the obfuscation capabilities of SQL Server’s event subsystems weren’t enough of a challenge (see part 7) , it is the risk associated with storing encryption keys alongside the data that has been the strongest deterrent to using T-SQL cryptography if there is another choice. It is getting better but then so are the attackers…

Anyone who has experimented with DBCC PAGE() or DBCC SQLPERF() or DBCC MEMUSAGE() until that became boring – or anyone unfortunate enough to need to recover lost bits from a corrupt database knows that every bit in SQL Server’s memory space is readily available given an adequate level of access.

There are many debuggers, tracing engines and logging resources that expose bits in memory, in cache or on the wire. Some, like the SSMS or Visual Studio (remote) debuggers specifically target SQL Server. Others are less restrictive (more powerful?).


  • the Visual Studio WCF Listener and ATL trace tools
  • ODBC trace
  • Event Tracing for Windows
  • Windbg.exe
  • .NET System.Diagnostics namespace
  • Java Debug Interface (JDI) trace
  • TDS analyzers (Ribo, WireShark)
  • Countless network sniffers
  • Add you favorite dirty dozen

These are useful tools. The problem is they are as useful to the malicious as they are to the constructive.

There are also indirect helpers available to the observant. From Books Online, for example, can be learnt that a database Master Key lives in the database memory space and is not encrypted.  Could be useful information to anyone bent on pirating the encryption hierarchy of a SQL Server. I have no doubt that a hacking expedition could sooner or later find the right buffers with the right tool, perhaps goaded by the factual nature of the target. Even SQL Injection – depending upon the authority of the hijacked SPID – has some opportunity to mine memory objects.  The ready availability of many resources leave no question in my mind that mastery of the memory space is attainable to the determined.

Other opened keys and certificates, in addition to the Master Key may be available in memory as well? I cannot say how difficult it might be to recover any of those keys. I can see that access could depend upon several factors, most external to SQL Server. Nonetheless, I have no doubt that any key can be recovered from memory. Once a someone with an ability to locate a key in memory is in possession of the key’s bits it could be only a matter of injecting the bits into the registers used by a key or certificate of the same name on a SQL Server back in the hackers lab to be able to decode stolen cipher text.

The best prevention is to securely isolate the keys from the data. For what-ever reasons, the designers and developers of SQL Server decided to disregard this principal of cryptography. The genuine need lost out to least effort yet again perhaps? At any rate, the SQL Server encryption implementation is hindered by compromises that are clearly strategic or political – as opposed to technical – in nature. EKM looks to be not too much more than wallpaper to hide the inherent weakness of the implementation. Instead of pursuing a solution that separates the keys and the data, the canned workaround story-line reads something like this to me, “So! You don’t like our cryptography? OK fine! Take this old OLEDB hook that we will never use – and may even deprecate soon – and add your own damn cryptography – and oh, by the way you are on your own if you use the hook.” Frankly I don’t get it. SQL Server is made up of numerous services that can be deployed and secured: DB engine, Analysis Services, Reporting Services, SSIS, etc. What is the big deal about adding an encryption service that does about what you get now only without out disregard for security best practice?

I’m thinking: if I have to write my own cryptography or plop down lots-o-dollars for an encryption appliance or HSM, how much harder or more expensive is it to just write my own encrypted data store? One that I know exploits Intel’s AES-NI perhaps and does not silently feed events to ETW. One with a “translucent” workspace that no one can see as clear text in toto. One that is far less likely than a Microsoft product to attract an army of hackers armed with a bevy of free hacking tools.

Regardless, it is within the realm of possibility to achieve a measure of secure and layered isolation of data and keys stored within SQL Server. One layer is the audit capabilities of Windows and SQL Server. The absence of audited change will increase confidence that a compromise has or has not occurred. Another layer must assure that keys from one SQL Server are used safely and securely when encrypting data from another SQL Server. Two SQL Servers are required to pull it off: one to store the keys and another to store the data. The good news is only one of them needs to do encryption so even SQL Azure is a suitable candidate for the one that stores the cipher text. The keys and certificates must be on a real or virtual SQL Server, SQL Azure won’t work. This is admittedly a effort of moderate complexity. To convey that complexity I wish to induce a mental shift in the reader away from the SQL Server internals perspective expanded upon in the previous post toward an application architectural perspective.

To facilitate the shift, kindly zoom your attention out to this architectural view of the BackupServiceMasterKey stored procedure’s application. The procedure and the business context have been previously introduced in this series (see Dog Food).  BackupServiceMasterKey is a method from an encrypted secure catalog schema for end-to-end SQL Server Encryption Hierarchy Management. The schema is found in the database labeled spoke 1 in the diagram below. spoke 1 could be on any on-premise, virtual or private-cloud SQL Server 2012 instance. The databases labeled spoke 2, 3 and n also hold a copy of the schema: one per SQL instance.

The backup files are sent to the hub and stored at the hub as VARBINARY typed data. There is no file system access to the backup at the hub.

Metadata changes land on hub A as an already encrypted data store. There are no keys or certificates at the hub. The hub is primarily a passive offsite data store. The hub serves as best practice secure offsite storage for key backups.

Locate the start here marker in the spoke 1 diagram to follow the execution of BackupServiceMasterKey. The Service Master Key export is output to a local backup file and then relayed to a remote location at hub A.  Metadata associated with key backup file (i.e., SQL Server object name, key identifier, file paths, file name, passphrases, etc.) is encrypted at the spoke and also sent to the offsite location. A spoke can see the rows from all other spokes. A spoke may or may not be able decrypt data encrypted on another spoke. (so sees only cipher text).

Following the key’s traverse you may have noticed:

  • Spoke Administrator action is required to initiate a backup, pull a file from offsite or restore a key from backup.
  • The Service Master Key, db Master Keys and Certificates from all database on the spoke are eligible for backup/restore
  • Keys are encrypted by a phrase when exported to backup FILES in folder with write access restricted to the SQL Server service account
  • Phrases and other sensitive information are encrypted into a secure Encryption Hierarchy Administration catalog schema.
  • Upon backup a message is sent to the local service broker initiating an asynchronous, guaranteed offsite copy of the backup to the hub.
  • The Spoke Broker uses OPENROWSET(BULK ’’, SINGLE_BLOB) to insert the bits of the backup file into a VARBINARY column at the hub
  • Change Data Capture changes of the catalog schema are also delivered offsite using the local service broker.
  • The hub has no query access to spoke data. All data is pushed/pulled by the spoke.
  • The hub is a passive data store.  The store can be presented as a distributed partitioned view if more than one hub is present.
  • Spoke Admins do not require access to the hub in the workflow but are granted global select permissions for research and support.
  • A SQL Server virtual FILETABLE folder is mounted under the local backup location at each spoke.
  • Spoke Admin Role Members can recall backups from the hub’s Backups table to the spoke’s FILETABLE.
  • The FILTABLE’s UNC address can be used as the source of a key or certificate restore.

Hijacking a Spoke Admin account would provide no access to any data rows on the hub that did not originate at the current spoke using the current encryption hierarchy. Yes the hi-jacked account could query the global catalog schema, but without the encryption hierarchy from the encrypting SQL Server, the raw data will be of no use. The hub requires no cryptographic support yet can be the secure offsite storage for the encryption hierarchy backups from a large number of SQL Servers.

SQL Azure can host the hub database. Let me say that differently: SQL Azure can host the hub database! Do you realize what just happened to the cost excuse for not keeping a secure offsite copy of all key and certificate backups as is recommended best practice in Books Online. The hub does not need to know about encryption but is storing a deeply encrypted data set and is able to support meaningful queries. The fact that the data actually includes the encryption keys used to encode the data is hopefully interesting but not important to the concept of separation of keys and certificates from encoded data to prevent data leaks

The spoke is still vulnerable to compromises that might allow the malicious user to gain access to open local keys and decipher data previously encoded by this spoke. To better protect the spoke’s encryption hierarchy, the copy of the Master Key for the database were the catalog schema is created that is encrypted into the master database by the Service Master Key is dropped. Only the phrase encrypted copy of the Master Key stored in the database is kept. The result is that the phrase must be presented to open the key. This probably doesn’t scale well much beyond 1-2 people needing to know the phrase on a particular spoke, but is effective in restricting even sysadmin members without that phrase from decoding the cipher text.

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