DecryptByKeyAutoCert With Cert_password Bugged??

Jul 19, 2007

I am having trouble with the DecryptByKeyAutoCert function when I try to provide the cert_password parameter.

According to the BOL, it is the second parameter of the function:
DecryptByKeyAutoCert
( cert_ID , cert_password , { 'ciphertext' | @ciphertext }
[ , { add_authenticator | @add_authenticator }
[ , { authenticator | @authenticator } ]
]
)
However, when I provide a password, I get the following error:
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of DecryptByKeyAutoCert function.

This is totally in contradiction with what the BOL description says:

cert_password

Is the password that protects the private key of the certificate. Can be NULL if the private key is protected by the database master key. varchar.Does anyone have any experience with this? I tried Google already but didn't get too many results, unfortunately.

I do not want to use the master key because that would enable all DBAs to read the encrypted data without knowing any password to decrypt. But I do need to use the automatic function due to the design of our dated VB6 application (i.e. it is impossible to open the key prior to the select due to design of interaction with Crystal Reports).

Also, is there any way to find out what parameter type the function is actually expecting? Where are these functions stored?

PS: I tried this on win2003 SP1/SQL2005 SP1 and winXP SP2/SQL2005 SP2 - same result on both.

Thanks in advance!

View 1 Replies

Why Doesn't This DecryptByKeyAutoCert Work?

Jun 25, 2007

This works:

-- authenticator

CREATE FUNCTION [dbo].[cc2_Helper]( @SecretData VARBINARY(256), @cId INT )

RETURNS NVARCHAR(50)

WITH EXECUTE AS 'DBO'

AS

BEGIN

RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData, 1, convert(varbinary, @cId) ) )

END

go

CREATE VIEW [dbo].[cc2View2]

AS

SELECT CardID as CardID, [dbo].[cc2_Helper](CardNumber, CardID) as CardNumber FROM [dbo].[cc2]

go

GRANT SELECT ON [dbo].[cc2View2] TO [user_low_priv]

This doesn't:

-- auth2/view3

CREATE FUNCTION [dbo].[cc2_Helper2]( @SecretData VARBINARY(256), @vBin VARBINARY )

RETURNS NVARCHAR(50)

WITH EXECUTE AS 'DBO'

AS

BEGIN

RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData, 1, @vBin ) )

END

go

CREATE VIEW [dbo].[cc2View3]

AS

SELECT CardID as CardID, [dbo].[cc2_Helper2](CardNumber, convert(varbinary, CardID)) as CardNumber FROM [dbo].[cc2]

go

GRANT SELECT ON [dbo].[cc2View3] TO [user_low_priv]

WHY? Note that the conversion to VARBINARY was moved from the call to DecryptByKeyAutoCert to the call to cc2_Helper2. That is the only change...

But if I declare @vBin as VARBINARY(256) then it does work! Guess I'm a little confused on declaring vars...anyone can elucidate? Thanks.

View 1 Replies

View Related