The Emperor’s New Transparent Data Encryption

Translucent Databases  (9780967584416) by Peter Wayner (2002) describes a database environment that reaches for security by separating information from data. Sort of a Hotel SQL Server: the data goes in but the information cannot get out. The result is an encryption driven environment where no single view inside or outside of the organization exposes all the data in the database in an unencrypted state at one time. The land where only the hash of your password is buffered, passed or persisted. Never the password. (Imagine! You, and of course that dude across the room that can memorize any 50 keystrokes you type from across the room, are the only one(s) that know your passwords! Yes there are such people…) A place where different parts of the data are encrypted with different keys, and keys belong to disparate organizational units. Where the DBA sees a customer table with mostly VARBINARY columns and perhaps other somehow index-able values that the various key authorities or application layer indexers have generated as that authority encrypts data. Wayner, in 2002 called it Translucent Data Encryption. Was he way off base since we have not vigorously pursued the mechanisms he postulated? I don’t think so. Instead, what has happened is the standards we strive for are too weak. Furthermore, many organizations fear security strategy that works because they fear the secure environment will be grindingly slow and unusable, so they default to compliance. And then the organization points helplessly to the standards while being hacked mercilessly: giving up our personally identifiable information in the process.

I suspect that RSA and Siemens and Sony and Citi and Sega and all the others were not far out of compliance with current standards yet millions of people have had pieces of their identity stolen recently in the small number of disclosed events. One is left to ponder just how many organizations are dishonest enough not to disclose breaches. (Update February 2, 2012 – you doubt it? VeriSign admits they were “successfully hacked several times in 2010” but saw no need for the admission until today. Wonder when they will get around to telling us about what happened in 2011? There must be some reason they decided to come clean all of the sudden.) Not that big of a deal to disclose I suppose. The only recourse we have is whether to save, trash or recycle the sympathy card from the organization that may have just cost a customer a lifetime of financial misery anyway.

Why have organizations accepted the weak standards? Why do many of the same organizations now whine that that strong security is a pipe dream? hmmm…

Translucent Databases: A Precursor to Privacy Sensitive Databases (.pdf)“, a paper I stumbled on recently by students Palaka Bhattacharya and Morteza Karya from an advanced survey of security course at Harvard from 2004 is drawn upon Wayner’s ideas. Database scenarios for cancer research, rape victims and a suggestion box are thoughtfully and creatively considered in the paper. Several good ideas about extending the Translucent Database to the specific security requirements particular to personally identifiable information are described. That students in a survey course can so lucidly map a convergence between the real world problems and the concepts from a book says good things about the students to be sure, something about the validity of the concepts in the book, and something about how little real progress we’ve made in the last decade on data security and in particular the ever-shrinking respect for the privacy of the customer/user.

Here we are many years later:

  • People steadfastly clinging to an obviously false belief that privacy policies are there to safeguard our data and that we are using best of breed solutions on problems that we must ultimately accept as unsolvable. Those privacy policies often do just the opposite. People sign away their privacy without looking because the document is called a privacy policy. Free market capitalism at it finest eh? Facebook is a good example of an identity puppy mill. Google or Microsoft or the government or the grocery store are ultimately no better. They could care less about protecting the personal information others have entrusted with them because there is no penalty as long as the compliance business is in order. I can’t prove it from here but I am quite certain I got clobbered with a man-in-the-middle exploit when I linked my WordPress hosted Blog to my Linked-In account. Strangely, the next day my yahoo email contact list was compromised (fixed it with a password change).
  • Organizations continuing to pretend that compliance & self regulation is as good as security gets. What they really know is compliance is as cheap as it gets. Real security infrastructure costs considerably more than the false front we see. And sadly, in too many cases all that is behind the false front is a couple of long wobbly sticks wedged to the ground to prop it up.

As a bit of proof that we aren’t that good at even recognizing good security standards,  I offer up the message I receive when I try to upload to the BLOG from the Windows Live Write client when the local Security Policy is checking for FIPS compliance:

WindowsLiveWriter,1.8800,Fail,00025,22-Jun-2011 16:43:05.969,
 The type initializer for 'WindowsLive.Writer.CoreServices.HttpRequestHelper' threw an exception.
 ---> System.TypeInitializationException:
      The type initializer for 'WindowsLive.Writer.CoreServices.WsseAuthenticationModule' threw an exception.
      ---> System.InvalidOperationException:
      This implementation is not part of the Windows Platform FIPS
      validated cryptographic algorithms.
at System.Security.Cryptography.SHA1Managed..ctor()
at WindowsLive.Writer.CoreServices.WsseAuthenticationModule..cctor()

The fix: disable the FIPS compliance policy to upload to the blog. Not sure if  other Microsoft Cloud technology will have this problem, have not tested it.


Transparent Data Encryption (TDE) is an encryption technique offered first for Oracle 10g Databases then later introduced into Microsoft SQL Server 2008. SQL Server TDE encrypts only at the database level while the Oracle TDE can also handle column and tablespace encryption strategies. TDE is included only in SQL Server 2008 Enterprise, Data Center, and of course Developer flavors. Correct me if I am wrong, but I think you can add Oracle TDE to any 10g/11i database but it is priced separately. I mention this competition to help establish why SQL Server even offers TDE and also clarify why the technology is available only in the high end SQL Server SKUs. I think it fair to say TDE came to SQL Server as “me too” technology.

“me too”

How corporate competition stifles software innovation

Say Company O has a product in wide use. Company M enters the market with a lower cost, easier to use product that serves almost the same purpose for the customer but with fewer and different features and without interchangeable compatibility with Company O’s product. Instead of purchasing the less expensive product, customers tell Company M’s sales staff that the product is just not as robust as Company O’s product. The sales staff collects the information and funnels it back to Company M HQ. The potential customers either choose not to switch from Company O’s product or to purchase from Company O instead of Company M in more cases than not. As things get desperate, Company M’s sales & marketing folks initiate ever more urgent and decisive enhancement requests for the ‘features needed to compete’! The risk assessment and/or cash flow and/or leadership egos do not allow time for vibrant R&D and anyway, the sales staff is adamant that the risk of upsetting customer’s with educational requirements for new or significantly different product features is too great. Development becomes fixated on delivering a functional – even if not yet fully usable – version 1 feature set to match targeted competitor features. The strategic purpose is to be seen as an equal competitor to Company O – at least in the silly mirror.  The message from leadership is that this vacuous short term play will solve the direct and immediate threat to long term product viability. Thus leaving the technology staff no choice but to make hay of it. Before you know it, when customers complain, account managers can say  we have that feature too now. At this point the commonly practiced “wait for SP1” avoidance behavior of the (ill-conceived?) feature(s) can begin. Eventually a sufficiently robust competitive feature list evolves while the competitor moves on as well.

The time to deliver those iterations is taken from time available for any other R&D investments. In this way,  so-called free market competition leads to a product targeting a competitor’s  feature list rather than any real customer need and time available for true innovation is also diminished product wide. Of course some of the me too efforts are so good they lead to patent litigation. The crony judge scolds everybody. The net effect will – hopefully – include increased short term earnings for corporations extended delivery times for real product innovation and, unfortunately,  fewer real choices for consumers of the product. From an Adam Smith economic perspective the effort is nothing more than Company M’s effort to collect rent on it’s customers: the goal is to collect more ‘profit’ with no added value. That is not good for investors or customers!

There is likely no way to even make a good correlation between “me-too” R&D and mid or long term benefit to the corporation without resorting the same convenient and ethically questionable statistical manipulations of the reader the corporation must use to continue to seek monetary rewards from fake innovation. Nonetheless, a business strategy that aims to beat others at their own game is risky, expensive, and ultimately counter-productive. It’s the opposite of a game changer and in no way consistent with the principals of free market capitalism.

(Ok, I’ve already exceeded my knowledge of Oracle’s TDE, Capitalism and the truth so I will limit my remarks to Microsoft SQL Server’s TDE from here.)

SQL Server 2008 Transparent Data Encryption

The Encryption Hierarchy is equally available in all SQL Server SKUs. However, TDE is included only in Enterprise, Developer and Data Center editions. SQL Server 2008 TDE is an emerging version 1 “me too” technology. It has seen a couple service releases. Predictably, the adoption rate was delayed at introduction but is now picking up some steam. Fortunately, I suspect there is not a lot of code involved in TDE so the unexpected consequences for jumping in early were small. Roy Ernest found a few good ones. Given the lack of disaster stories – somewhat confirming that the code changes were not large – I can only speculate as to why the technology in not available in all $KUs. The additional logic is to implement CryptoAPI hooks into SQL Server Buffer Pool management and plop in a CREATE and ALTER DDL statement – but no DROP – to manage the Database Encryption Key (DEK) stored in a small and always unencrypted space within the encrypted database. If you are the DBA and you enable TDE and manage the keys – or nobody manages the keys – don’t expect your risk exposure to go down by much. Much of what TDE is about is removing the implicit need to trust a DBA and other administrator. There is also a benefit from the always encrypted backups from a TDE database. This is a crucial mechanism to allow the DBA and the organization to better control who can use that encrypted backup and where it can be restored. The other main protection that TDE provides – data at rest encryption – is pretty close to never in play for a service like SQL Server that is typically running 24×7, has a lock on the file at all times – not that that can stop everyone, and exposes all data only in unencrypted form to all user database connections while running anyway. Most of what you need encryption for – protecting sensitive data – simply cannot be done with TDE. Lets face it, TDE is a low cost brute force compliance alternative or supplement to application layer data value encryption, hashing and cryptographic signing approaches to sensitive data protection.

SQL Server TDE occurs in low level IO processing. The data is encrypted just before the bus ride to secondary storage and then decrypted just as it transits back to primary storage. The implementation should benefit from XEON encryption acceleration. The documentation states that TDE uses the, “Windows Cryptographic API (CAPI)”. Interestingly, the number one hit on Google for, “Windows Cryptographic API (CAPI)” is a sardonic wikipedia entry who’s author(s) seems to have shared my longstanding confusion about all these names for Microsoft Cryptographic technologies. The second result is the link to the “Microsoft CryptoAPI“. Not one nor the other ever mentions or uses the phrase “Windows Cryptographic API” – not even the one that shows a long list of synonyms for the CryptoAPI. Lucky for me, the search engine seems to know what I want. I think this is a case where the SQL Server Documentation adds confusion where there was already enough. (It’s almost like the documentation team was trying to make the Wikipedia volunteer look ill-informed by using a name not listed. They are coming after me next I sure.)

The main benefits of TDE over other encryption techniques are:

  • the encryption requires no modification to the application
  • the encryption is generally recognized as compliant to PCI-DSS ‘Data at Rest’ requirements
  • TDE database backups are also encrypted using the DEK – binding the instance and backup file
  • no data is exposed as clear text if files are copied, stolen or probed in-situ – unless the probing is done via a SQL Server connection, in which case all data is exposed as clear text.

Nothing about the data being better protected from application level attacks!? This is because TDE exposes only decrypted data to all application and ad hoc user connections at the database. Authenticated users connected to the database – whether for legitimate or malicious purposes – see decrypted data in all query results. There is no option to view the cipher text. The encryption is at the page level so the page must be decrypted to even find out much about the rows on that page. There is no need for the user to provide credentials other than those presented for a ‘normal’ connection without TDE enabled. From the outside world the database is the same as it ever was. The data is encoded as it moves from primary, i.e. SQL Server data buffers to secondary storage, i.e. a RAIN (redundant array of independent NAND) of 750,000 IOP Micron p320hs SSDs or a few direct attached storage enclosures stuffed with commodity SAS drives or a zone of Brocade 96 gigabits per second encrypted LUNs.

The Database Encryption Key (DEK) is the core secret of TDE. It is not mentioned in any catalog views and is not accessible in the same way as other keys in the database. From Books Online:

“The database encryption key cannot be exported from the database. It is available only to the system, to users who have debugging permissions on the server, and to users who have access to the certificates that encrypt and decrypt the database encryption key.”

There should be no reason for application user’s to access the DMK or DEK.  Execution of all DMK DDL requires CONTROL permission on the database and VIEW DEFINITION permission on any asymmetric key or certificate mentioned in the DDL. If any application user requires CONTROL of the database they inherit control the DEK. Application users requiring elevated database permission increase the of risks of a breach. (HINT: Applications that require CONTROL of the database for anything are not secure by design.)

If all the application does at the database is INSERT, UPDATE, SELECT and maybe DELETE data, yet has authority – explicitly or implicitly – to mess with the DEK then consider tuning the security dial toward the principal of least privilege. IMHO, application users should not even have direct INSERT, UPDATE, or DELETE table permissions. This is even more true when the data is at all sensitive or private. Use stored procedures instead. Even for SELECT queries stored procedures are recommended – prepared statements are almost as good but the dependency chain is strongest when only stored procedures are used and they need direct table permission.  With stored procedures, the only permission the application user needs is EXECUTE on the procedure.  The application user should never own tables, schemas or databases. Only the rarest of application users should be a member of any Fixed Server Role. The access level of the application users must be carefully considered because application users are the most likely way bad guys will initially access the system.

Any database user can still view/copy/modify any data maliciously that they are also able to view/copy/modify legitimately. This is equally true if the user identity is taken over for malicious use or if the user identity is otherwise compromised.  This is true whether the data store is protected by TDE or file system encryption (an alternative to TDE described below).  TDE encryption protects the data from a file copy attack and some other trust exploitation attacks. TDE also encrypts all backup of the database.  By transparent is meant: applications and ad hoc SQL Server connections never know the data at rest is encrypted.

All secrets and certificates that are used to configure TDE must be protected in order to protect TDE. There are more than a couple important secrets to protect. The actual number can vary depending up the tactics selected for encryption. If encryption provides a choice between a certificate and secret, most of time I choose the certificate. Ideally, the data administrator must provide one password and the HSM or key vault administrator must provide one other password in order to restore a TDE database backup anywhere but the SQL Server that generated that backup.

The architecture and configuration options for TDE are nicely covered in Zubair Ahmed Mughal’s post on “Database Encryption Key Management” in the MSDN SQL Server Security weblog. This should be in Books online.

The configuration options for hashing, signing and encryption of identified data values are not as easily condensed to a single link. Look for Raul Garcia’s posts in the SQL Server Security weblog for insights into SQL Server’s implementation. But even before you go there, a decision about whether to encrypt in the application or in the database is needed. A developer/architect starting point is this “VISA BEST PRACTICES: Data Field Encryption” (.pdf)  that advocates , “data field encryption as a complement to, rather than a substitute for PCI-DSS compliance requirements”

As with the EKM + HSM solution, if encryption occurs at the point of data creation or entry, the data administrators need not know the unencrypted values to administer the data. She probably cannot help you much with searching or sorting based on that data values though. Further, the data administrator is not involved in the administration of certificates or secrets other than Certificates encrypted by database master keys .

TDE is useless against 90% of the attacks on your data!

Hopefully I have painted the picture enough to clearly show that TDE really isn’t so much protection in the grand scheme of things. According  to this 2009 ACE Team weblog post that provides 6 “Simple Rules to Stop the Bad Guys” 90% of attacks come at the application layer. TDE does nothing to stop that 90%. TDE targets some part of the other 10%. It’s strength is in embargoing the insider attack and securing the backup, but is only effective if the organization has the fortitude to truly protect the encryption key from the DBA and all other administrators. Few take the necessary steps. TDE in that context can help prevent data from walking out the back door. A DBA managed TDE DEK should decelerate the speed with which the back door swings wide open. Any careless use of TDE will increase the CPU load and can result in loss of data if a corruption issue requiring a restore from last known good backup is incurred only to have an untested restore process fail. The good news is, TDE is pretty much plug and play as long as nothing goes wrong.


Update October 2012: A monthly study released September 2012 by Imperva title “Monitoring Hacker Forums: ADC Monthly Web Attacks Analysis, October 2012″ found that only 6% of forum posts referred to encryption of any kind. This study found that:

›SQL injection is now tied with DDoS as the most discussed topic. Both topics got 19% of discussion volume. Last year, SQL injection was second with 19%, and DDoS came in first place with 22%. Ironically, of the $25 billion spent on software security, and we believe this means less than 5 percent of security budgets is allocated to products that cannot even recognize SQL injection attacks – let alone stop them. We believe this imbalance encourages hackers to continue to learn and deploy this attack method.
› Social networks today pose a major interest for hackers. They are becoming a prominent source of information, pictures, and potential monetary gain. Facebook was the most discussed social media platform, commanding 39% of discussions with Twitter a close second at 37%.
› E-whoring is becoming one of the most common methods for beginner cyber criminals to gain easy money. We saw more than 13,000 threads in a dedicated forum. E-whoring is a practice of selling pornographic content, while pretending to be the person, usually a female, photographed.

and concluded that corporations and governments are wasting time and money trying to solve the wrong problems:

› Security teams continue to ignore SQL injection attacks at their own risk. SQL injection continues to be the preeminent method of attack. Ironically, this year’s Gartner’s Worldwide Spending on Security by Technology Segment, Country and Region, 2010-2016 shows that of the $25 billion spent on software security, we believe less than 5  percent of security budgets is allocated to products that cannot even recognize SQL injection attacks [sic] – let alone stop them. We believe this imbalance encourages hackers to continue to learn and deploy this attack method.
› Consumers and enterprises that use social media need to better recognize the security risks posed by these platforms. While privacy concerns often get most of the headlines, security should be just as much of a concern. Hackers have developed a profitable industry using services and tools to hijack accounts, dupe users, and spread malware.
› Women should keep their clothes on when cameras are in the vicinity and men would better off getting to know the girl next door. Pornography continues to drive cybercrime with the growth of e-whoring that exploits both unsuspecting males and females. Hopefully, building awareness of e-whoring will mitigate its growth.

Note: I did send a note to Imperva questioning the statement “we believe less than 5  percent of security budgets is allocated to products that cannot even recognize SQL injection attacks”. Pretty sure they meant less than 5% of budget is allocated to products that can recognize a SQL injection attack but still waiting for a difinitive answer without having to buy a Forester report I don’t need.


Column level encryption, implemented in such a way that only authorized users can access the keys required to decrypt the column – perhaps in conjunction with TDE or file system encryption of the database file(s) – is a more realistic way to protect sensitive data at rest because it provides protection from application layer attacks. If the key is stored in the SQL Server it cannot be secured from all data administrator and may be more vulnerable to hacking. Keeping the encryption keys in a physical store that administrators of the database, database server OS and database subnet do not control will improve protection of the data from internal attacks. At a minimum, a good separation of responsibilities strategy would require that at least two admins be in cahoots to be able to steal a backup.

EKM enables separation of responsibilities by moving the certificate store to a Hardware Security Module (HSM), and thereby breaking away from the single principle in control condition of the SQL Server Encryption Hierarchy. At least a few currently available HSMs advertise that they have the required Cryptographic Provider .dll for use with the SQL Server EKM.  I have no hands on experience with an HSM. Unless I am mistaken, the 5 HSMs reviewed in this Internet Infrastructure Foundation commissioned HSM review (.pdf) all serve keys to the SQL Server 2008  EKM? A good place to jump in and learn a little anyway.

Are there alternatives when you do not have a TDE enable SQL Server SKU, cannot change the application, and yet have important and sensitive data that must be protected when at rest for compliance?

Lets set the bar low and say we want to be able to use SQL Server 2005 EXPRESS or better. Better meaning that the solution will also work on other SQL Server 2005 SKUs or any SQL Server 2008 SKU, or any SQL Server 2008 R2 SKU -only better because SQL Server 2005 EXPRESS is the poodle in the wolf pack.

Microsoft has published a white paper titled, “Database Encryption in SQL Server 2008 Enterprise Edition“. Mostly it is yet another how-to on implementing TDE. Though it does give a bit of valid consideration to several options. The most interesting of those options is file system encryption.

Transparent File System Encryption -vs- TDE

January 3, 2013 update: For $300 anyone can purchase software from Elcomsoft – A Gold Certified Microsoft Partner Headquartered in Moscow but with a .uk Internet Domain: – that will recover TrueCrypt, PGP and BitLocker encryption passphrases from memory or from a memory image created by Windows Hybernate.

Elcomsoft also offers software that targets SQL Server: “No need to install Microsoft SQL Server to recover a password! Unlock password-protected databases from any PC. All you need to replace any user or administrative password is Advanced SQL Password Recovery and master.mdf file.”

Does not look like Elcomsoft has much work left at all before a product to fetch the SQL Server TDE key from memory if it cannot be done with Elcomsoft products already available.

One option to encrypt the file system is BitLocker integrated file system encryption included with Windows 7 and Windows Server 2008. Or its predecessor the Encrypting File System in Windows 2000/XP-Pro/Server 2003, or even one of the 3rd party disk encryption software.  One vendor, gosecure, provides a nice comparison of their product to a couple other encryption software products with a list price and to TrueCrypt, an open source, free, on-the-fly and transparent encryption software that could even encrypt your MySQL databases that runs on a Linux box if you were so inclined. I’ll focus on TrueCrypt because I have actually used the product.

TrueCrypt allows three kinds encryption containers. Files, non-boot operating system logical drives, and the boot device. Encrypting all drives including the boot device – and entering the strong password to complete the encryption key during each system startup – is the best encryption protection because the pagefile.sys and any installation and configuration files that get created on the boot device and do reveal clues about sensitive data are found on the boot device. This is far more protection than TDE affords. Encrypting all drives including the boot device also carries the most overhead. Encrypting non-boot Volumes is attractive in a SQL Server direct attached storage configuration. The optimal SQL Server configuration with separate physical RAID arrays for data, log, and tempdb could be encrypted in this configuration. On a SQL Server I would expect the addition of the boot device to the encryption scheme would not add so much burden except at start-up since the IO on a database server is almost all about the data cache. (If a SQL Server is writing to the OS paging file it is already in performance hell. If you can’t fix that problem, I recommend you not mess with even open source, open license encryption solutions until you lay your hands on adequate memory.)

A SQL Server database device cannot be encrypted by encryption software. Instead, the .mdf and .ldf files are placed in already created encryption containers. If tempdb is to be encrypted place it on (a) TrueCrypt device(s) as well. Another subtle difference is that anyone that can read from a mounted TrueCrypt drive can get to the unencrypted data. Not the encrypted container created by TrueCrypt, that is definitely encrypted. I’m referring to the drive letter that shows up in Windows Explorer when a TrueCrypt container is mounted in the file system. Although, anyone with enough access to initiate such access would have a much easier time of it and a lower risk of being caught using the same credentials and T-SQL.

Unlike BitLocker, TrueCrypt can encrypt the boot drive or a file container.  There is a nicely detailed comparison of BitLocker and TrueCrypt at Tom’s Hardware.

TrueCrypt exposes the decrypted data when the encrypted container is accessed through the mounted file system device. This is an intended characteristic of transparent encryption. Once the mounting process acquires a drive letter, the drive must be secured at least as well as the SQL Server installation. If you are a Member of the Super Users Group on the machine, for example, when you mount the container file the permissions could allow other Super Users to access your containers.

TrueCrypt VHDs must be mounted and dismounted in the correct sequence. For encrypted disk to protect the database, a commitment to detach the database with sensitive data or alternately shut down the SQL Server Service and dis-mount the TrueCrypt device when the database is not in use and then remount and re-attach (or restart as the case may be) on demand may be a good option. Easy enough to automate…

TrueCrypt-ed data can be viewed in clear text. The TDE encrypted database is presented to everyone that connects to the database as unencrypted data. This is also true when the disk is encrypted except that in addition, decrypted data can also be exposed through the logical file system device as unencrypted data – for example if the file is copied using the logical drive to unencrypted storage the encryption is removed. The way to move encrypted TrueCrypt data is to un-mount the logical device and copy the encrypted container file. This does not integrate well with SQL Server. Better to have a good plan to start. To be safe from prying eyes, the logical device must have very restrictive (principle of least privilege) ACLs . With BitLocker a pre-boot password is required. With TrueCrypt it may only mean that a file is mounted to the already running local system before the SQL Server can be started. Although TrueCrypt can also be configured to encrypt the boot disk – and would also require the pre-boot password in this context.

TrueCrypt cannot encrypt existing files. Nor can it encrypt a SQL Server data file. Instead, the SQL Server data file must be placed inside the TrueCrypt created file container after TrueCrypt is mounted. When mounted, the encrypted file is assigned a logical drive letter and is used like any other drive. Care must be taken that the same drive letters are used for each device else the attach script will need to be edited before each use.

TrueCrypt is open source and free. TDE is integrated with SQL Server. TrueCrypt container encryption does not use SQL Server in any way. In both cases the encryption algorithm, certificate and password are user specified. And in both cases the FIPs complaint AES encryption algorithms and SHA1 hashing algorithm are available for selection. (Why use anything else? You want to be compliant don’t you?)

TrueCrypt uses Intel’s Advanced Encryption Standard New Instructions (AES-NI) hardware acceleration. By some accounts AES-NI hardware acceleration is over 4x faster than without. No reason not to believe that even TDE is using the hardware acceleration although I do not see much propaganda to that effect. TrueCrypt identifies AES-NI capable CPUs and allows enable/disable configuration of AES hardware acceleration.

TrueCrypt is not integrated with SQL Server. I said this already. It deserves repeating because there are so many pros and cons to that condition. With TDE there is no intermediate step like mounting a file and starting the SQL Server as is required when a TrueCrypt file container is used. SQL Server can auto-starts with the OS as designed with TDE. TrueCrypt will prevent SQL server from starting and could result in a database being marked suspect if the service is able to start but the database file cannot be found. The TrueCrypt piece of the startup/shutdown process can be automated and can include the startup/shutdown of the SQL Server service if the automation user is able to start services.

TrueCrypt can prevent users with CONTROL of the SQL Server from overriding the encryption. The password needed to mount the TrueCrypt device could, for example, belong to the data center operations group (Ops) and Ops could be excluded from CONTROL of the server and from access to the database.

With TDE you could use the EKM Cryptographic Provider (.dll) from an HSM. TDE  with EKM in a configuration where the HSM administrator does not have access to database subnet or a login to the SQL server is the clear security winner but the cost difference is very significant. The scenario where domain administrators do not control the SQL Server and the database administrators are not local administrators on the SQL Server box is almost achievable. (There is always a way for a local administrator to gain authorized access to a SQL Server. For this reason, it is better to entrust the key management  to a group that has no elevated authority as a database user, as a local system user, or on the domain and all other ACLs of the database subnet.)

As with my recommendation for TDE: if the SQL instance can work with an encrypted file system you may as well use it.  The main cost is CPU overhead that will never bother most SQL Servers,  and the time the DBA and any external key store owners must invest to verify, document and test the various security configurations, restore scenarios and key rotations.    

T-SQL Encryption 

That VISA document above says to consider encrypting the data for defense in depth along with TDE. As if data value encryption had little or no protection to offer or was at most a supplemental option. I’m here to tell you that is 141% backwards! And the TDE documentation seems to agree with me. I want to take it one step farther and state that encryption efforts must be redoubled in scope and hardness and TDE can be used as the supplemental technology to protect the database data at rest when the SQL Server is down. If we don’t roll up our sleeves and do as has been advocated by security experts and damn soon, surfing the Internet is going to feel like a ride on Charon’s boat. To be clear, the TDE documentation gets it right. The first sentence of the article, “Understanding Transparent Data Encryption (TDE)” states,

You can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. …

Whether the SQL Server Edition supports TDE or file encryption must be used to protect the data files and the backups, the increase level of security possible with data value level encryption is far more than the net security benefit of TDE or encrypted files. Most security guidelines recommend using transparent encryption along with data value encryption of identified sensitive values.

Encrypting at the application layer provides the most protection a la Translucent Data Encryption. Accurately identifying personally identifiable information is tricky as well. You just have to do your homework. Do you care if the primary key to the customer table is exposed as clear text as long as the name, address and  or the news story BLOBS that are served up to all Internet users on demand? If you were dumb enough to build customer IDs that contain any meaningful tidbits – say a three letter prefix that can be used to group customers you just may have to encrypt the primary key to be secure. If all data access occurs by stored procedure then the encoding and decoding can pretty easily be done in a stored procedure, but I’m telling you this is almost for sure not the best way, even if it is better than not encrypting data.  At least the rest of the application will not have to know anything about the encrypted data, but you still have that problem with a single principle responsible for the entire encryption hierarchy.

In summary, until the focus is turned toward security and away from compliance in lieu of security expect many a profitable company to give up your personally identifiable data unabated and unrequited but don’t expect them to tell you it happened.

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

One Response to The Emperor’s New Transparent Data Encryption

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.