tsql - Generate ASP.Net Membership password hash in pure T-SQL -

i'm attempting create pure t-sql representation of default sha-1 password hashing in asp.net membership system. ideally, this:

username           password              generatedpassword cbehrens           34098kw4d+fkj==       34098kw4d+fkj== 

note: that's bogus base-64 text there. i've got base64_encode , decode functions round-trip correctly. here's attempt, doesn't work:

select username, password, dbo.base64_encode(hashbytes('sha1', dbo.base64_decode(passwordsalt) +  'test')) testpassword aspnet_users u join aspnet_membership m on u.userid = m.userid 

i've tried number of variations on theme, no avail. need in pure t-sql; involving console app or double work.

so if can supply precisely syntax should duplicate password asp.net membership stuff, appreciate it.

if running 2005 or higher, can create clr (.net) udf:

[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));   } } 

you need include following namespaces in class:

using microsoft.sqlserver.server; using system.data.sqltypes; 

the class must public.

build .dll run following (per database want call udf) sql statement:

sp_configure 'clr enabled', 1 go reconfigure go  if object_id (n'dbo.encodepassword', n'fs') not null   drop function dbo.encodepassword;     if exists (select name sys.assemblies name='udf') drop assembly udf  create assembly udf 'full_path_to.dll' permission_set=safe     go  create function encodepassword(   @pass nvarchar(4000),   @salt nvarchar(4000) ) returns nvarchar(4000) -- return null if input parameter(s) null returns null on null input external name udf.[namespace.classname].encodepassword go 

obviously, replace 'namespace.classname' namespace (if any) , name of class. , might want mess input parameter , return value sizes.

then call udf t-sql:

select username,password ,dbo.encodepassword('password', passwordsalt) testpassword  aspnet_users u  join aspnet_membership m on u.userid = m.userid 

works me :)