SQL Server Data Encryption with Certificate and Symmetric Key

Question

I am trying to encrypt database columns using a certificate and a symmetric key.

I successfully created a certificate and a symmetric key using the following:

CREATE CERTIFICATE MyCertificate ENCRYPTION BY PASSWORD = 'password' WITH SUBJECT = 'Public Access Data' GO CREATE SYMMETRIC KEY MySSNKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCertificate 

I tried to encrypt and decrypt some data using the following:

 DECLARE @Text VARCHAR(100) SET @Text = 'Some Text' DECLARE @EncryptedText VARBINARY(128) -- Open the symmetric key with which to encrypt the data. OPEN SYMMETRIC KEY MySSNKey DECRYPTION BY CERTIFICATE MyCertificate; SELECT @EncryptedText = EncryptByKey(Key_GUID('MySSNKey'), @Text) SELECT CONVERT(VARCHAR(100), DecryptByKey(@EncryptedText)) AS DecryptedText 

When I do this, I get the following error message:

The certificate has a private key that is protected by a user-defined password. This password must be provided in order to use the private key.

Ultimately, what I'm trying to do is write a stored procedure that takes some unencrypted data as input, encrypts it, and then saves it as an encrypted varbinary. Then I would like to write a second stored procedure that will do the opposite, i.e. decrypt the encrypted varbinary and convert it back to a human readable data type. I would prefer not to specify the password directly in the stored procedure. Is there any way to do this? What am I doing wrong in my code above?

Thanks.

Answer 1

You just need to use:

 OPEN SYMMETRIC KEY MySSNKey DECRYPTION BY CERTIFICATE MyCertificate WITH PASSWORD = 'password'; 

Answer 2

You are not using MASTER KEY

Example:

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterPassword'; CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'Public Access Data'; CREATE SYMMETRIC KEY MySSNKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCertificate; OPEN SYMMETRIC KEY MySSNKey DECRYPTION BY CERTIFICATE MyCertificate; SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number', CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number' FROM dbo.Customer_data; CLOSE SYMMETRIC KEY MySSNKey ;