SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Unable to decrypt with restored certificate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tiiinygecko
Starting Member

USA
3 Posts

Posted - 01/06/2014 :  17:29:35  Show Profile  Reply with Quote
I submitted the following T-SQL:

--BACKUP CERTIFICATE EncryptTestCert
-- TO FILE = N'c:\backup\EncryptTestCert.cer'
-- WITH PRIVATE KEY
-- ( FILE = N'c:\backup\EncryptTestCert.pvk',
-- ENCRYPTION BY PASSWORD = N'T0yp0calypse'
-- )

--DROP SYMMETRIC KEY PrivateKey1

--DROP CERTIFICATE EncryptTestCert

--CREATE CERTIFICATE EncryptTestCert
--FROM FILE = N'C:\backup\EncryptTestCert.cer'
--WITH PRIVATE KEY (FILE = N'C:\backup\EncryptTestCert.pvk',
--DECRYPTION BY PASSWORD = N'T0yp0calypse')

--CREATE SYMMETRIC KEY PrivateKey1
--WITH ALGORITHM = TRIPLE_DES ENCRYPTION
--BY CERTIFICATE EncryptTestCert

--OPEN SYMMETRIC KEY PrivateKey1 DECRYPTION
--BY CERTIFICATE EncryptTestCert
--SELECT CONVERT(nvarchar(50),DECRYPTBYKEY(encryptedcolumn)) AS DecryptSecondCol
--FROM EncryptionTest1

However, the return data just contains nulls, instead of the original decrypted data. You can see above that I deleted the certificate, but then restored the certificate from backup. However, it doesn't work.

What am I doing wrong?

Tiiinygecko
Starting Member

USA
3 Posts

Posted - 01/08/2014 :  12:14:49  Show Profile  Reply with Quote
Does no-one know how I can restore both the certificate and the symmetric key?
Should I have asked this in one of the other sub-forums?
Go to Top of Page

Tiiinygecko
Starting Member

USA
3 Posts

Posted - 01/09/2014 :  16:24:13  Show Profile  Reply with Quote
The following works:

--CREATE SYMMETRIC KEY myGoodkey1
--WITH ALGORITHM = TRIPLE_DES,
--KEY_SOURCE = 'MyKeyS0urce',
--IDENTITY_VALUE = '0h n0, Identity'
--ENCRYPTION BY CERTIFICATE EncryptTestCert

--OPEN SYMMETRIC KEY myGoodkey1 DECRYPTION
--BY CERTIFICATE EncryptTestCert
--UPDATE EncryptionTest1
--SET encryptedcolumn = ENCRYPTBYKEY(KEY_GUID('myGoodkey1'),testcolumn)

--OPEN SYMMETRIC KEY myGoodkey1 DECRYPTION
--BY CERTIFICATE EncryptTestCert
--SELECT CONVERT(nvarchar(50),DECRYPTBYKEY(encryptedcolumn)) AS DecryptSecondCol
--FROM EncryptionTest1

--DROP SYMMETRIC KEY myGoodkey1

--CREATE SYMMETRIC KEY myGoodkey1
--WITH ALGORITHM = TRIPLE_DES,
--KEY_SOURCE = 'MyKeyS0urce',
--IDENTITY_VALUE = '0h n0, Identity'
--ENCRYPTION BY CERTIFICATE EncryptTestCert

--OPEN SYMMETRIC KEY myGoodkey1 DECRYPTION
--BY CERTIFICATE EncryptTestCert
--SELECT CONVERT(nvarchar(50),DECRYPTBYKEY(encryptedcolumn)) AS DecryptSecondCol
--FROM EncryptionTest1
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000