September 20, 2011 at 9:03 am
Hi,
I'm testing encryption due to user requests, I'm doing:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$EncryptionPassword12'
GO
CREATE CERTIFICATE CertificateTest1
ENCRYPTION BY PASSWORD = 'CERTP@ssw0d'
WITH SUBJECT ='Test certificate 1',
START_DATE = '09/19/2011',
EXPIRY_DATE = '09/21/2011'
GO
CREATE SYMMETRIC KEY PasswordFieldSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE CertificateTest1;
GO
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE CertificateTest1;
UPDATE dbo.T_CHEQ_v2 SET ENCRYPTEDBENE =
EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), BENE);
GO
CLOSE ALL SYMMETRIC KEYS
This produce the following 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
Where is my error ? the result should be a column encrypted.
Thanks for your response
June 25, 2012 at 12:50 pm
I'm fairly new to this myself, but as I understand it, since you created the certificate with a password, you need to include the password in the open key command, like so:
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE CertificateTest1 WITH PASSWORD = 'CERTP@ssw0d';
The other option is to create the certificate without a password in which case the database master key will be used to encrypt the certificate, and the password would not be needed at the open key command.
HTH.
June 26, 2012 at 8:24 am
Note that your password is very weak for production. If you're going to use any password at all for automated encryption, it should be as long as is practical, and should be random - it's going to be embedded in code, not hand-typed in.
For SQL Server, in general a 128 character long password is allowed. Note that .NET in specific doesn't do well with extended ASCII such as ¾ or à, and most secondary languages have issues with certain symbols, generally a select few of ', ", <>, [], &, {}, ; `, %, *, or similar. That said, a cryptographically random 128 character password could be composed entirely of numbers and still be a very strong password (and you should at least use upper case, lower case, and numbers, since those almost never give problems) - length dominates everything if they're generated by a crytographically strong random source.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply