March 6, 2015 at 4:45 am
The passwords in table 'users', column 'passwordencrypted' are encrypted. Someone enters a password and I'd like to compare if it is correct. The syntax below seems ok but nothing is returned. Why not?
---
OPEN SYMMETRIC KEY mykey DECRYPTION BY CERTIFICATE mycert;
DECLARE @mypw varchar(300);
SET @mypw = 'test';
SELECT * FROM users WHERE passwordencrypted = EncryptByKey(Key_GUID('mykey'), @mypw);
CLOSE SYMMETRIC KEY mykey;
---
-Michael
March 6, 2015 at 8:53 am
Did you try assigning the encrypted value to a @variable and using the @variable in the where clause ?
March 7, 2015 at 4:41 am
yes I tried that, no difference
March 7, 2015 at 6:05 am
First of all, you shouldn't store even an encrypted password, this is a serious security hole. Correct solution is storing only hash values.
During encryption using EncryptByKey there are added salt with guarantee that the same value encrypted by the same key do not generate the same value.
"When using the .NET cryptography classes, you can specify the salt as the initialization vector argument. In SQL Server, a random salt value is always applied to the encryption. " https://technet.microsoft.com/en-us/library/cc837966%28v=sql.100%29.aspx
For passwords use HASHBYTES with a salt, an example:
http://www.mssqltips.com/sqlservertip/3293/add-a-salt-with-the-sql-server-hashbytes-function/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply