locked
How to Take backup a database which has encrypted data by ASYMMETRIC KEY RRS feed

  • Question

  • Hi,

    I have database in which I have used Asymmetric key for encrypting some of the columns in the table. And encrypt/decrypt is working fine in this particular database without any issues. 

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<some password>'; 
    GO 
    CREATE ASYMMETRIC KEY MY_ASSymmetricKey
    WITH ALGORITHM = RSA_2048;
    GO 
    -----------------------------------------------------------------
    ------To Create the Asymmetric key, and Symmetric key chain----
    CREATE SYMMETRIC KEY MY_SymKeyChain
    WITH ALGORITHM = AES_256
    ENCRYPTION BY ASYMMETRIC KEY MY_ASSymmetricKey;

    I am getting issues when I backup the database and tried to restore it in New server.

    In the new server while using decrypt command the data is not decrypted instead getting all the rows with null values.

    I could able to see the keys availale after restoring in the new server, and also to make sure I have given grant permission for the user account.

    SELECT *
    FROM sys.symmetric_keys

    Let me know if there is any way to resolve this problem. Do I need separately backup the keys and restore, in such case please let me know the procedure. 

    Wednesday, September 16, 2015 10:50 AM

Answers

  • This is because the two servers have different server master keys.

    You either need to open the database master key explicitly using the password, or reencrypt the database master key with ALTER MASTER KEY. See Books Online for details.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, September 16, 2015 9:16 PM