Click here to Skip to main content
11,576,977 members (60,635 online)
Rate this: bad
Please Sign up or sign in to vote.
How do I encrypt and decrypt any string using sql?
I have tried following option, but not able to get same values of encrypted and decrypted values.

DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'pass123');
select EncryptByPassPhrase('key', 'abc' )
select convert(varchar(100),DecryptByPassPhrase('key', '0x01000000E6A0E10AF0144E38670D9B8E92E6E22787F9CD27B467E253'))
Posted 1-May-12 3:17am
Pablo Aliskevicius at 1-May-12 10:19am
Reason for my vote of 5
This can happen to anyone.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

You're sending a string to DecryptByPassPhrase, but the function expects a varbinary parameter.

declare @encrypt varbinary(200) 
select @encrypt = EncryptByPassPhrase('key', 'abc' )
select @encrypt 
select convert(varchar(100),DecryptByPassPhrase('key', @encrypt ))

The results are:

(1 row(s) affected)

(1 row(s) affected)

Hope this helps,
Mukund Thakker at 2-May-12 0:42am
declare @encrypt varbinary(200)
select @encrypt = EncryptByPassPhrase('key', 'abc' )
select @encrypt

If you encrypt with following script it gives different output every time. if I apply the same it gives NULL result
Pablo Aliskevicius at 2-May-12 1:46am
Different output every time is expected. Some encryption algorithms introduce random noise in the encrypted string; this makes them harder to break. The whole issue of encryption, with concepts like 'evidence' and 'enthropy' (which have, in the context of encryption, different meanings than their usual ones) has filled dozens of books.
Mukund Thakker at 3-May-12 5:02am
Pablo Aliskevicius at 3-May-12 7:18am
Why not?
Rakesh Bhavsar at 6-Jul-12 4:51am
I am agree with Mukund Thakker.
"When we run both functions in a single query it works fine. But when we run it using separate sql scripts it will not work."

Scenario is as follows:
I have created two different stored procedures each one for EncryptByPassPhrase('key', 'abc' ) and another for convert(varchar(100),DecryptByPassPhrase('key', @encrypt))
I am storing procedure 1 result into database.
Procedure 2 will retrived stored encrypted varbinary value and do decryption and it is returning 'NULL' value.
Frankly I din't get the exact use of sql 'DecryptByPassPhrase()' function.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 221
1 DamithSL 155
2 OriginalGriff 155
3 Abhinav S 130
4 Afzaal Ahmad Zeeshan 95
0 OriginalGriff 820
1 Sergey Alexandrovich Kryukov 631
2 Abhinav S 528
3 F-ES Sitecore 420
4 Suvendu Shekhar Giri 365

Advertise | Privacy | Mobile
Web04 | 2.8.150603.1 | Last Updated 12 Aug 2014
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100