March 10, 2010 at 6:52 am
Hi there
I have not worked much on SQL Server 2008 Encryption. But I am aware of creating keys, symmetric keys, certificates in SQL Server 2005 to encrypt the data at column level.
My question is, if I enable TDE at database level, does it encrypt the data in the table level? I mean is there no need to again create encryption at table level?
Thanks
Sunny
March 17, 2010 at 12:08 pm
TDE encrypts the data at rest. That means the file on disk and any backups generated from that database.
It doesn't keep the data encrypted if someone has legitimate access to the database objects, unlike the other forms of encryption. So, for instance, I could encrypt data within a SQL Server column using a symmetric key. If you have SELECT rights against the table but you don't have appropriate rights on the key (or its encrypting key), you can't decrypt the contents of the column. However, if all you're using is TDE and I have SELECT rights against the table, I will see the data as SQL Server will decrypt it for you in memory.
Hope that makes clear the distinction between the two types of encryption.
K. Brian Kelley
@kbriankelley
March 18, 2010 at 8:00 am
Also all backups are encrypted, as well as the mdf and ldf files, and any ndf if you have multiple filegroups.
If you want to restore your database to another server (move prod data to dev to test), you will need to have the server certificate installed on the new server, that you created on your previous server.
Other wise you will get a failure when you try to perform a restore, or attach the files on a different server.
Here are 2 great articles from Jasper Smith. I used them to get started and they really help you gain a quick understanding of TDE.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply