Password encryption - in your SProcs!
Whilst working on the login system for a now complete project, I learnt about a couple of unsupported but useful functions in sql - pwdencrypt and pwdcompare. SQL uses these to 1-Way hash passwords. Here's an example of how to use them:
You'll want a simply users table, note the varbinary(256) column for password
CREATE TABLE [Users] (
[Username] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [varbinary] (256) NOT NULL ,
CONSTRAINT [PK_mmj_Users] PRIMARY KEY CLUSTERED
(
[Username]
) ON [PRIMARY]
) ON [PRIMARY]
GO
The create user operation is pretty straightforward:
create PROCEDURE mmj_CreateUser
(
@Username nvarchar(20),
@Password nvarchar(15)
)
AS
INSERT INTO
Users
(
Username,
[Password]
)
VALUES
(
@Username,
CONVERT(varbinary(256),pwdencrypt(@password))
)
And finally, the validate user procedure:
ALTER PROCEDURE ValidateUser
(
@Username nvarchar(20),
@Password nvarchar(15),
@Valid bit OUTPUT
)
AS
SELECT
@Valid = pwdcompare(@Password, [Password])
FROM
Users
WHERE
Username = @Username
So, to sum things up:
pwdencrypt accepts a (n)varchar/(n)char value which you then convert to binary format. It always comes out to length 256. pwdcompare accepts two arguments: the supplied password, and the hashed password. It hashes the supplied password using pwdencrypt, and compares that to the hashed password (your password column). If the supplied password is correct, it returns 1, otherwise 0.