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

SQL Server 2008 - Transparent Data Encryption

  • Comments 20
  • Likes

One of the new things you can do in CTP5 of SQL Server 2008 is to encrypt your databases so that they are protected at rest and so are any backups made from them.  So this prevents anybody from accessing a database without going through the server it belongs to.

To move an encrypted database from one server to another you would need to move the key that encrypted it as well. For example you might send the key be e-mail and then send the database on CD's in the post.

First you need a master key and then a certificate:

USE master;
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate for Sensitive Data' GO

Then you can use this to encrypt the database with this:

USE Retail_DWH

The encryption process runs as a background task and the database is available during the process.

It's a really good idea to back up the certificate as without it you can't get the database or backups back - whihc of course is the whole objective! 

This script backs up the certificate to a temp folder as an example:

TO FILE = 'c:\temp\MyServerCert'
WITH PRIVATE KEY (file='c:\temp\MyServerCertKey',

To move this to another instance or server the first step is to create a master key on the new server:

create master key encryption by password = 'UseDifferentStrongPassword1!'

The certificate can then be restored like this:

create certificate MyServerCert
from file='c:\temp\MyServerCert'
with private key (
file = 'c:\temp\MyServerCertKey',
decryption by password='UseStrongPassword1!')

So very simple to setup, easy to use

Technorati Tags: ,
  • Jes

    I don't understand what you mean by "since Windows Authorization can do anything on database if they get in MSSQL server" for example I can create a login Jes using windows authorisation that only has very limited access to a given database.

    If you mean that a winodws administrator has unlimited access then that's not the case either as I could remove them from the sysdmins role if I want to.

    I would also add that this has nothing to do with transparent data encryption

    Finally if you have a suggestion to improve any Microsoft product then you can go to and put in your suggestion.  at the very least you should get a reply fomr the product team

  • Andrew,

    Can we setup a LOGIN on a database with EXECUTION permission on store procedures and this LOGINcan not view or see what store procedures are coded?



  • How can we hide the actual data in TDE, so that no one can see ?

  • I guess you mean so that only autorised people can see it or I would simply delete it!

    Seriously since sql server 2005 there has been support for encrypting values in the database for example credit card number as there isn't a real need to encrypt the whole database.  These values have to be unencrypted in the application that accesses them.  

    I would need to understand more about what you are trying to do or protect against to offe rmore help than this, so if you're in the UK perhaps we cna take this offline


  • Is there any way to encrypt XML data within SQL Server 2008 R2 edition? Our client having such requirement so i am looking for help on that.

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