kpwimberger (1/18/2013)
Our DBA has assigned my user account ALL permissions on the certificate, and reference permission on the symmetric key, but when I run this code:
USE AdventureWorks;OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCert;
UPDATE
Sales.CreditCard_ENCRYPTION
SET
CardNumbENC = EncryptBykey(Key_GUID('TestSymmetricKey'), CardNumber);
SELECT
CardNumber
, CardNumbENC
FROM
Sales.CreditCard_ENCRYPTION;
... gets me this error:
Msg 15334, Level 16, State 1, Line 20
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.
If, as mentioned above, CONTROL permission on the cert, and REFERENCE permission on the symmetric key, should allow me to access SQL Server Automatic Key Management to avoid entering the cert password, what went wrong?
Ugh. :crazy:
If TestCert is protected with a password you must provide the password to use it, you can check protection type in a view
select * from sys.certificates
CREATE CERTIFICATE CertTest WITH SUBJECT = 'Certificate for test purpose only'
go
CREATE SYMMETRIC KEY TestKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE CertTest
go
select pvt_key_encryption_type_desc from sys.certificates where name='CertTest'
--now cert is protected by DMK and you don't need to pass any password do open it
OPEN SYMMETRIC KEY TestKey DECRYPTION
BY CERTIFICATE CertTest
--close SYMMETRIC KEY
CLOSE SYMMETRIC KEY TestKey
--set CertTest protected by password=pass
alter certificate CertTest WITH PRIVATE KEY(ENCRYPTION BY PASSWORD = 'pass' )
select pvt_key_encryption_type_desc from sys.certificates where name='CertTest'
--now cert is protected by password and you must provide valid password
OPEN SYMMETRIC KEY TestKey DECRYPTION
BY CERTIFICATE CertTest with password='pass'
CLOSE SYMMETRIC KEY TestKey
--set CertTest protection to DMK (cert protected with password)
alter certificate CertTest WITH PRIVATE KEY(DECRYPTION BY PASSWORD = 'pass' )
CLOSE SYMMETRIC KEY TestKey
drop SYMMETRIC KEY TestKey
drop certificate CertTest