Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008 Transparent Data Encryption and Replication

SQL Server 2008 Transparent Data Encryption and Replication

  • Comments 6
  • Likes

SQL Server 2008 will have a mechanism to encrypt the database at rest, with little overhead, and you can try this in CTP5.  While it is on the instance where it was encrypted it can be accessed by any application with out modification.  However if it is detached/attached or restored to another instance then the key used to encrypt it will need to be available on the new instance.

How does that affect replication?

Well you don't have to encrypt the replicated database, and that maybe OK if you are only replicating a non-sensitive part of the data.  The replication agent will decrypt and encrypt data on the way in and on the way out between the two. However the recommendation is to encrypt the databases involved with a Master Key.

Finally you should be aware that the data going in and out of a database that has been encrypted is not itself encrypted, that is left to the transport layer so if that's important to you then you will need to apply any of the common approaches for securing traffic i.e. VPN, SSL and IPSec. 

For further reading check MSDN here 

Comments
  • PingBack from http://geeklectures.info/2007/12/21/sql-server-2008-transparent-data-encryption-and-replication/

  • Click here to see the Evangelist Elfs dance! - remember to turn the sound on first! Sadly Viral 's image

  • Two interesting blogs found on Microsoft SQL Server 2008 and Encription: SQL Server 2008 Encryption Keys

  • FYI - I got the following error trying to follow the link:

    Content not found  

    We apologize for the inconvenience, but the location you are seeking cannot be found. If you are looking for a particular document, please try one of the following areas:

    If ALSO recommended a Silverlight install. After the Silverlight install, the content was there - so I don't know if it was just a strange issue, or if it the MSDN page requires Silverlight. Just as fyi

  • Regan

    I have silverlight on my machine (well I'd be shot if I didn't!) and I can hit the links no problem @ work & @home

  • Looking for stored procedure code to automate this hybrid encryption stuff so that existing applications do not need to be grossly redesigned. Also, would love if someone figured out how to trap or tigger on SELECT to check if data encrypted and decrypt it somewhat automatically so that views, joins, etc. would still work without having to rewrite.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment