SP not displaying output from decrypted value

Question

SP not displaying output from decrypted value

reefshark

Ten Centuries

Points: 1340
More actions
June 10, 2010 at 10:57 am

#223785

Hi -
I've written a stored procedure to encrypt a data value and display the results. This works fine.
I've also written another SP to do the opposite and decrypt the value, only I cannot get the output to display. The SP command runs successfully, but just doesn't give me any visible results. Any help is appreciated!
Here's my SP:
CREATE PROCEDURE [dbo].[usp_GetDecryptedVal]
@EncryptedVal varbinary (256),
@DecryptedVal varbinary (256) OUTPUT
WITH EXECUTE AS OWNER
AS
OPEN SYMMETRIC KEY keyname
DECRYPTION BY CERTIFICATE certname
SET @DecryptedVal = DecryptByKey(@EncryptedVal);
CLOSE SYMMETRIC KEY keyname
Here's the TSQL I used to run the SP:
declare @EncryptedVal varbinary (256),
@DecryptedVal varbinary(256)
Set @EncryptedVal = CAST('0x0000ADCED7A3754290C6305321AD49D801000000C30B447DDF84FAA5C05F832EC0DA82EC9C7704F85B86961DE18477118B6198A5179ED1424E1FC4E32B204EFC35884018' as varbinary(256))
exec usp_GetDecryptedVal @EncryptedVal, @DecryptedVal OUTPUT
print @DecryptedVal -- This value does not display, but I get the message, "Command(s) completed successfully."
BTW - I am running SQL Server 2005 Standard and I have also tried the SP & execution with @DecryptedVal as a varchar but this did not work either.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

what is the actual data you are expecting when you decrypt the value (varchar)? Try to set the parms to that data type instead.

CREATE PROCEDURE [dbo].[usp_GetDecryptedVal]

@EncryptedVal varbinary (256),

@DecryptedVal varchar (256) OUTPUT

WITH EXECUTE AS OWNER

AS

OPEN SYMMETRIC KEY keyname

DECRYPTION BY CERTIFICATE certname

SET @DecryptedVal = cast(DecryptByKey(@EncryptedVal) as varchar(256));

CLOSE SYMMETRIC KEY keyname

then the TSQL to call it

declare @EncryptedVal varbinary (256),

@DecryptedVal varchar(256)

Set @EncryptedVal = CAST('0x0000ADCED7A3754290C6305321AD49D801000000C30B447DDF84FAA5C05F832EC0DA82EC9C7704F85B86961DE18477118B6198A5179ED1424E1FC4E32B204EFC35884018' as varbinary(256))

exec usp_GetDecryptedVal @EncryptedVal, @DecryptedVal OUTPUT

print @DecryptedVal

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

reefshark (6/10/2010)
CREATE PROCEDURE [dbo].[usp_GetDecryptedVal]
@EncryptedVal varbinary (256),
@DecryptedVal varbinary (256) OUTPUT
WITH EXECUTE AS OWNER
AS
OPEN SYMMETRIC KEY keyname
DECRYPTION BY CERTIFICATE certname
SET @DecryptedVal = DecryptByKey(@EncryptedVal);
CLOSE SYMMETRIC KEY keyname

you are only setting the DecryptedVal variable not SELECTing ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Mike01 SSChampion Points: 11395 More actions · Answer 3

you shouldn't have to select it. It's an output parameter

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Mike01 (6/11/2010)
you shouldn't have to select it. It's an output parameter

oh my bad i just overlooked into it.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Kirman Old Hand Points: 335 More actions · Answer 5

Are u getting the decrypted output printed in normal print statment , i.e. not passing any o/p param

DECLARE @EncryptedVal varbinary (256),@DecryptedVal varbinary(256)

SET @EncryptedVal = CAST('0x0000ADCED7A3754290C6305321AD49D801000000C30B447DDF84FAA5C05F832EC0DA82EC9C7704F85B86961DE18477118B6198A5179ED1424E1FC4E32B204EFC35884018' as varbinary(256))

SET @DecryptedVal = DecryptByKey(@EncryptedVal)

PRINT @DecryptedVal

Because for me its not working in the above scenario and as well in the SP , output scenario.

If you have got the answer kindly share.

reefshark Ten Centuries Points: 1340 More actions · Answer 6

Thanks for the help and input. I actually got it to work by using the unicode prefix N'.

DECLARE @return_value int,

@DecryptedVal varbinary(256)

EXEC @return_value = [dbo].[usp_GetDecryptedVal]

@EncryptedVal = 0x0000ADCED7A3754290C6305321AD49D801000000C30B447DDF84FAA5C05F832EC0DA82EC9C7704F85B86961DE18477118B6198A5179ED1424E1FC4E32B204EFC35884018,

@DecryptedVal = @DecryptedVal OUTPUT

SELECT @DecryptedVal as N'@DecryptedVal'

SELECT 'Return Value' = @return_value

GO