Friday, November 18, 2011

0 Transparent Data Encryption (TDE)

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. Displays the hierarchy described in the topic.

Create the Server master key and certificate

  1. USE master
  2. GO
  3. CREATE MASTER KEY ENCRYPTION BY PASSWORD = '12345'
  4. GO
  5. CREATE CERTIFICATE ServerCert WITH SUBJECT = 'My Server Cert for TDE'
  6. GO
  7. BACKUP CERTIFICATE ServerCert TO FILE = 'C:\KEY\ServerCert_20111117.cer'
  8. WITH PRIVATE KEY (FILE = 'C:\KEY\ServerCert_20111117.key',
  9.     ENCRYPTION BY PASSWORD = '5678')
  10. GO

Create the test database

  1. USE Master;
  2. GO
  3. IF EXISTS (
  4. SELECT name
  5. FROM sys.databases
  6. WHERE name = N'TDE_TEST')
  7. DROP DATABASE TDE_TEST;
  8. GO
  9. CREATE DATABASE TDE_TEST
  10. ON PRIMARY
  11. (NAME='TDE_TEST1',FILENAME= 'C:\Data\Primary\TDE_TEST1.mdf',SIZE=10, MAXSIZE=100,FILEGROWTH=1 )
  12. LOG ON
  13. ( NAME = N'PartitoinTest_log', FILENAME = N'C:\LOG\TDE_TEST_LOG.ldf' , SIZE = 10 , FILEGROWTH = 10%)
  14. GO

Enable the TDE

  1. USE TDE_TEST
  2. GO
  3.  
  4. CREATE DATABASE ENCRYPTION KEY
  5. WITH ALGORITHM = AES_128
  6. ENCRYPTION BY SERVER CERTIFICATE ServerCert
  7. GO
  8. ALTER DATABASE TDE_TEST
  9. SET ENCRYPTION ON
  10. GO
  • 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

  1. select se.database_id ,sd.name ,se.encryption_state ,se.key_algorithm,se.key_length ,sd.is_encrypted
  2. from sys.dm_database_encryption_keys se join sys.databases sd on se.database_id= sd.database_id

image

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

  1. ALTER DATABASE TDE_TEST
  2. SET ENCRYPTION off

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.

image

After the server bounce, the tempdb is no longer encrypted.

image

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates