Introduction to SQL Server Common Language Runtime Integration

Posted 2011-02-19

Generate an ASP.Net Membership Password Hash Using a SQL Server CLR UDF

After a little less than six months on the ASP.NET Forums, I decided to move to on. Spend 15 or 20 minutes browsing the site and you'll figure out why - on a number of fronts it's a mess. In fact, as I write this article the link to search for my posts is sending a HTTP 500 status code. Par for the course. Now I can be found at Stack Overflow, much better!

Anyway, last night I saw an interesting question about generating a ASP.Net Membership Password Hash from the SQL Server side, presumably to migrate existing users to another database without forcing the users to change their password, or perhaps the OP was writing a custom Membership Provider.

SQL Server 2005 and above suport common language runtime (CLR) integration. It's just a matter of following a few simple steps.

Configure the Database

Set the compatibility level to 2005 or above

Right-click on the database, select Properties:
Select Options, then set Compatibility level:

.NET code:

  [SqlFunction(
    IsDeterministic = true, IsPrecise = true, 
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None
  )]
  public static string EncodePassword(string pass, string salt) {
    byte[] bytes = Encoding.Unicode.GetBytes(pass);
    byte[] src = Convert.FromBase64String(salt);
    byte[] dst = new byte[src.Length + bytes.Length];
    Buffer.BlockCopy(src, 0, dst, 0, src.Length);
    Buffer.BlockCopy(bytes, 0, dst, src.Length, bytes.Length);
    using (SHA1CryptoServiceProvider sha1 = new SHA1CryptoServiceProvider()) {
      return Convert.ToBase64String(sha1.ComputeHash(dst));
    }
  }

Some things you need to know when writing the class:

At a minimum you need to include the following namespaces:
- using System.Security.Cryptography;
- using System.Data.SqlTypes;
- using Microsoft.SqlServer.Server;
The class must obviously be public.
The methods must be declared static
Build the class as an assembly (.dll)

Deploying the Assembly to SQL Server

Now run the following script on the SQL Server.

  sp_configure 'clr enabled', 1
  GO
  RECONFIGURE
  GO

  IF OBJECT_ID (N'dbo.EncodePassword', N'FS') IS NOT NULL
    DROP FUNCTION dbo.EncodePassword;    
  IF EXISTS (SELECT name FROM sys.assemblies WHERE name='UDF')
  DROP ASSEMBLY UDF

  CREATE ASSEMBLY UDF FROM 'FULL_PATH_TO.dll' WITH PERMISSION_SET=SAFE    
  GO

  CREATE FUNCTION EncodePassword(
    @pass NVARCHAR(4000),
    @salt NVARCHAR(4000)
  )
  RETURNS NVARCHAR(4000)
  -- return NULL if any input parameter(s) are NULL
  WITH RETURNS NULL ON NULL INPUT
  AS
  EXTERNAL NAME UDF.[NAMESPACE.CLASSNAME].EncodePassword
  GO

Some things you need to know when deploying the .dll:

The UDF(s) are installed per database.
Replace FULL_PATH_TO.dll above to the full path of your assembly
Replace [NAMESPACE.CLASSNAME] with the namspace, if any and name of your class.

Calling the UDF from SQL Server

  SELECT UserName,Password
  ,dbo.EncodePassword('PASSWORD', PasswordSalt) As TestPassword 
  FROM aspnet_Users U 
  JOIN aspnet_membership M ON U.UserID = M.UserID