Post reply

Database Master Key

  • September 21, 2007 at 12:15 pm

    #118716

    Hi, this is my problem.

    I have a database that I'm moving back and forth between two SQL server 2005 on two different machines. And I have encrypted columns in some tables using native SQL server 2005 encryption. But the problem is that each time I move the data from one machine unto another I have to regenerate the database master key (without the FORCE option) else it's not able to decrypt my data.

    Is this by design or there is anything I'm doing wrong, try to go through the BO but nothing much.

    Thanks

  • September 21, 2007 at 12:54 pm

    #735348

    I've not worked with encryption directly but have done some research. From what I've seen, the issue is that you have to backup and restore the master key (apparently separate from a regular backup).

    Here's the basic commands:

    BACKUP SERVICE MASTER KEY TO FILE='file_name_path' ENCRYPTION BY PASSWORD = 'password'

    -- SQL will use the password to encrypt the backup

    RESTORE SERVICE MASTER KEY FROM FILE='file_name_path'

    DECRYPTION BY PASSWORD = 'password'

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

Viewing 2 posts - 1 through 1 (of 1 total)

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