CE's Blog

The following sample code shows how to return a resultset from a CLR stored procedure.
The main thing to note is that you have to pass an empty SqlDataRecord to the SendResultsStart() method in order to provide feedback on the columns sent back to the caller.

[SqlProcedure]
public static SqlInt32 MyStoredProcedure()
{
    SqlMetaData[] meta = new SqlMetaData[] { new SqlMetaData( "MyColumn", SqlDbType.Int ) };
    Int32[] data = new Int32[] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
    SqlDataRecord[] records = data.Select( x => { SqlDataRecord r = new SqlDataRecord( meta ); r.SetInt32( 0, x ); return r; } ).ToArray();
 
    SqlContext.Pipe.SendResultsStart( new SqlDataRecord( meta ) );
    foreach ( SqlDataRecord r in records )
        SqlContext.Pipe.SendResultsRow( r );
    SqlContext.Pipe.SendResultsEnd();
 
    return records.Length;
}

In case you would like to return data retrieved from the database using a SqlDataReader object, you can directly pass the reader to the SqlContext.Pipe.Send() method:

[SqlProcedure]
public static void MyStoredProcedure()
{
    using ( SqlConnection con = new SqlConnection( "context connection=true" ) )
    {
        con.Open();
        using ( SqlCommand cmd = con.CreateCommand() )
        {
            cmd.CommandText = "SELECT SomeColumn FROM SomeTable WHERE SomeColumn IS NOT NULL";
            using ( SqlDataReader r = cmd.ExecuteReader() )
                SqlContext.Pipe.Send( r );
        }
    }
}

.NET programmability support has been one of the big novelties of SQL Server 2005. Sometimes I had the impression that CLR support was also one of the most misunderstood features of the new platform. Some people considered moving their existing pre-2005 T-SQL code to .NET in an attempt to gain more efficiency by using a ‘compiled’ programming language.

This was a bit of a common misbelief, since stored T-SQL procedures and functions are also optimized/compiled prior to execution. Performance gains by using .NET code instead of T-SQL are usually limited to highly iterative/procedural code, and not for optimizing anything which can be easily done within native SQL.

Let us have a look at a simple user defined function written in C#. This function takes a clear text string and a 2 character salt parameter and calculates a Unix crypt hash based on both.

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction( IsDeterministic=true )]
    public static SqlString Crypt( SqlString sPlainText, SqlChars cSalt )
    {
        if ( sPlainText.IsNull || cSalt.IsNull || cSalt.Length < 2 )
            return SqlString.Null;
        string sSalt = new string( cSalt.Value );
        sSalt = sSalt.Substring( 0, 2 );
        return new SqlString( UnixCrypt.Crypt( sSalt, sPlainText.Value ) );
    }
}

We just declare a class with a static function qualified as a SqlFunction that accepts and returns classes/structs of the System.Data.SqlTypes namespace. The UnixCrypt.Crypt method contains the actual code which I am excluding here. If you are interested in building this example, you can find a working implementation of the crypt class in C# at The Code Project.

After compiling the above code into a dll, you may load it into SQL Server and run it with the following commands:

-- CLR support is disabled by default
EXEC sp_configure 'clr enabled' , '1'
GO
reconfigure;
GO
-- load dll and create assembly
CREATE ASSEMBLY asmYourAssembly FROM 'D:\Your\Path\to\CryptUDF.dll' WITH PERMISSION_SET = SAFE
GO
-- add a function that references the CLR UDF
CREATE FUNCTION dbo.Crypt( @plain AS NVARCHAR(200), @salt AS NCHAR(2) )
    RETURNS NVARCHAR(200) AS EXTERNAL NAME asmYourAssembly.UserDefinedFunctions.Crypt
GO
SELECT dbo.Crypt( 'password', 'xy' );

Note PERMISSION_SET = SAFE, which reflects the external dependencies on framework libraries. Since we are doing all our calculations within the library, there is nothing additional we need to reference, which permits us to be running within the safest permission set. You can learn more about the CLR integration permission sets on MSDN.

When doing authentication, you could use the UDF as follows:

SELECT @UserID=[UserID] FROM dbo.tblAccounts 
    WHERE [UserName]=@Username COLLATE Latin1_General_CI_AS
    AND dbo.Crypt( @Password, [Password] )=[Password] COLLATE Latin1_General_CS_AS

Note that we are doing a case insensitive compare on the UserName column and a case sensitive compare on the crypt hash of the crypt hash column.

Why does it make sense to do authentication like this inside the database instead of calculating the password hash outside of the database and then just pass it to a WHERE Username=@Username AND Password=@Password query?

The reason lies in the nature of the crypt function: the hash it creates is based on a two character prefix or salt, which has to be used for hashing the candidate password prior to comparing it with the original password hash. Since the above approach can be completely encapsulated within a stored procedure, no hashes or salts need to be returned to the calling application for verifying the correctness of the password.

Where is this used today? While Unix and Linux user authentication is the classic usage of the crypt function, also some internet services are relying on it. E.g. the PayPal IPN feature for selling subscription accounts is transmitting passwords as crypt hashes only.