Post reply

Transparent Data Encryption

  • July 14, 2011 at 1:55 am

    #239610

    Hi All

    I'm experimenting with database encryption and have the code below

    USE master;

    GO

    CREATE CERTIFICATE TheDBCert

    ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

    WITH SUBJECT = 'TheDB Certificate';

    GO

    USE TheDB;

    GO

    CREATE DATABASE ENCRYPTION KEY

    WITH ALGORITHM = AES_256

    ENCRYPTION BY SERVER CERTIFICATE TheDBCert;

    GO

    ALTER DATABASE TheDB

    SET ENCRYPTION ON;

    GO

    If I don't use the part "ENCRYPTION BY PASSWORD = 'Pa$$w0rd'" then it works fine as I first tested this using a MASTER KEY. If I run it like above then I get the error

    Msg 33101, Level 16, State 1, Line 1

    Cannot use certificate 'TheDBCert', because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.

    I've looked for tutorials for the above but can't find a tutorial that runs through the whole process and doesn't use the MASTER KEY only.

    Any help would be appreciated. 😀

    The Fastest Methods aren't always the Quickest Methods

  • July 14, 2011 at 3:35 am

    #1352653

    you need to create a master key for TDE to work

    The below article gives a understanding of how TDE works

    http://msdn.microsoft.com/en-us/library/bb934049.aspx

    Jayanth Kurup[/url]

  • July 14, 2011 at 4:43 am

    #1352680

    ok 🙂

    if I need the back up from the database to work on another server, do I just need the certificate and private key that the certificate was backed up with? What if the other server has a different Master Key?

    Sorry, some of my questions might be common knowledge but I'm a bit stuck with it

    The Fastest Methods aren't always the Quickest Methods

  • July 14, 2011 at 6:49 am

    #1352771

    The gist of moving a TDE enabled database from one server to another is like this.

    Server A:

    -Backup the Certificate and the Private Key

    Server B:

    -Create the database master key

    -Create a Certificate from file (using the backed up private key)

    -restore the database

    Here's a bit more detail on the process

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • July 14, 2011 at 7:18 am

    #1352807

    thanks, does the master key have to be the same on the other server?. Didn't see the link :-).

    awesome got an example of this 2 work 😀

    The Fastest Methods aren't always the Quickest Methods

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

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