Sometimes you need to store credentials

This blogpost is both an introduction to building and managing CLR functions in SQL Server as well as a guide to avoid storing sensitive information that is only used for authentication.

If you want to know more about the bcrypt algorithm itself I recommend this wikipedia entry: https://en.wikipedia.org/wiki/Bcrypt

If you want to play along at home, here is a link to the demo sql file, the c# source code for the assembly and the actual dll if you want to save time and not compile it yourself.
http://1drv.ms/1K8nPJk

The basic gist of bcrypt is that each record has it’s own salt and that the hashing is done in a large number of iteration with the intention of being slow.
Being slow is key in protection against brute force attacks on a stored password.
Individual salts protects users who have the same passwords, their hashes end up being different.

This implementation is done in C# and has three public functions that become exposed.

public static string HashPassword(string password, string salt)
public static string GenerateSalt(int logRounds)
public static bool CheckPassword(string plaintext, string hashed)

Lets say we have a database named demo in which we want to put this functionality.
The first thing we do is enable clr.

USE demo;
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE

Then we can import our dll, either look through the code and build it yourself or take my word for it and put my dll into C:\temp.
With the assembly in place we can create functions in our database that reference the external assembly.

CREATE ASSEMBLY BCrypt FROM 'C:\temp\BCrypt.dll';
GO
CREATE FUNCTION fn_BcryptGenerateSalt(@logRounds INT) RETURNS NVARCHAR(29) 
AS EXTERNAL NAME [BCrypt].[BCrypt].[GenerateSalt];
GO
CREATE FUNCTION fn_BcryptHashPassword(@password NVARCHAR(255),@salt NVARCHAR(29)) RETURNS NVARCHAR(60) 
AS EXTERNAL NAME [BCrypt].[BCrypt].[HashPassword];
GO
CREATE FUNCTION fn_BcryptCheckPassword(@plaintext NVARCHAR(255),@hashed NVARCHAR(60)) RETURNS bit
AS EXTERNAL NAME [BCrypt].[BCrypt].[CheckPassword];

To try out the functions we can do something like this, remember that the salt that I got is of course different than any you generate.

--Get a salt
SELECT
dbo.fn_bcryptgeneratesalt(14) --This salt will be used for a password with 2^14 (16384) rounds of hashing.
-- for example I got $2a$14$eR5F.sU09ygkgmweesx7Ze

--Generate a password hash from my secret string "mypass123$³" and the salt.
SELECT dbo.fn_BcryptHashPassword(N'mypass123$³',N'$2a$14$eR5F.sU09ygkgmweesx7Ze')
-- for example I got $2a$14$eR5F.sU09ygkgmweesx7ZeMrtuH.Zbh1WhfHa7C1PC/Qc5EDc5OC2

--Check if a string is the correct password for a particular hash or not.
SELECT
dbo.fn_BcryptCheckPassword(N'mypass123$³',N'$2a$14$eR5F.sU09ygkgmweesx7ZeMrtuH.Zbh1WhfHa7C1PC/Qc5EDc5OC2') as correct
,dbo.fn_BcryptCheckPassword(N'mypass123',N'$2a$14$eR5F.sU09ygkgmweesx7ZeMrtuH.Zbh1WhfHa7C1PC/Qc5EDc5OC2') as wrong1
,dbo.fn_BcryptCheckPassword(N'mypass123³',N'$2a$14$eR5F.sU09ygkgmweesx7ZeMrtuH.Zbh1WhfHa7C1PC/Qc5EDc5OC2') as wrong2

Here is a mock up of some objects using these functions to produce an application where users can sign up with usernames, emails and log on using passwords that are not stored in the database.

CREATE TABLE dbo.SiteUser(
Id int identity(1,1) NOT NULL primary key clustered
,Name varchar(30) NOT NULL
,PasswordHash CHAR(60) NOT NULL
,Email VARCHAR(100) NOT NULL
,ValidEmail bit NOT NULL default 0
);
GO
CREATE PROCEDURE dbo.xSP_CreateUser
(@UserName NVARCHAR(30),@UserPassword NVARCHAR(255),@Email VARCHAR(100),@logRounds INT = 14)
AS
BEGIN
IF NOT EXISTS (SELECT Id FROM dbo.SiteUser WHERE Name = @UserName)
BEGIN
INSERT INTO dbo.SiteUser(Name,PasswordHash,Email)
SELECT
@UserName
,dbo.fn_BcryptHashPassword(@UserPassword,dbo.fn_BcryptGenerateSalt(@logRounds))
,@Email
RETURN 0
END
ELSE BEGIN
RAISERROR ('User already exists',16,1);
END
END
GO
EXEC xSP_CreateUser @UserName = 'David', @UserPassword= 'mittlösen123$³', @Email = 'dsoderlund@solidq.com'
GO
SELECT * FROM SiteUser

Here is a script to clean up anything left behind while toying around with these objects:

--Cleanup script
USE demo;
DROP PROCEDURE dbo.xSP_CreateUser;
DROP TABLE dbo.SiteUser;
DROP FUNCTION dbo.fn_BcryptHashPassword;
DROP FUNCTION dbo.fn_BcryptGenerateSalt;
DROP FUNCTION dbo.fn_BcryptCheckPassword;
DROP ASSEMBLY BCrypt

GO
sp_configure 'clr enabled', 0
GO
RECONFIGURE

Remember that if you are serious about creating this kind of application yourself the first thing you should do is to make sure that any traffic to and from your database server is encrypted or all that hashing is for nothing since the password could get intercepted in the function call anyway.

If you have any good feedback on these types of solutions feel free to comment here or shoot me a message through your favorite flavor of social media.

Thanks for reading!