Encrypt Dara throw a Stored Procedure

Hi,

I'm trying to use a stored procedure to encrypt data but it dosent work fine, this is how I proceeded and that worked well

Code Snippet

CREATE PROCEDURE [dbo].[UpdateUser]

@.CardNumber nvarchar(max),

@.UserID int

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.SecretData varbinary(max)

OPEN SYMMETRIC KEY MY_SYMMETRIC_KEY

DECRYPTION BY CERTIFICATE [MY_CERTIFICATE]

DECLARE @.KeyGuid AS UNIQUEIDENTIFIER

SET @.KeyGuid = key_guid( 'MY_SYMMETRIC_KEY')

SET @.SecretData = encryptbykey( @.KeyGuid, @.CardNumber)

UPDATE User

SET

CardNumber=@.SecretData

Where UserID=@.UserID

CLOSE SYMMETRIC KEY MY_SYMMETRIC_KEY

END

but Now I use my Stored Procedure to encrypt the data, but I'm getting bad data when I decrypt.

Code Snippet

CREATE PROCEDURE [dbo].[UpdateUser]

@.CardNumber nvarchar(max),

@.UserID int

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.SecretData varbinary(max)

exec EncryptData @.CardNumber, @.SecretData output

UPDATE User

SET

CardNumber=@.SecretData

Where UserID=@.UserID

END

Code Snippet

CREATE PROCEDURE [EncryptData]

@.ClearData varchar(max),

@.SecretData varbinary(max) output

WITH EXECUTE AS 'DBO'

AS

BEGIN

OPEN SYMMETRIC KEY MY_SYMMETRIC_KEY

DECRYPTION BY CERTIFICATE [MY_CERTIFICATE]

DECLARE @.KeyGuid AS UNIQUEIDENTIFIER

SET @.KeyGuid = key_guid( 'MY_SYMMETRIC_KEY')

SET @.SecretData = encryptbykey( @.KeyGuid, @.ClearData)

CLOSE SYMMETRIC KEY My_SYMMETRIC_KEY

END

Any Idea how to fix this issue

Thanks in advance.

In fact the issue happens frequently; Sometimes when I create procedure with encryption feature it works, sometimes not, even with the same code

Is there a know bug in Sql Server 2005 with the same behaviour?

|||

In procedure EncryptData

@.ClearData varchar(max),

should be

@.ClearData nvarchar(max),

Thanks

Laurentiu