Post reply

Retrieving decrypted string from stored procedure

  • December 8, 2008 at 12:11 pm

    #197059

    Hi,

    I'm trying to decrypt a string within a stored procedure but it's giving a conversion error. Here's the Stored Procedure:

    Alter PROCEDURE SelectUserPassword

    @inUserIDUNIQUEIDENTIFIER

    AS

    BEGIN

    declare @outUserPassword AS VARCHAR(50)

    OPEN SYMMETRIC KEY keyUserPassword DECRYPTION BY CERTIFICATE certUserPassword

    SELECT@outUserPassword = CONVERT(varchar, DecryptByKey(UserPassword))

    FROMgbPassword

    WHEREUserID = @inUserID

    RETURN @outUserPassword

    END

    The original string was varchar(50) and I can decrypt and view the string just fine from TSQL, but the above code gives this error:

    Msg 245, Level 16, State 1, Procedure SelectUserPassword, Line 23

    Conversion failed when converting the varchar value 'Test1' to data type int.

    The Line 23 it refers to is the DecryptByKey line.

    Any suggestions? Thanks...

    Sam Alex

  • December 8, 2008 at 1:12 pm

    #909330

    The issue I believe is the fact that you are returning the password in the stored procedure using the RETURN statement. This is trying to convert it to INT i expect as it is used for return codes and not values by default. I think you should either just return the password as a select statement in the sp or as an output parameter.

  • December 8, 2008 at 9:28 pm

    #909454

    Are you using @test1 somewhere in your code? I think it's the return value as well, but some code is missing.

Viewing 3 posts - 1 through 2 (of 2 total)

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