Moving table with Encrypted column to different server

  • I have an SQL 2005 Develoment system with one table.

    The table contains an encrypted column of data that was created using a master key, a certificate and a Symmetric key.

    I have moved the table to a 2005 Sql Express database on a different server.

    What do I need in order to read the encrypted data?

    =========================

    This SP works on the Dev server.

    ALTER proc [dbo].[uspGetCryptedSSN]

    @InString varchar(50),

    @OutString varchar(50) OUTPUT

    AS

    SET NOCOUNT ON

    Open Symmetric key TestTableKey

    Decryption By Certificate EncryptTestCert

    select @OutString = (Select dbo.ufngetpc(@InString))

    Close Symmetric key TestTableKey

    ============================

    Do I need to copy the certificate and/or master key and/or Symmetric Key from the original server to the new server?

    If so which files need to be copied and where are they?

    I will continue to search the archives and BOL on this.

    Thanks in advance.

    "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
    "Robert A. Heinlein"

  • DougGifford (11/3/2010)


    I have an SQL 2005 Develoment system with one table.

    The table contains an encrypted column of data that was created using a master key, a certificate and a Symmetric key.

    I have moved the table to a 2005 Sql Express database on a different server.

    What do I need in order to read the encrypted data?

    Do I need to copy the certificate and/or master key and/or Symmetric Key from the original server to the new server?

    Yes, you will need to restore all of these keys.

    If so which files need to be copied and where are they?

    I will continue to search the archives and BOL on this.

    Thanks in advance.

    Not trying to harp on you - but if anyone is thinking about using encryption at all, please thoroughly test what all will be involved to restore the database on a new server. Document this, so that you are ready to go when that aging server you put the database on finally does die. It's really not a matter of "if", but "when" you move to a different sql instance.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thank you Wayne.

    I am in the process of setting up and testing what you suggest.

    My first step is to regenerate the keys so I can read the encrypted data.

    Where might you suggest I find a "How to regenerate encryption keys on a new server?" explaination?

    "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
    "Robert A. Heinlein"

  • This link helped solve my problem!

    http://www.eggheadcafe.com/software/aspnet/30238935/master-key-issue.aspx

    "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
    "Robert A. Heinlein"

  • DougGifford - Friday, November 5, 2010 11:39 AM

    This is what I hate about links... that link is now dead and so is, what appears to be, the correct answer. 🙁

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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