I am using EncryptByPassPhrase of sql server. But i am not able to use where condition properly.

DECLARE @login_details TABLE(uid integer,username varchar(10),password varbinary(100)) insert into @login_details(uid,username,password) values(1,'abc',EncryptByPassPhrase('12','XXX')) insert into @login_details(uid,username,password) values(1,'nvt',EncryptByPassPhrase('12','YYY')) select * from @login_details --this give all rows from table with encrypted values-- select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details --this gives decrypted values But in below query i am using where condition but it does not work and result in 0 rows.Why? Select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details where password=EncryptByPassPhrase('12','YYY') where as below query works fine. Select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details where convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password))='YYY' Why there is a problem if i include encryptbyphrase command in where condition? You need to use the condition that works. English Malayalam Typing Software. The reason is easy to demonstrate.

Encrypt and Decrypt a Password using EncryptByPassPhrase and DecryptByPassPhrase This Article explains you how to Encrypt and Decrypt a text. Adobe Flash Player Activex 9.0 there. Is DecryptByPassphrase secured to use in order to encrypt a column value. I'm able to encrypt and Decrypt a column value using encryptByPassphrase.

Run this: DECLARE @x VARBINARY(8000) = ENCRYPTBYPASSPHRASE('12','YYY'); SELECT @x, CONVERT(VARCHAR(32), DECRYPTBYPASSPHRASE('12', @x)); GO 5 Do you see the same result every time? This is because the encryption adds some magic to prevent predictable results (that is, but for the purpose of this question, this is kind of how 3DES works to produce this symptom). So, the way to do this is to decrypt first, then compare. Of course, this kills sargability on the password column (if it is indexed), forcing a full scan (if this is really the only predicate). If you need efficient seeks on this column (seems unlikely that you'd ever just be checking for the password to match), you might need to consider a 'locked-down' computed/persisted/indexed column.

As an aside, funny that a Google search for has hit #1 as.

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).