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: ,
  • We do a good line in sending databases via the post here in the UK :-)

  • Thanks for the demo of this last night Andrew - well done for keeping your head as projectors blew up and the local scallies were banging on the windows!

    Seriously though, this and the backup compression are little tweaks which make 2008 an attractive proposition from the IT Professional's standpoint.

    Thanks for a very enjoyable event.

  • Andrew's turning into a really useful guy. He also has a great post on TDE here . :-) Technorati Tags:

  • Andrew's turning into a really useful guy. He also has a great post on TDE here . :-) Technorati

  • Is there any difference between this solution and just using EFS on your filesystem? Using EFS has been an option since Win 2k and from what I see it provides the same results.

  • John.  EFS won't protect the backup or the MDF, LDF database files if you put those on a  CD, but TDE does do that.  Andrew

  • Here are 2 big disadvantages to EFS and BitLocker from MS "Detaching or backing up the database to a different volume that is not protected by EFS or BitLocker causes any protection the file currently has to be lost. The other disadvantage of BitLocker is the broad scope of protection. Because the entire volume is unlocked, any user with access to a computer that can access the files on disk can access the data in plaintext."  Reference

  • Sean,

    Completely agree, but Your point about bitlocker is also true of the RSA encryption used by manby UK govenrment departments.

    so it's important to pick the right tool for the job.  field encryption within the database, might make sense to protect credit card numbwers.  TDE stops the database being moved to another location without the certificate. EFS / bitlocker are the only offerings form Micrsoft to protect filestream, and if these aren't sufficent to meet your needs then there are numerous third aprty tools thiat might.  


  • How do I prevent access to the database and its objects in the SQL server Management Studio. I want only my application should use the data and no-one should be able to work in the back-end including administrator.

  • I'm not sure why you would want to do this unless your app provides all of the functionality of SSMS (backup, access to T-SQL and to monitor the health of the databases etc.)

    All you can do is use a strong sysadmin password and chnage the default port that SQL uses (1433).  You can't stop soemone installing SSMS, and SQL erver doesn't care what tool you connect with.

  • Hi Andrew,

     I read about SQL Server 2008 TDE and I got a basic question.

    For example, if you have a table called users with userid, username and password as columns which returns the following data when you run a query against users tabel.

    SELECT * FROM users

    Userid   Username  Password

    1        John      John345

    2        Paul      Paul789  

    After you implement TDE for a specific database based on your steps described above, when you run the same  query how does the data look like ?

    Does the result data is in encrypted form or normal form as above ?

    Thank you.



  • Satya

    The data will be returned exactly as before, whether you run the query from an app, excel, or mamagement studio.

    TDE only prpotects the database 'at rest' and doesn't affect the queries yoiu run against it when it is in it's usual location.  so TDE kicks in when you detach/attach or backup/restore in that you'll need the key to unlock the database if you restore or attach to a new instance virtual machine or physical server.


  • Andrew,

    There are no security if you can't stop someone installing SSMS. People can create any SQL to get entire database scheme and export data by SSMS.

    What about some body using tool as in



  • Jes

    Your utility which might well be useful is nothing to do with Transparent Data Encryption in SQL Server 2008.  If you give your users full access to your databases then I agree they can install ssms and export the entire database.  However that's what schemas are for and you can also use the audit functions in SQL Sevrer 2008 to watch and alert for the kinds of queries theat are requesting sensitive data in bulk.

  • Andrew,

    Can we ask MSSQL to do some kind of updates to prevent viewing WITH ENCRYPTION store procedures and functions and etc. ( Decrypt ) ? Otherwise it is too dangers for database security since Windows Authorization can do anything on database if they get in MSSQL server?



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