{"id":20,"date":"2011-08-12T21:31:10","date_gmt":"2011-08-12T21:31:10","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=20"},"modified":"2014-02-28T21:31:28","modified_gmt":"2014-02-28T21:31:28","slug":"securing-and-protecting-sql-server-data-log-and-backup-files-with-tde","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/securing-and-protecting-sql-server-data-log-and-backup-files-with-tde\/","title":{"rendered":"Securing and protecting SQL Server data, log and backup files with TDE"},"content":{"rendered":"

In this post I’ll show you how to setup Transparent Data Encryption (TDE). TDE is new in SQL Server 2008 and serves as an encryption method that uses a database encryption key (DEK) to protect SQL Server’s data and log files. The DEK is a key secured by a certificate stored in the master database.<\/p>\n

To setup TDE we’ll need to run a few scripts: (My test database is named TDE)<\/p>\n

The following script will create the master key with a specified password ElephantRhin0:<\/p>\n

\n
USE master;\r\nGO\r\nCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ElephantRhin0';\r\nGO<\/pre>\n<\/div>\n
Next, we’ll create a certificate named TDECert that will be protected by the master key:<\/p>\n
\n
USE master;\r\nGO\r\nCREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';\r\nGO<\/pre>\n<\/div>\n
After creating the certificate we’ll backup the certificate to a specified source:<\/p>\n
\n
USE master;\r\nGO\r\nBACKUP CERTIFICATE TDECert TO FILE = 'C:\\TDECert_backup' WITH \r\nPRIVATE KEY ( FILE = 'C:\\TDECert_key' ,ENCRYPTION BY PASSWORD = 'ElephantRhin0' )\r\nGO<\/pre>\n<\/div>\n
Once the certificate is backed up we will create the DEK using the AES algorithm and protect it by the certificate:<\/p>\n
\n
USE TDE;\r\nGO\r\nCREATE DATABASE ENCRYPTION KEY\r\nWITH ALGORITHM = AES_128\r\nENCRYPTION BY SERVER CERTIFICATE TDECert;\r\nGO<\/pre>\n<\/div>\n
The final step is to set our database to use encryption:<\/p>\n
\n
ALTER DATABASE TDE\r\nSET ENCRYPTION ON;\r\nGO<\/pre>\n<\/div>\n
If everything completed successfully then we have officially encrypted our database with TDE, but don’t take my word for it, run the following query to confirm:<\/p>\n
\n
SELECT name, is_encrypted\r\nFROM sys.databases\r\nWHERE name = 'TDE'<\/pre>\n<\/div>\n
\"SQL<\/a>Click here to view the rest of this post.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
In this post I’ll show you how to setup Transparent Data Encryption (TDE). TDE is new in SQL Server 2008 and serves as an encryption method that uses a database encryption key (DEK) to protect SQL Server’s data and log files. The DEK is a key secured by a certificate stored in the master database. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[12,98,11],"class_list":["post-20","post","type-post","status-publish","format-standard","hentry","category-security","tag-encryption","tag-security","tag-tde"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/20","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=20"}],"version-history":[{"count":1,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/20\/revisions"}],"predecessor-version":[{"id":22,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/20\/revisions\/22"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=20"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=20"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=20"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}