Certificate Restore Not Working.

I have been trying to use Encryption to encrypt a few key fields. So my first goal was to create all the items I would need to effectivly use encryption, to encrypt a few key fields.



First I created a sample sql script to create a certificate, create a table and insert some test data encrypted. And then made sure I could decrypt the data encrypted with the certificate.



Second step was to test the backing up of the certificate, droping the certificate to similate a restore of the database. And then a restore of the certificate, and then see if the it was still possible to decrypt the existing encrypted data with the restored certificate.



I receive no errors when restoreing the certificate, but it does not properly decrypt the exisiting data.



Can anyone help and point out my mistake?





--Start Sql----Create Sample Cert
CREATE CERTIFICATE SampleCert1
ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C'
WITH SUBJECT = 'Sample Cert for Demo',
EXPIRY_DATE = '04/18/2017';

--create Sample table with encrypted version
CREATE TABLE Table_1
(Id int NOT NULL IDENTITY (1, 1), SSN_Encrypted varbinary(300) NOT NULL) ON [PRIMARY]
GO

--insert row with encrypted version.
Insert into Table_1
(SSN_Encrypted)
values
(EncryptByCert(Cert_ID('SampleCert1'),'000-00-0000'))

--returns '000-00-0000' for 'SampleCert1' since it was able to decrypt
select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1'
from Table_1

GO

--backup Certificate
BACKUP CERTIFICATE SampleCert1 TO FILE = 'D:BackupsSampleCert.cer'
WITH PRIVATE KEY
(
FILE = 'D:BackupsSampleCert.pvk' ,
DECRYPTION BY PASSWORD = N'728AC41753642403251BF8E7233EC0C' ,
ENCRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh'
);

GO

DROP CERTIFICATE SampleCert1;

GO

--Restore from backup. to simulate a restore from tape
CREATE CERTIFICATE SampleCert1
FROM FILE = 'D:BackupsSampleCert.cer'
WITH PRIVATE KEY (FILE = 'D:BackupsSampleCert.pvk', DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh');

--This should return the same ssn as was encrypted, if returns null the restore failed.
select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1'
from Table_1

--cleanup Removes Certificate from the system
DROP CERTIFICATE SampleCert1;

--removes the temporary table.
Drop table Table_1