Freigeben über

SQL Server 2008 - Transparent Data Encryption

  • Artikel

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;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'UseStrongPassword1!'; --SMK????
GO
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
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyDBCert

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:

BACKUP CERTIFICATE MyServerCert
TO FILE = 'c:\temp\MyServerCert'
WITH PRIVATE KEY (file='c:\temp\MyServerCertKey',
ENCRYPTION BY PASSWORD='UseStrongPassword1!')

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: SQL Server 2008,encryption

Comments

  • Anonymous
    November 30, 2007
    The comment has been removed

  • Anonymous
    November 30, 2007
    The comment has been removed

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

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

  • Anonymous
    December 05, 2007
    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. http://www.sqlservercentral.com/articles/Administering/implementing_efs/870/

  • Anonymous
    December 07, 2007
    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

  • Anonymous
    November 14, 2008
    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 http://msdn.microsoft.com/en-us/library/cc278098.aspx

  • Anonymous
    November 17, 2008
    The comment has been removed

  • Anonymous
    July 31, 2009
    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.

  • Anonymous
    July 31, 2009
    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.

  • Anonymous
    December 24, 2009
    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. Regards, Satya

  • Anonymous
    January 04, 2010
    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

  • Anonymous
    January 04, 2010
    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 http://www.devlib.net/decryptsql.htm? Thanks, Jes

  • Anonymous
    January 04, 2010
    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.

  • Anonymous
    January 05, 2010
    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? Thx, Jes

  • Anonymous
    January 06, 2010
    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 http://connect.microsoft.com and put in your suggestion.  at the very least you should get a reply fomr the product team

  • Anonymous
    January 13, 2010
    The comment has been removed

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

  • Anonymous
    January 28, 2010
    The comment has been removed

  • Anonymous
    February 27, 2012
    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.

  • Anonymous
    March 12, 2016
    To decrypt SQL server database you can take the help of SQL Decryptor Tool. For more information, visit: http://www.sqlmdfviewer.org/sql-decryptor.html