Post reply

Cannot Decrypt the encrypted columns from the database backup on local machine

  • June 29, 2015 at 11:20 am

    #398667

    Hello All,

    I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.

    USE [master];

    GO

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword';

    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

    GO

    select File_Name

    , CONVERT(nvarchar,DECRYPTBYKEY(File_Name))

    from [test].[dbo].[Orders_Customer]

    I tried doing above still no luck.

    Can anybody point me in the right direction? Any help is greatly appreciated.

    Thanks

  • June 29, 2015 at 11:31 am

    #1808856

    I'd start by reading this, https://msdn.microsoft.com/en-us/library/bb964742(v=sql.120).aspx.

    I haven't done much with encryption in a long time, but you have to have the same keys on both servers.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • June 29, 2015 at 11:36 am

    #1808857

    Hi Jack,

    I appreciate your quick response. I tried creating the same keys on my local SQL server as my main server has. Still no luck. Am I missing something here?

    Thanks

  • June 30, 2015 at 7:46 am

    #1809126

    Still stuck with this issue. Any suggestions are greatly appreciated? Need help guys. Thanks.

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

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