Using the Bcrypt hash algorithm in MS-SQL Server

sql-server

Storing the passwords in plain text within a database is not a good idea. Many people use the same password for their email, online-banking or social network accounts and probably your app. So the passwords should be safe if your database gets into the wrong hands.

So how do you safely store a password?

Use BCrypt.

One way could be to use a library for PHP, Java, Ruby, Python and so on and store the generated hash in MS SQL Server.

The better way would be to have MS SQL Server do the encryption and keep your app server code free of this.

Unfortunately SQL Server doesn’t come with a builtin BCrypt function. But here’s how you can implement it in 10 minutes.

To make BCrypt available in MS SQL Server follow these steps:

The Common Language Runtime (CLR) for the .NET framework makes this possible. It integrates directly with Microsofts SQL Server from version 2005 on. By using .NET we are able to deploy a DLL directly to the SQL Server and map its Methods to functions and stored procedures to be used in T-SQL.

This is the quick way on how you can add this feature to your Server Management Console by doing a little bit of copy & paste.

  • Switch to the database where you want to add Bcrypt and issue the following SQL statements.

  • Turn CLR for your database on, so you are able to use modules that are written for .NET

EXEC sp_configure 'CLR ENABLED' , '1'
GO
RECONFIGURE
GO
ALTER DATABASE mydatabase SET TRUSTWORTHY ON
GO
  • Download this file and run it in your database context.

This file is the dll encoded in hexadecimal values. Your SQL Server will accept this as a bytestream so you don’t have to download a library and place it somewhere on the filesystem.

By the way: In this file you will find the statement WITH PERMISSION_SET=SAFE. This sets restrictions for executing these functions so they can’t access the filesystem, network, registry or other external ressources.

  • Now map the assembly methods BCrypt and CheckPassword to scalar valued functions, so they are available in T-SQL.

BCrypt-Function:

CREATE FUNCTION [dbo].[BCrypt](@password [nvarchar](4000), @rounds [int])
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [BCrypt].[BCryptPackage.UserDefinedFunctions].[BCrypt]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'BCrypt'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'BCryptAssembly' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'BCrypt'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=813 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'BCrypt'

And the CheckPassword function:

CREATE FUNCTION [dbo].[CheckPassword](@password [nvarchar](4000), @hashed [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [BCrypt].[BCryptPackage.UserDefinedFunctions].[CheckPassword]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'CheckPassword'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'BCryptAssembly' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'CheckPassword'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=820 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'CheckPassword'

Done.

Now check if everything is working:

DECLARE @passwd VARCHAR(60)
DECLARE @crypted_passwd VARCHAR(60)
SET @passwd = 'weakPassword1234'
select @crypted_passwd = dbo.Bcrypt('weakPassword1234',10)
print @crypted_passwd
-- Check if the passwords match
select dbo.CheckPassword(@passwd,@crypted_passwd)

Note: You can also do all this manually by downloading the source and compiling it with Visual Studio 2005 or higher, using a C# database project.

More information on bcrypt in .NET and the C# source code is available here.

share