Passwords as Binary Data Type?

[byrne1]

Has anyone worked with storing alphanumeric passwords as binary fields in databases? I have worked up the following command to convert an NVARCHAR field to a BINARY type and it seems to work fine.

UPDATE USERS SET USERPASS=CAST(CAST('TEST' AS NCHAR(5)) AS BINARY) WHERE USERID='USER1

But, I can just as easily convert it back to NVARCHAR and obtain the password with this command:

SELECT CAST(CAST(USERPASS AS NCHAR(20)) AS NVARCHAR(20)) FROM USERS WHERE USERID='USER1'

I guess my question is how can I encrypt the password to make it more difficult to convert back to NVARCHAR?

 

[dbadmin]

There are two options:

1. You can use the ENCRYPT function. The set back is that you would never be able to see that the password is, just verify against it. Example:

To set the password:

INSERT INTO myTable (Username, Password)
VALUES ('dbAdmin', ENCRYPT('password'))

To check against it:

DECLARE @checkPwd varchar(100)

SELECT @checkPwd = pwd
FROM myTable WHERE username = 'dbAdmin'

IF @checkPwd = ENCRYPT('password')
PRINT 'Password is confirmed'
ELSE
PRINT 'Wrong password'


2. Buy an ActiveX COM object and use that to encrypt & decrypt strings...

 

[byrne1]

Thanks! I think I'll try the ENCRYPT function in SQL.

 

[Guest_imported]

byrne1,

Thought you would also like to see this example from

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9809

DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = 'test'
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)