How to use DECRYPTBYPASSPHRASE with an Int Column

I am trying to use EncryptByPassPhrase and DecryptByPassPhrase to persist an int value in a SQL database.

I have 2 original columns (only for testing, I will remove them once this code is working), Base (varchar) and NextIndex (int). I am encrypting their values into 2 varbinary columns like this:

DECLARE @PassphraseEnteredByUser nvarchar(128);
SET @PassphraseEnteredByUser = 'some password';

UPDATE [dbo].[Licenses]
SET EBase = EncryptByPassPhrase(@PassphraseEnteredByUser, Base),
    ENextIndex = EncryptByPassPhrase(@PassphraseEnteredByUser, CONVERT(varchar(10), NextIndex);
GO

Then I am trying to retrieve the values using the DECRYPTBYPASSPHRASE stored procedure:

DECLARE @PassphraseEnteredByUse开发者_StackOverflow中文版r nvarchar(128);
SET @PassphraseEnteredByUser = 'some password';

SELECT TOP 1000 [Id]
      ,[Base]
      ,[NextIndex]
      ,CONVERT(varchar, DECRYPTBYPASSPHRASE(@PassphraseEnteredByUser, EBase)) as DEBase
      ,CONVERT(int, DECRYPTBYPASSPHRASE(@PassphraseEnteredByUser, ENextIndex)) as DENextIndex
  FROM [dbo].[Licenses]

The string columns (Base) is decrypted correctly, but the integer columns is not: an original value of 1 is decrypted as 49, as value of 2 is decrypted as 50, etc.

I suspect my datatypes for my conversion routines are incorrect, but I cannot figure out how to fix it.

I'm not familiar with these functions, but using varbinary rather than varchar should fix it from the MSDN examples. The return types are varbinary.

The hex code for ASCII zero is 30 = 48 in decimal. So your encoded 0x31 is actually 1 but you are reading it back as 49.

So, correctly: decimal 1 will then be varbinary 0x01
Currently, wrong, you have decimal 1 -> varchar '1' -> implied varbinary 0x31

Convert(int,CONVERT(varchar(max), DECRYPTBYPASSPHRASE(@PassphraseEnteredByUser, ENextIndex))) as DENextIndex

Use Varchar(max) for decrypting as well. then convert to int this will work.