It is often good practices to encrypt a Microsoft database via Transparent Data Encryption (TDE). The purpose it so protect the content inside the database, when physical media (such as drives or backup tapes) are stolen. If such a TDE encrypted DB would be attached to SQL server which do not have the propper keys, the following error would appear:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot find server certificate with thumbprint '0xA123456789012345678901234567890'. (Microsoft SQL Server, Error: 33111)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5026&EvtSrc=MSSQLServer&EvtID=33111&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Â
and the attaching from the DB would fail.
TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data “at rest”, meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.
So to use the DB on another server you need either to export and import the keys, or decrypt the database on the source server. Decrypting the database is quite easy:
1.) Open the Database properties from the TDE encrypted database via the SQL Management studio
2.) switch to the options section and scroll to the bottom where you can find the option “Encryption Enabled” which should be on “True”
3.) To turn the encryption off switch that option to “False”, then press OK and close the dialog
4.) Depending on the size from the DB and overall performance on the SQL cluster, it might take time. You can check the status with the following SQL command:
select DB_name(database_id),encryption_state from sys.dm_database_encryption_keys
Which according to the Microsoft documentation would list a database which is in the decryption progress with the number “5”. Once the output change and shows no longer the “5” the DB is decrypted and can be used on another server.
5.) The last step would be to drop the Encryption key from the encrypted DB via:
USE MyEncryptedDB;
GO
DROP DATABASE ENCRYPTION KEY;
GO
