By: Pablo Echeverria | Updated: 2022-08-31 | Comments | Related: > SQL Server and Oracle Comparison
Problem
In a previous tip, you've already learned how to encrypt data in transit using TLS 1.2 and how to hide/show data using data masking, but how do you protect data at rest? In this article, we look at how to do this for both Oracle and SQL Server.
Solution
Transparent Data Encryption (TDE) does real-time I/O encryption and decryption of data and log files without having to change existing applications, manage external keys, implement triggers/views/stored procedures, or change query execution plans. However, there are some considerations you must be aware of:
- This is the easiest way to encrypt data, but it must be complemented with access controls and auditing, especially for privileged users.
- All operations are reversible. You can change the encryption algorithm (i.e., when a vulnerability is discovered). You can remove encryption. You can change the master password/encryption key/rekey all data (decrypt and encrypt), etc.
- For increased protection, DBAs must not know the keystore/masterkey password and avoid creating a remote auto-login wallet in Oracle.
- First, analyze the risk and sensitivity of the data, then determine which algorithm causes less overhead.
- Consider the table size, redo/log size, and time of day when decrypting and re-encrypting columns because this is a resource-intensive operation during which data won't be available. This occurs when changing the encryption algorithm in Oracle and when changing the master key password in SQL Server.
- Avoid encrypting a column in an encrypted tablespace/database. There is no benefit, and it only affects performance.
Encrypting data within individual columns has worse performance over tablespace/database level and affects the ability to compress data. It's not transparent in SQL Server (manual operation), and you won't be able to perform range scans (BETWEEN, >, <). Tablespace/database level encryption is preferred as it operates during read/write operations, so there are fewer restrictions than with column encryption. Performance is rarely an issue since modern CPUs provide built-in hardware acceleration. There is no storage overhead, and it doesn't interfere with compression (compressed before encrypted).
TDE master keys need to be rotated periodically for compliance to limit a breach in case a key is deciphered (because decrypted records will be old enough) and if there have been enough records encrypted with a certain key. This is an online operation with zero downtime and without having to re-encrypt and store data since you only decrypt and re-encrypt the data encryption key. Changing the keystore password is necessary due to staff turnover, if the environment is shared, or if there is a high value in decrypting data. You can also decrypt and re-encrypt data as needed.
Previous keystores/masterkeys need to be retained in case there are old, encrypted database backups that must be restored later and until the next full/transaction log backup. Always backup all key-related objects before performing any operation, but keep them in a location separate from the encrypted data, as there won't be any protection if both are stolen together. When the keystore/masterkey is closed, the data is safe, and no operations are allowed on the key-related objects.
Both Oracle and SQL Server provide out-of-the-box means to encrypt data within data files, but there are several differences in how they do it and how you utilize them. We will compare SQL Server 2019 and Oracle 19c on Windows in this article.
Oracle TDE
After enabling TDE, you will be able to:
- Choose from different encryption algorithms: 3DES168, AES128, AES192, AES256, ARIA (for offline tablespaces), and GOST (for offline tablespaces)
- Choose to use integrity validation (SHA1) or not (NOMAC) in column encryption
- Duplicate the database decrypted using RMAN
- Export data decrypted using a data pump
- Encrypt Large Object Binary (LOB) data with SecureFiles
- Encrypt stored credentials for database links (SYS.LINK$) and scheduler jobs (SYS.SCHEDULER$_CREDENTIAL); by default, those are only obfuscated
- Encrypt data stored in text files through external tables
- Encrypt terabyte-sized, enterprise-core tables with Online Table Redefinition
- Store client application secrets in the keystore
There are some considerations you must be aware of:
- If you have a special case where TDE must not/can't be used, or you want to control who can decrypt the data, it's best to use the DBMS_CRYPTO package (manual operation).
- Earlier versions have more restrictions; refer to the documentation first.
- There are restrictions on SYSTEM, SYSAUX, and UNDO tablespaces.
- The TEMP tablespace must be dropped and re-created encrypted, but Oracle recommends that you don't encrypt UNDO and TEMP tablespaces if their underlying data is already encrypted.
- Once you encrypt the UNDO tablespace, Oracle recommends against decrypting it.
- There is an average performance impact of approximately 8% in end-user response time and around 5% in CPU usage in column encryption.
- Decrypted data can be found for some time in the datafiles (ghost copies) and momentarily in the swap file. You must implement ways to prevent access to them and clear that data.
- The encryption key may be required during a database close operation; thus, the key needs to be open.
The official documentation can be found here and best practices for older versions that still apply can be found here.
The steps to enable TDE for Oracle are described below.
Step 1: Set two initialization parameters:
- WALLET_ROOT to the path where the wallet will be stored, and
- TDE_CONFIGURATION to FILE (other values are used only for external verification)
You do this with the PowerShell commands below. There is an additional initialization parameter named ENCRYPT_NEW_TABLESPACES, which sets the default encryption for new tablespaces.
Clear-Content C:\app\Oracle19c\database\INITORCL.ORA $content=@" db_name=ORCL WALLET_ROOT=C:\app\Oracle19c\data\wallet TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" "@ Add-Content C:\app\Oracle19c\database\INITORCL.ORA $content
At this point, there is nothing in the wallet directory:
SQL> host dir /s C:\app\Oracle19c\data\wallet
Volume in drive C has no label.
Volume Serial Number is 684C-7016
Directory of C:\app\Oracle19c\data\wallet
07/18/2022 10:10 PM <DIR> .
07/18/2022 10:10 PM <DIR> ..
0 File(s) 0 bytes
2 Dir(s) 20,276,645,888 bytes free
This means querying the configuration returns status NOT AVAILABLE:
SQL> SELECT WRL_TYPE, WRL_PARAMETER, STATUS, WALLET_TYPE, FULLY_BACKED_UP FROM V$ENCRYPTION_WALLET; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE FULLY_BAC -------- ----------------------------------- ------------- ------------ --------- FILE C:\APP\ORACLE19C\DATA\WALLET\tde\ NOT_AVAILABLE UNKNOWN UNDEFINED
But if you run the queries below, there is an AES128 master key since the database inception and is associated with the SYSTEM, SYSAUX, and UNDO tablespaces, although those aren't encrypted. You can confirm this by searching for text in the datafiles. This key is used to encrypt wallet files, keys, passwords, secrets, etc.
SQL> SELECT ENCRYPTIONALG, MASTERKEYID, MASTERKEY_ACTIVATED FROM V$DATABASE_KEY_INFO;
ENCRYPT MASTERKEYID MAS
------- -------------------------------- ---
AES128 6F1CF952455C4FD0BF123EE4E244DD32 NO
SQL> SELECT TABLESPACE_NAME, NAME FROM V$DATAFILE_HEADER;
TABLESPACE_NAME NAME
------------------------------ ----------------------------------------
SYSTEM C:\APP\ORACLE19C\DATABASE\DBS1ORCL.ORA
SYSAUX C:\APP\ORACLE19C\DATABASE\SYX1ORCL.ORA
SYS_UNDOTS C:\APP\ORACLE19C\DATABASE\UND1ORCL.ORA
SQL> SELECT NAME, MKID, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE('01'||SUBSTR(MKID,1,4)))||UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(SUBSTR(MKID,5,LENGTH(MKID)))) MKID_DECODED
FROM (SELECT T.NAME, RAWTOHEX(X.MKID) MKID FROM V$TABLESPACE T NATURAL JOIN X$KCBTEK X); --TABLESPACES
NAME MKID MKID_DECODED
---------- -------------------------------- -------------------------
SYSTEM 6F1CF952455C4FD0BF123EE4E244DD32 AW8c+VJFXE/QvxI+5OJE3TI=
SYSAUX 6F1CF952455C4FD0BF123EE4E244DD32 AW8c+VJFXE/QvxI+5OJE3TI=
SYS_UNDOTS 6F1CF952455C4FD0BF123EE4E244DD32 AW8c+VJFXE/QvxI+5OJE3TI=
SQL> SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE('01'||SUBSTR(MKEYID,1,4)))||UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(SUBSTR(MKEYID,5,LENGTH(MKEYID)))) MASTERKEYID_BASE64 FROM (SELECT RAWTOHEX(MKID) MKEYID FROM X$KCBDBK); --CONTROLFILE
MASTERKEYID_BASE64
----------------------------------------
AW8c+VJFXE/QvxI+5OJE3TI=
Step 2: Create the keystore with the PL/SQL command below, but with your own password:
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY "software_keystore_password";
Now, there is a file in the directory:
SQL> host dir /s C:\app\Oracle19c\data\wallet
Volume in drive C has no label.
Volume Serial Number is 684C-7016
Directory of C:\app\Oracle19c\data\wallet
08/01/2022 10:21 PM <DIR> .
08/01/2022 10:21 PM <DIR> ..
08/01/2022 10:21 PM <DIR> TDE
0 File(s) 0 bytes
Directory of C:\app\Oracle19c\data\wallet\TDE
08/01/2022 10:21 PM <DIR> .
08/01/2022 10:21 PM <DIR> ..
08/01/2022 10:21 PM 2,555 ewallet.p12
1 File(s) 2,555 bytes
Total Files Listed:
1 File(s) 2,555 bytes
5 Dir(s) 20,256,141,312 bytes free
And the TDE status is CLOSED:
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE FULLY_BAC -------- ----------------------------------- ------------- ------------ --------- FILE C:\APP\ORACLE19C\DATA\WALLET\tde\ CLOSED UNKNOWN UNDEFINED
Step 3: Open the keystore (not to be confused with the key) with the PL/SQL command below, but with your keystore password.
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "software_keystore_password";
Now, the TDE status is OPEN_NO_MASTER_KEY because we haven't defined a key yet, and now the wallet type is PASSWORD:
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE FULLY_BAC -------- ----------------------------------- -------------------- ------------ --------- FILE C:\APP\ORACLE19C\DATA\WALLET\tde\ OPEN_NO_MASTER_KEY PASSWORD UNDEFINED
Step 4: Create a key with the PL/SQL command below, but with your keystore password. Note: This command only creates an internal encryption key, and you can't create it without the BACKUP part:
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "software_keystore_password" WITH BACKUP USING "key_backup";
There is a backup file in the directory, and the timestamp has changed:
SQL> host dir /s C:\app\Oracle19c\data\wallet
Volume in drive C has no label.
Volume Serial Number is 684C-7016
Directory of C:\app\Oracle19c\data\wallet
08/01/2022 10:21 PM <DIR> .
08/01/2022 10:21 PM <DIR> ..
08/01/2022 10:29 PM <DIR> TDE
0 File(s) 0 bytes
Directory of C:\app\Oracle19c\data\wallet\TDE
08/01/2022 10:29 PM <DIR> .
08/01/2022 10:29 PM <DIR> ..
08/01/2022 10:29 PM 3,995 ewallet.p12
08/01/2022 10:21 PM 2,555 ewallet_2022080204291231_key_backup.p12
2 File(s) 6,550 bytes
Total Files Listed:
2 File(s) 6,550 bytes
5 Dir(s) 20,256,186,368 bytes free
The TDE status is OPEN with FULLY_BACKED_UP set to NO:
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE FULLY_BAC -------- ----------------------------------- ------ ------------ --------- FILE C:\APP\ORACLE19C\DATA\WALLET\tde\ OPEN PASSWORD NO
And you can see the key details in V$ENCRYPTION_KEYS:
SQL> SELECT KEY_ID, CREATION_TIME, ACTIVATION_TIME, CREATOR, KEY_USE, KEYSTORE_TYPE, ORIGIN, BACKED_UP FROM V$ENCRYPTION_KEYS; KEY_ID CREATION_TIME ACTIVATION_TIME CREATOR KEY_USE KEYSTORE_TYPE ORIGIN BACKED_UP -------------------------- ------------------ ------------------ ------- ------- ----------------- ------ ---------- Aeh5NrF7A0/kv/pOUvEJ+GsAAA 01-AUG-22 10.21.51 01-AUG-22 10.21.51 SYS TDE SOFTWARE KEYSTORE LOCAL NO
Step 5: To fully backup all keys in the keystore, you must backup the keystore with the PL/SQL command below:
ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING "keystore_backup" IDENTIFIED BY "software_keystore_password";
Now the status is FULLY_BACKED_UP set to YES:
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE FULLY_BAC -------- ----------------------------------- ------ ------------ --------- FILE C:\APP\ORACLE19C\DATA\WALLET\tde\ OPEN PASSWORD YES
And there is one more backup file in the directory. The timestamps changed again:
SQL> host dir /s C:\app\Oracle19c\data\wallet
Volume in drive C has no label.
Volume Serial Number is 684C-7016
Directory of C:\app\Oracle19c\data\wallet
08/01/2022 10:21 PM <DIR> .
08/01/2022 10:21 PM <DIR> ..
08/01/2022 10:37 PM <DIR> TDE
0 File(s) 0 bytes
Directory of C:\app\Oracle19c\data\wallet\TDE
08/01/2022 10:37 PM <DIR> .
08/01/2022 10:37 PM <DIR> ..
08/01/2022 10:37 PM 3,995 ewallet.p12
08/01/2022 10:21 PM 2,555 ewallet_2022080204291231_key_backup.p12
08/01/2022 10:29 PM 3,995 ewallet_2022080204372986_keystore_backup.p12
3 File(s) 10,545 bytes
Total Files Listed:
3 File(s) 10,545 bytes
5 Dir(s) 20,255,518,720 bytes free
Step 6: Close the keystore and re-open it to see both the keystore and the key open without issues. When the keystore is closed, you can't perform any operations on the encrypted data or the keystore and its keys:
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "software_keystore_password"; keystore altered. SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "software_keystore_password"; keystore altered. SQL> SELECT WRL_TYPE, WRL_PARAMETER, STATUS, WALLET_TYPE, FULLY_BACKED_UP FROM V$ENCRYPTION_WALLET; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE FULLY_BAC -------- ----------------------------------- ------ ------------ --------- FILE C:\APP\ORACLE19C\DATA\WALLET\tde\ OPEN PASSWORD YES
Step 7: Check the keys stored in the wallet with the command below, entering your keystore password when prompted:
SQL> host orapki wallet display -wallet C:\APP\ORACLE19C\DATA\WALLET\tde Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Requested Certificates: Subject: CN=oracle User Certificates: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION.Aeh5NrF7A0/kv/pOUvEJ+GsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY ORACLE.SECURITY.ID.ENCRYPTION. ORACLE.SECURITY.KB.ENCRYPTION. ORACLE.SECURITY.KM.ENCRYPTION.Aeh5NrF7A0/kv/pOUvEJ+GsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA Trusted Certificates:
The output shows only the encryption key, not the master key, and if there are multiple keys. All of them must be listed here for them to work, or you must import them. For your reference, the command to merge an existing key into the existing wallet is like the following:
ADMINISTER KEY MANAGEMENT MERGE KEYSTORE 'C:\keystore\tde' IDENTIFIED BY "Welcome1" INTO EXISTING KEYSTORE 'C:\app\Oracle19c\data\wallet\tde' IDENTIFIED BY "Welcome1" WITH BACKUP;
Column-level Encryption
Considerations for this type of encryption include:
- The same key and integrity validation are used for all columns in the same table, but you can have different salt settings for different columns.
- The maximum size for each datatype gets reduced to accommodate the hash and the salt, up to 52 bytes.
- If you need to index the column, don't use salt.
- Not all datatypes are supported; refer to the documentation.
- Because this encryption operates at the SQL layer level, you can't use it for indexes other than B-tree ascending, range scan search through indexes (only equality works), synchronous change data capture, transportable tablespaces, and identity columns.
- It can't be applied when a foreign key constraint exists.
- The default encryption is AES192 with integrity SHA1 and salt. Otherwise, you must explicitly specify the algorithm, integrity, and salt usage.
When encrypting a single column, you can specify the key to encrypt data, which is helpful for external tables that can be moved or for table partition exchange operations where the password is used when creating a table based on the data, no matter what the master key is. You must create a user because you can't encrypt SYS-owned objects, and the wallet must be OPEN:
CREATE USER MYDB IDENTIFIED BY MyPwd;
GRANT DBA TO MYDB;
CONNECT MYDB/MyPwd
CREATE TABLESPACE MYTBSP DATAFILE 'MYTBSP.ORA' SIZE 1M;
CREATE TABLE MyTbl (
first_name VARCHAR(10),
last_name VARCHAR(10),
empID NUMBER,
salary DECIMAL(10,2),
ssn VARCHAR(10) ENCRYPT IDENTIFIED BY "password" NO SALT) TABLESPACE MYTBSP;
INSERT INTO MyTbl VALUES ('first', 'last', 1, 123.45, 'securessn');
SELECT * FROM MyTbl;
CONNECT / AS SYSDBA
ALTER SYSTEM CHECKPOINT;
Now you can see information related to the encrypted columns:
SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS; OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SALT INTEGRITY_ALG ----- ---------- ------------ ---------------- ---- ------------- MYDB MYTBL SSN AES 192 bits key YES SHA-1
You can see what key is associated with the table using the query below:
SQL> SELECT O.NAME, E.MKEYID FROM ENC$ E JOIN OBJ$ O USING(OBJ#); NAME MKEYID ---------- ---------------------------------------------------- MYTBL Aeh5NrF7A0/kv/pOUvEJ+GsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
If you encrypt another column with the command below, the associated encryption key doesn't change:
ALTER TABLE MYDB.MYTBL MODIFY (salary DECIMAL(10,2) ENCRYPT);
But if you rotate the key, the new key gets associated with the table.
For reference, consider the below test case scenario:
- Create a sample table with an INTEGER column.
- Populate the table with one million records: 25 seconds.
- Modify the table to encrypt the column: 4 minutes 28 seconds.
- Create a sample table with an INTEGER ENCRYPT column.
- Populate the table with one million records: 1 minute 9 seconds.
- Modify the table to decrypt the column: 1 minute 55 seconds.
- Rotate the encryption key: 0 seconds.
Tablespace-level Encryption
Considerations for this type of encryption include:
- The key is stored in tablespace file headers and segment headers.
- For the indexes to be encrypted, they must be in an encrypted tablespace. Otherwise, they return decrypted data even if the wallet is closed (if the table is not in the execution plan or materialized views).
- If the index resides in an encrypted tablespace, but the underlying table isn't encrypted, the index remains active when the wallet is closed.
- For performance, it is recommended that you specify FILE_NAME_CONVERT when encrypting an existing tablespace.
You can perform online encryption, which uses auxiliary space, generates redo, and allows encrypting multiple tablespaces in parallel, but not multiple datafiles in parallel in the same tablespace. You can also perform offline encryption, which allows the database to remain in an earlier database version (even if some features are unsupported). It is quick and allows encrypting data in a standby database first.
To create an encrypted tablespace, use the command below:
CREATE TABLESPACE MYTBSP DATAFILE 'MYTBSP.ORA' SIZE 1M ENCRYPTION ENCRYPT;
Then it appears in the DBA_TABLESPACES as shown:
SQL> SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES; TABLESPACE_NAME ENC ------------------------------ --- MYTBSP YES
The encryption-specific details can be found in V$ENCRYPTED_TABLESPACES. The status NORMAL means you can perform additional encryption operations on the tablespace because it has finished the previous operation:
SQL> SELECT TS#, DH.TABLESPACE_NAME, ET.ENCRYPTIONALG, ET.KEY_VERSION, ET.STATUS FROM V$ENCRYPTED_TABLESPACES ET JOIN V$DATAFILE_HEADER DH USING(TS#);
TS# TABLESPACE_NAME ENCRYPT KEY_VERSION STATUS
---------- ------------------------------ ------- ----------- ----------
3 MYTBSP AES128 0 NORMAL
Below is the command to modify the encryption settings for an existing tablespace online. You can specify the algorithm, specific datafiles, finish the previous operation, etc. except NOMAC and NO SALT:
ALTER TABLESPACE MYTBSP ENCRYPTION ONLINE USING 'AES256' ENCRYPT FILE_NAME_CONVERT=('MYTBSP.ORA', 'MYTBSP_ENC.ORA');
Below is the command to modify the encryption settings for an existing tablespace offline:
ALTER TABLESPACE MYTBSP ENCRYPTION OFFLINE ENCRYPT FILE_NAME_CONVERT=('MYTBSP.ORA', 'MYTBSP_ENC.ORA');
For reference, consider the below test case scenario:
- Create a sample table in an encrypted tablespace.
- Populate the table with one million records: 23 seconds.
- Rotate the encryption key: 0 seconds.
Password or Key Rotation
Rotate the key with the command below:
ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY "software_keystore_password" WITH BACKUP;
You need to provide the keystore password, and apart from adding a new key to the keystore, the existing encrypted tables and tablespaces are associated with the new key. Note: If you lose any of the keys, the data becomes unrecoverable forever, and there will be additional entries in the file "ewallet.p12" and in V$ENCRYPTION_KEYS.
If you rotate the keystore password with the command below, the records will have the same encryption key, but the password to open the keystore will change. There's no need to keep the old password anymore:
ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY "software_keystore_password" SET "new_software_keystore_password" WITH BACKUP;
This will modify the same file "ewallet.p12", so its timestamp is not valid proof that the password or the key were rotated.
There is a different command for rekeying an individual table with a different encryption algorithm, but there isn't for tablespaces. It requires you to move the data from one tablespace to another, and in both cases, it is an expensive and extensive operation.
Auto-login Keystore
The master key can be copied into an 'auto-open' wallet, but there are some considerations:
- This can be done using Oracle Enterprise Manager, Oracle Wallet Manager, or the 'orapki' utility.
- This operation creates an auto-open wallet ('cwallet.sso') in the directory.
- To significantly strengthen your security when using an auto-open wallet, a local one can be created. It does not open on any machine other than the one it was created on.
- It is not possible to use a local auto-open wallet in Oracle RAC when the wallet is to be stored centrally in ACFS. You must copy it to each node in a non-shared directory.
- Do not delete the original encryption wallet, as re-keying the master key requires the original encryption wallet to be present.
The command is similar to the following:
ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE 'C:\app\Oracle19c\data\wallet\tde' IDENTIFIED BY "software_keystore_password";
SQL Server TDE
There are some considerations you must be aware of:
- You can't encrypt system databases, but the TEMPDB gets encrypted, and you can't decrypt it later, which may affect performance for unencrypted databases.
- All files and filegroups need to be encrypted, so there can't be read-only ones.
- While an encryption operation is in progress, maintenance is disabled.
- You can't enable TDE if a backup is running or a snapshot is in progress.
- After enabling TDE, instant file initialization is unavailable.
- Replication isn't encrypted unless you set it up, and even after setup, it can still store unencrypted data within intermediate files.
- Filestream data isn't encrypted.
- Files related to buffer pool extension aren't encrypted; you can use BitLocker or EFS for them.
- In earlier versions, there are less secure algorithms; refer to the documentation.
The official documentation can be found here.
The steps to enable TDE are described below:
Step 1: Create a symmetric key to protect the private keys of certificates and database asymmetric keys. It will be AES_256, and remember to use your own strong password where specified:
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'; GO
You will see the master database has a server encrypted key:
SELECT name, is_master_key_encrypted_by_server FROM sys.databases; name is_master_key_encrypted_by_server master 1
And there will be a database master key:
SELECT name, algorithm_desc, create_date, key_guid FROM sys.symmetric_keys; name algorithm_de create_date key_guid ##MS_DatabaseMasterKey## AES_256 2022-08-10 CA6A0000-2A9F-4585-B9F7-9002AFCD3B01 ##MS_ServiceMasterKey## AES_256 2022-08-10 63E73506-DB3F-4DE8-B80E-CA9109F1D948
Note: There was already a ServiceMasterKey since SQL Server was installed. After the master key is created, backup both keys and store them in a secure off-site location (in SQL Server 2022, it can be an URL in Azure Blob):
BACKUP MASTER KEY TO FILE = 'C:\temp\MyDbMasterKey' ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'; BACKUP SERVICE MASTER KEY TO FILE = 'C:\temp\ServiceMasterKey' ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
By default, automatic decryption of the master key is performed using the service master key, which is the master key stored in the master database.
WARNING: The documentation says you can drop the service master key, so you need to manually open the master key. But during my testing, after dropping it:
- I was unable to encrypt the database
- The scan state was ABORTED
- The encryption state was ENCRYPTION_IN_PROGRESS, and
- There was an entry in the error log: "Please create a master key in the database or open the master key in the session before performing this operation."
This happened even after I opened the master key, regenerated the service master key, and even restarted the server.
Step 2: Add a self-signed X.509 certificate to the database with the command below. No password is required since it is encrypted with the database master key:
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
Then you can see it with the query and output below:
SELECT name, issuer_name, start_date, expiry_date, key_length FROM sys.certificates; name issuer_name start_date expiry_date key_length MyServerCert My DEK Certificate 2022-08-11 03:25:15.000 2023-08-11 03:25:15.000 2048
Note: By default, it will expire in one year. Once it expires, you need to create a new one (rotate), but even if it's expired, you'll be able to encrypt and decrypt databases, and only a warning is shown. Now, you need to backup the certificate, as it may be used to decrypt old backup files:
BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\setup\MyServerCert' WITH PRIVATE KEY ( FILE = 'C:\setup\MyPrivateKey', ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>' )
Step 3: If you want to do column encryption (which is not transparent, it's manual), create a symmetric key like the following. The available algorithms are AES_128, AES_192, and AES_256:
CREATE SYMMETRIC KEY CreditCardsKey WITH ALGORITHM=AES_256 ENCRYPTION BY CERTIFICATE MyServerCert;
Then a new entry is added to sys.symmetric_keys. After creation, you can encrypt data by opening the symmetric key (decrypt by certificate) and using the function EncryptByKey.
Database-level Encryption
First, you need to create a database and then create its encryption key encrypted by the server certificate. The available algorithms are AES_128, AES_192, and AES_256:
CREATE DATABASE MYDB; GO USE MYDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM=AES_256 ENCRYPTION BY SERVER CERTIFICATE MyServerCert; GO
A warning will be displayed if you haven't backed up your certificate and private key:
Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
And there will be an entry as shown below:
SELECT db_name(database_id), create_date, encryptor_thumbprint FROM sys.dm_database_encryption_keys; name create_date encryptor_thumbprint MYDB 2022-08-11 05:48:35.800 0x385E456A239626FB10FB8D8F7904CC659BF85576
Then, you can turn on database encryption with the command below:
ALTER DATABASE MYDB SET ENCRYPTION ON;
For large databases, during the encryption scan, you can suspend and resume it with the commands below:
ALTER DATABASE <db_name> SET ENCRYPTION SUSPEND; ALTER DATABASE <db_name> SET ENCRYPTION RESUME;
You can get the encryption status with the query and output below:
SELECT db_name(database_id), encryption_state_desc, encryption_scan_state_desc, encryption_scan_modify_date FROM sys.dm_database_encryption_keys; name encryption_state_desc encryption_scan_state_desc encryption_scan_modify_date tempdb ENCRYPTED COMPLETE 2022-08-11 03:51:00.577 MYDB ENCRYPTION_IN_PROGRESS ABORTED 2022-08-11 03:51:00.613
And you can see which databases are encrypted with the query and output below:
SELECT name FROM sys.databases where is_encrypted=1; name tempdb MYDB
For reference, consider the below test case scenario:
- Create a test table in a non-encrypted database. tempdb is also not encrypted.
- Populate the table with one million records: 2.4 seconds.
- Create a test table in an encrypted database.
- Populate the table with one million records: 2.4 seconds.
- Create a test database in a non-encrypted database. tempdb is encrypted.
- Populate the table with one million records: 2.4 seconds.
Password or Key Rotation
When the certificate is approaching its expiration date or when enough records have been encrypted with it, you can generate a new one with the command below:
USE master; GO CREATE CERTIFICATE MyServerCert2023 WITH SUBJECT = 'My DEK Certificate'; GO USE MYDB; GO ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE MyServerCert2023; GO
This operation takes no time. After this, you will see a different encryptor_thumbprint associated in sys.dm_database_encryption_keys.
You can use the command below to rotate the master key encryption password. This is a resource-intensive operation because the keys are decrypted and re-encrypted:
USE master; GO ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere2023>'; GO
Remove TDE
You can remove TDE with the command below:
ALTER DATABASE <db_name> SET ENCRYPTION OFF;
Conclusion
You can see the process is similar in both databases: create a keystore in Oracle and a master key in SQL Server, and then create a key in Oracle and a certificate in SQL Server. This key or certificate needs to be backed up and rotated periodically. Then you can encrypt what you need in Oracle. However, in SQL Server, you must create a database encryption key first. In Oracle, you encrypt individual tablespaces, which has benefits and drawbacks, while in SQL Server, the whole database gets encrypted, but tempdb is also encrypted. In both databases, the time isn't affected after encrypting a tablespace or the whole database, and rotating the key also takes no time.
Next Steps
Here is the official documentation for Oracle TDE:
Here is the official documentation for SQL Server TDE:
Here are more tips regarding SQL Server TDE:
- How to simulate transparent data encryption TDE with SQL 2000 and 2005
- SQL Server 2008 Transparent Data Encryption getting started
- Implementing Transparent Data Encryption in SQL Server 2008
- Configuring Transparent Data Encryption with SQL Server 2012 AlwaysOn Availability Groups
- Does SQL Server TDE still work with an expired certificate
- Configure SQL Server Transparent Data Encryption with PowerShell
- Add and Monitor Transparent Data Encryption to Azure SQL Databases
- SQL Server Transparent Data Encryption (TDE) Performance Comparison
- Get Immediate Protection Your SQL Server Databases with DBDefence
- Azure SQL Database TDE Customer Managed Keys
- Configure a SQL Server Database using Transparent Data Encryption and Database Mirroring
- SQL Server Encryption Tips
- Encryption Protection for your Application and SQL Databases
- SQL Server Transparent Data Encryption Alternative Solution - NetLib Security Encryptionizer
- SQL Server Transparent Data Encryption vs. NetLib Encryptionizer
- Easy and Cost Effective way to Encrypt Every SQL Server Database
- SQL Server Transparent Data Encryption Alternative from DBDefence
- SQL Server Data Masking with DbDefence
About the author
Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-08-31
