Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Restoring database to another server cannot open SYMMETRIC key Expand / Collapse
Author
Message
Posted Monday, January 5, 2009 10:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 15, 2010 4:06 PM
Points: 98, Visits: 54
After restoring the database into a another server, I run the following T-SQL command:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

and when I try to open the SYMMETRIC key to retrieve the data on the encrypted field with a SELECT statement I get the following message (the same SELECT statement on the original server works fine as expected).

Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.

(402 row(s) affected)
Msg 15315, Level 16, State 1, Line 7
The key 'SymKey' is not open. Please open the key before using it.

Thanks.



Post #629972
Posted Monday, January 5, 2009 1:52 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
What is the symmetric key encrypted with? If it's a certificate or asymmetric key, are you able to open that key explicitly?

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #630123
Posted Monday, January 5, 2009 2:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 15, 2010 4:06 PM
Points: 98, Visits: 54
Thanks for your help.

>>>What is the symmetric key encrypted with?
>>>If it's a certificate or asymmetric key, are you able to open that key explicitly?

The symmetric key is encrypted with a Certificate.

No, I am not able to open the key. When I try to open the key with the statement:

OPEN SYMMETRIC KEY SymKey
DECRYPTION BY CERTIFICATE Cert

I get the error

Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.

I have also imported the Service Master Key from the original server and I still have the same problems.




Post #630128
Posted Monday, January 5, 2009 2:17 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
And the certificate was encrypted by the database master key and not a password? If you export the database master key from the original server and restore it on the new, can you open the database master key manually and then everything works?


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #630143
Posted Monday, January 5, 2009 2:30 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 15, 2010 4:06 PM
Points: 98, Visits: 54
And the certificate was encrypted by the database master key and not a password?

Yes it was encrypted by the DBMK because it was not encrypted with a password.

If you export the database master key from the original server and restore it on the new, can you open the database master key manually and then everything works?

No, I open the key by using the following command:


OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd'

and then I try to open the symmetric key and it gives me the error.



Post #630148
Posted Monday, January 5, 2009 3:55 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Can you open the certificate? And what function are you using to open the Symmetric Key and return the data?


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #630193
Posted Tuesday, January 6, 2009 6:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 15, 2010 4:06 PM
Points: 98, Visits: 54
Before continuing, please let me thank you for your help so far.

After your previous posting I went ahead and tried the following in the new server:

DECLARE @plaintext nvarchar(2000), @cyphertext nvarchar(2000)
SET @plaintext = N'AAA'
SET @cyphertext = EncryptByCert(Cert_ID('MyCert'), @plaintext)
PRINT @cyphertext
SELECT CAST(DecryptByCert(Cert_ID('MyCert'), @cyphertext) AS NVARCHAR)

This works in the original server but in the one where I restored the database instead of getting AAA at the end of the above code I get NULL. So it appears that the certificate can be used to encrypt but not to decrypt. However, when I run the same code in the original server, I get AAA at the end.

I hope this answers your question.



Post #630497
Posted Thursday, April 9, 2009 4:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 13, 2009 3:34 PM
Points: 3, Visits: 10
Just wondering if you were able to fix the issue.


We have the same issue.

We have 3 DB servers - dev , test and Prod. We have no decryption problems when we restore the database from Test to Dev.

We have problems when we restore from Prod to Test.

Only difference that I see is that our Prod Server is a Windows 2003 Enterprise Edition. Our Dev and Test servers are Windows 2003 Standard Edition.

Any feedback is greatly appreciated.

Thanks.


Post #694517
Posted Thursday, April 9, 2009 5:02 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Are you able to open the master key on the database?

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #694526
Posted Thursday, April 9, 2009 5:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 13, 2009 3:34 PM
Points: 3, Visits: 10
Yes.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'xxxxxx' was successful.

OPEN SYMMETRIC KEY XXX_KEY DECRYPTION BY CERTIFICATE XXX_CERTIFICATE is where we got the following error

'an error occurred during decryption'


Also got the same results for the test script posted by diogenes1331.
When we ran it in our Prod server,we got 'AAA'. When we ran it in the Test server, we got NULL
Post #694542
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse