How to Use TDE (Transparent Data Encryption) to encrypt the column values of table SQL

Question

I went through this link: https://msdn.microsoft.com/en-us/library/bb934049(v=sql.110).aspx Is that it, it only encrypts the database, what about the column inside the table.

I used the syntax

    USE master;
    GO
     CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
     go
      CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
      go
       USE AdventureWorks2012;
       GO
        CREATE DATABASE ENCRYPTION KEY
        WITH ALGORITHM = AES_128
        ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
        GO
         ALTER DATABASE AdventureWorks2012
         SET ENCRYPTION ON;
         GO

But the column are visible to me how to encrypt the columns also using TDE

I am new to TDE please help me out.

score 1 · Answer 1 · edited Feb 21 '18 at 14:50

1

TDE is an encryption type which encrypts the data and log files of a database. If someone manages to steal your backup files they will not be able to decrypt it without the database encryption key referred to as the DEK, which is stored in the Master database. Even if you want to restore a backup of a TDE encrypted database you will have to have the same DEK in the destination database. Enabling TDE also encrypts your tempdb.

For a more clear understanding, please refer to Microsoft's Books Online and you can also use the following from TechNet. Transparent Data Encryption (TDE)

Before enabling on any system please make sure to read up more and know its effects, advantages and disadvantages.

edited Feb 21 '18 at 14:50

RoastBeast

1,059
2
22
38

answered Feb 18 '16 at 15:39

Im88

111
2

Hi thanks for the reply, one small question, if I try to view the data in my table will it be encrypted or will it appear as normal values. whatever was inserted into the table... – A J Feb 19 '16 at 06:44
Data in table it will appear as normal value. You will see no difference in data at table level. – Im88 Feb 19 '16 at 13:54

score 0 · Answer 2 · answered Feb 21 '18 at 14:58

TDE is transparent data encryption, i.e., it does not affect your database as perceived by connected users and applications. What it does is encrypt data before it is saved to disk and decrypt it when it is being read back up into the working set.

If you want to encrypt data per column, you can take a look at SQL Server feature called Always Encrypted (only available on v2016 and newer) or external solutions like CryptDB (research project, only for MySQL) or Prisma/DB. They provide per-column encryption and are compatible with TDE as well.

How to Use TDE (Transparent Data Encryption) to encrypt the column values of table SQL

2 Answers2