CODEWITHSUNDEEP
sqlencryption
bv8z
bv8z

Reputation: 975

Encrypting SQL Server Data with Certificate and Symmetric Key

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

I successfully created the certificate and 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 encrypting and decrypting 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 so, I get the following error message:

The certificate has a private key that is protected by a user defined password. That password needs to be provided to enable the use of the private key.

Ultimately, what I am trying to do is write a stored procedure that will take some unencrypted data as input, encrypt it, then store it as encrypted varbinary. Then I'd like to write a 2nd 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 rather not have to specify the password directly in the stored procedure. Is there any way to do that? What am I doing wrong in my code above?

Thanks.

Upvotes: 4

Views: 10842

Answers (2)

Paulos02
Paulos02

Reputation: 181

You nead use 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 ;

Upvotes: 1

SilverbackNet
SilverbackNet

Reputation: 2074

You just need to use:

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

Upvotes: 5

Related Questions