Encrypt and Decrypt a Password using EncryptByPassPhrase and DecryptByPassPhrase This Article explains you how to Encrypt and Decrypt a text.

1. Give More Feedback
2. See More On Microsoft Docs

Give More Feedback

I am using EncryptByPassPhrase of sql server. But i am not able to use where condition properly. DECLARE @logindetails TABLE(uid integer,username varchar(10),password varbinary(100)) insert into @logindetails(uid,username,password) values(1,'abc',EncryptByPassPhrase('12','XXX')) insert into @logindetails(uid,username,password) values(1,'nvt',EncryptByPassPhrase('12','YYY')) select. from @logindetails -this give all rows from table with encrypted values- select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @logindetails -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 @logindetails where password=EncryptByPassPhrase('12','YYY') where as below query works fine. Select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @logindetails 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.

1. How long is the output of EncryptByPassPhrase(), relative to the. I'd forgotten that SQL Server can store trailing spaces but doesn't.
2. SQL Server; SharePoint Products. (Transact-SQL) DECRYPTBYPASSPHRASE (Transact-SQL). ENCRYPTBYPASSPHRASE (Transact-SQL) HASHBYTES.

See More On Microsoft Docs

The reason is easy to demonstrate. 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.