TDE - Managing Certificates.

Question

TDE - Managing Certificates.

kkanaya

SSCommitted

Points: 1615
More actions
January 5, 2016 at 10:33 am

#323362

We use TDE on SQL Server A and have a certificate setup in Master database, DEK on User database has been setup with AES_256.
On SQL Server B it has its own certificate setup in Master database and DEK on User database has been setup with AES_128.
We now need to restore a database called Test from SQL Server B onto SQL Server A. So we first restore the certificate used on SQL Server B onto SQL Server A and then restore the database called Test which then works fine.
We will have number of SQL Servers setup with TDE and if we need to move databases between them, which is the best way of managing TDE and its certificates i.e. without ending up in a mess?
If we need to document them is there a template document that we could use? Our worry is that managing TDE could very easily go out of hand in the above scenario.
Any advice/help welcome!! Thanking you in anticipation.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

kkanaya SSCommitted Points: 1615 More actions · Answer 1

Having investigated further we have two options -

1) We could use a single certificate on all the SQL Servers that will have TDE setup and generate DEK on user databases based on the single certificate. However this defeats the whole purpose of having TDE in the first place.

2) Carry on managing certificates and document them some how, as this option is a lot more secure than the first one.

For ease of use, we also have the option of using the same certificate on servers that hold same/similar data e.g. Live, Stage and Test servers for bespoke applications.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

Please read my articles at the following links

http://www.sqlservercentral.com/articles/Transparent+Data+Encryption+(TDE)/91712/[/url]

http://www.sqlservercentral.com/articles/Security/122707/[/url]

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

kkanaya SSCommitted Points: 1615 More actions · Answer 3

Thank you Perry, have just read through them and they were very helpful.

Thank you once again for your help and guidance.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

you're welcome

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉