Unable to decrypt with restored certificate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Unable to decrypt with restored certificate

Author

Topic

Tiiinygecko
Starting Member

3 Posts

Posted - 2014-01-06 : 17:29:35

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

3 Posts

Posted - 2014-01-08 : 12:14:49

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?

Tiiinygecko
Starting Member

3 Posts

Posted - 2014-01-09 : 16:24:13

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources