TDE provide the encryption on the database and log file itself. Even other get hands on your database file or backup file. Without the key, they still can not get the information store in the database.
Upon enable the TDE, the SQL server engine performs real-time I/O encryption and decryption of the data and log files. The application does not require changes.
TDE is MSSQL Enterprise ONLY feature. Once you enable the TDE, you can not use the windows Instance File initialization feature ( because SQL Server will defiantly needs zero out the file before it can use it). TDE also make the backup compression less effective hence MSFT does not recommend to use 2 features together.
Create the Server master key and certificate
Create the test database
Enable the TDE
- Since the encryption is transparent, you can run your regular query against the object. SQL Server engine will take care the encryption and decryption.
- If the database was enable for TDE, even it is being disable. you still can not take the new backup file and restore the backup to the different instance.
- individual database can be encrypted using different algorithm. Once any of the database is being encrypted. The tempdb will be encrypted too with AES_256.
We can use below query to see the encryption status for the database
the sys.database.is_encrypted column reflect the state for the last ALTER DATABSE set encryption. Since we do not explicitly set encryption for the tempdb. the value is 0. However, the tempdb is being encryption as show in dm_database_encryption_keys.encryption_state. State 3 means it is encrypted.
We can disable the TDE by running below alter database
Lets check the encryption status again. TDE_TES encryption_state change to 1 which means it is unencrypted. The tempdb is still encrypted. The only way to complete disable the encryption from tempdb is to recreate it which means bouncing the server.
After the server bounce, the tempdb is no longer encrypted.
- Understanding Transparent Data Encryption (TDE) http://msdn.microsoft.com/en-us/library/bb934049.aspx
- Transparent Data Encryption http://www.simple-talk.com/sql/database-administration/transparent-data-encryption/