{"id":8023,"date":"2019-01-17T06:28:22","date_gmt":"2019-01-17T06:28:22","guid":{"rendered":"https:\/\/dbtut.com\/?p=8023"},"modified":"2019-01-17T07:03:38","modified_gmt":"2019-01-17T07:03:38","slug":"tdetransparent-data-encryption-on-sql-server","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/01\/17\/tdetransparent-data-encryption-on-sql-server\/","title":{"rendered":"TDE(Transparent Data Encryption) On SQL Server"},"content":{"rendered":"
TDE is used to encrypt the database. With TDE, Data and Log files are encrypted at the page level in real time and there is no interruption in applications.<\/p>\n
I guess the most curious question about TDE is this: “Can a DBA or another user who is authorized on instace can see this database when the database is encrypted by using TDE?”.<\/p>\n
Yes, he can. The purpose of TDE is to prevent third parties who are authorized on the database server to open your database on another instance by using the methods of copying database files to another server or retrieving the database’s backup and copying them to another server.<\/p>\n
In a TDE-enabled database, the data is encrypted before being written to the disk and the passwords are resolved and transferred to the memory when the data needs to be read. If you don’t want DBAs to see the data, you need to use an application-level encryption method. As an important information, if the database has fulltext index, it will be encrypted after TDE.<\/p>\n
In order to create the TDE, let’s perform the following steps step by step;<\/p>\n
\n
Create Master Key<\/li>\n
Creating a Certificate Protected by Master Key<\/li>\n
Create a Database Encryption Key protected by the certificate<\/li>\n
Set the database to use encryption<\/li>\n<\/ol>\n
Creating Master Key:<\/strong> We create the master key with the help of the following script.<\/p>\n
USE [master]\nGO\nCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDE123'\nGO<\/pre>\n
Creating a Certificate Protected by Master Key:<\/strong> We create our certificate with the help of the following script.<\/p>\n
USE [master]\nGO\nCREATE CERTIFICATE Sertifikam WITH SUBJECT = 'MyCertificate';\u00a0\nGO<\/pre>\n
Create a Database Encryption Key protected by the Certificate:<\/strong><\/p>\n
Create DEK with the help of the following script.<\/p>\n
USE AdventureWorks2014;\u00a0\nGO\u00a0\nCREATE DATABASE ENCRYPTION KEY\u00a0\nWITH ALGORITHM = AES_128\u00a0\nENCRYPTION BY SERVER CERTIFICATE MyCertificate;\u00a0\nGO<\/pre>\n
When we create DEK, it gives us a warning like below.<\/p>\n
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.<\/em><\/p>\n
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.<\/p>\n
For this reason, you should definitely take the backups of the certificate and private key.<\/p>\n
You can take backups of your certificate and private key with the help of the following script.<\/p>\n
USE master\nGO\nBACKUP CERTIFICATE MyCertificate TO FILE = 'C:\\Backup\\MyCertificate'\u00a0\n\u00a0\u00a0\u00a0 WITH PRIVATE KEY ( FILE = 'C:\\Backup\\MyPrivateKey' ,\u00a0\u00a0\n\u00a0\u00a0\u00a0 ENCRYPTION BY PASSWORD = 'TDE123' );\u00a0\nGO<\/pre>\n
Set the database to use encryption:<\/strong> Finally, we activate the encryption with the following script on our database.<\/p>\n
ALTER DATABASE AdventureWorks2014\nSET ENCRYPTION ON;\nGO<\/pre>\n
After completing these steps, we get the error as follows when we want to take the backup of our AdventureWorks2014 database and restore it to another instance.<\/p>\n
Msg 33111, Level 16, State 3, Line 2<\/em><\/p>\n
Cannot find server certificate with thumbprint ‘0x03F47278CEE248166E63B0E6736EA13CF8ECD9F3’<\/em><\/p>\n
Msg 3013, Level 16, State 1, Line 2<\/em><\/p>\n
RESTORE DATABASE is terminating abnormally.<\/em><\/p>\n
<\/p>\n
This error is normal. Because we did not create our certificate in this new instance. First we have to create our master key as follows.<\/p>\n
USE [master]\nGO\nCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDE123'\nGO<\/pre>\n
Then, using the certificate and private key backup, we create our certificate on the new instance with the help of the following script.<\/p>\n
Use [master]\nCREATE CERTIFICATE Sertifikam\n\u00a0\u00a0\u00a0 FROM FILE = 'C:\\Backup\\Sertifikam'\n\u00a0\u00a0\u00a0 WITH PRIVATE KEY ( FILE = 'C:\\Backup\\PrivateKeyim' ,\n\u00a0\u00a0\u00a0 DECRYPTION BY PASSWORD = 'TDE123' );\nGO<\/pre>\n
When I ran the script, I got an error like the following.<\/p>\n
The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.<\/i><\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c401fac120e7.png\")
<\/p>\n![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c401fe3f0fe9.png\")
<\/p>\n