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.
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 = 'email@example.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!
Latest posts by David Söderlund (see all)
- Creating an installer for your SSIS-assemblies - July 4, 2016
- Creating and using memory optimized tables in an application - June 16, 2016
- That time I used ON UPDATE CASCADE in production - May 10, 2016