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:\Backups\SampleCert.cer' WITH PRIVATE KEY ( FILE = 'D:\Backups\SampleCert.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:\Backups\SampleCert.cer' WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.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

I think I found out why your statement is not working as you expected.

In the following query, the certificate is re-created from the backup, but only a decryption password (used to open the PVK file) is specified, but no encryption password is present. This will create the CERTIFICATE in SQL Server with the private key protected by the DB master key.

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh');

Instead of

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh',

ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C');

You can verify if my assumption is correct by running the following statement:

SELECT name, pvt_key_encryption_type_desc FROM sys.certificates

If I am right, you can just change the protection mechanism for the existing CERTIFICATE:

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' );

Let us know if this information helped.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Well today is not my lucky day I am afraid. I can't verify yet.

Last night we rolled out a policy to enforce passwords complexity, and length requirements.

So I am geting an error just trying to run the same script, even though I am using NT Authentication, and have updated my password to conform to the new policy.

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

Does Create Certificate use a different security creditial then the loged in Users?

I even tried using SA with the following set. with the same results.

Code Snippet

Alter Login [sa] with CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Any more idea's where I need to look to figure this out?

|||

Disabling the password policy checks is not available for cryptographic objects (certificates, keys, etc.), the only part of the policy enforced in such objects is the password complexity for newly created or modified objects/passphrases.

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '<<new password that complies with password policy>>' );

--returns '000-00-0000' for 'SampleCert1' since it was able to decrypt

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted, <<new password that complies with password policy>>') as varchar(12)) as 'SampleCert1'

from Table_1

Or if you prefer to use the DBMK to protect your certificate, you can change your query to the following:

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted) as varchar(12)) as 'SampleCert1'

from Table_1

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you for all your help.

Knowing what password I had to fix made it short work geting it working.

Owen.