So you want to load a CLR assembly into SQL Server.  What’s that you say?  You DONT want to set your database to TRUSTWORTHY?  Well look no further, there is a much more secure alternative to the old TRUSTWORTHY fallback: sign your assemblies with a secure certificate.

While this sounds like a walk in the park, it’s not very straight-forward.  In this post I will walk you through the steps necessary to get up and running with a signed assembly.  First you’ll need some additional tools.  You’ll need the makecert.exe tool, which is part of the Microsoft Windows SDK.  Also needed are the signtool and pvk2pfx tool, both of which are also part of the SDK.  You can download the Windows SDK here: http://go.microsoft.com/fwlink/?linkid=84091.

Once you’ve downloaded and installed the SDK, you’re ready to begin.  First, create the root authority certificate:

makecert -sv SignRoot.pvk -cy authority -r signroot.cer -n "CN=Cert Authority"

Once you’ve created this certificate, use it to create an additional certificate and private key pair. This is what will be loaded into SQL server. It is also what you will use to sign compiled DLL files.

makecert -m 360 -n "CN=Signing Cert" -iv signroot.pvk -ic signroot.cer -cy end -pe -sv signcert.pvk signcert.cer

Now convert this certificate into a PFX file for signing assemblies:

pvk2pfx -pvk signcert.pvk -spc signcert.cer -pfx signcert.pfx

Once you have the PFX file, you can use it to sign assemblies:

signtool sign /f signcert.pfx "YOUR_DLL_HERE"

Load the certificate file created above into SQL Server. Be sure to use the .cer file:

USE [master];
GO

CREATE CERTIFICATE CLRCertificate
FROM FILE = 'signcert.cer'
WITH PRIVATE KEY (
	  FILE = 'signcert.pvk'
	, DECRYPTION BY PASSWORD = 'password'
	, ENCRYPTION BY PASSWORD = 'password'
)

Now that the certificate has been loaded into SQL Server, you need to create a login from that certificate. This login will need to have the necessarey assembly permissions (unsafe, external_access).

CREATE LOGIN AssemblyLoader FROM CERTIFICATE CLRCertificate
REVOKE connect sql FROM AssemblyLoader 
GRANT external access assembly TO AssemblyLoader 
GRANT unsafe assembly TO AssemblyLoader 

There you have it, the assembly is successfully loaded into SQL Server without compromising security by using the TRUSTWORTHY flag. Enjoy.

Overview

In this post I would like to demonstrate how to create a simple yet non-trivial CLR table-valued function (TVF). There are several examples of CLR TVFs on the net but the majority of them show only a trivial example. It is very difficult, in some cases impossible, to apply the principles and code in those examples to your own CLR TVF. The TVF outlined in this post is used to return the IP address for a given host name. At my shop we needed a way to log the IP a user connects from when they perform certain actions in the database. .NET can readily perform this work so why not create a TVF to return the IP addresses for a user? Sometimes this function will get called on a machine with two or more IPs (wireless and physical LAN) so a scalar function will not work here. What makes this TVF non-trivial in my opinion is that it does not just output an already formatted table; you have to build the output.

Without further ado, let’s dive in. The following code is full C# source code for the function. I will first divulge the entire code and then explain the pieces.

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;                       // Bring in classes necessary for working with IPs
using System.Collections;               // Bring in the IEnumberable defintion
using System.Collections.Generic;       // Bring in the List<T> class

public class Class_mfn_dnslookup
{
    [SqlFunction(Name = "mfn_dnslookup", FillRowMethodName = "FillRow", TableDefinition = "IP NVARCHAR(20)")]
    public static IEnumerable mfn_dnslookup(SqlString hostname)
    {
        string c_hostname = hostname.ToString();
        IPHostEntry host;
        host = Dns.GetHostEntry(c_hostname);

        List<object[]> resultItems = new List<object[]>();

        foreach (IPAddress ip in host.AddressList)
        {
            if (!IPAddress.IsLoopback(ip) && ip.ToString().IndexOf(".") != -1)
            {
                object[] item = new object[1];
                item.SetValue(ip.ToString(), 0);

                resultItems.Add(item);
            }
        }
        return resultItems;
    }

    public static void FillRow(Object obj, out SqlString IP)
    {
        object[] item = (object[])obj;
        IP = (SqlString)item[0].ToString();
    }
};

Code Guts

In line 10 we define the FillRowMethodName which will dictate how the return table is built. We also define the TableDefinition for the output table. Note that Line 11 uses the output type IEnumerable. This type interacts closely with the FillRow method specified earlier. This also allows for the table to be returned as rows are filled, instead of waiting for all rows to be populated and then returned. This is particularly handy when returning large data sets that you want to begin viewing right away. To build a result set, I assign the IP address to an index within an object array of static length (lines 23 and 24). This array only has the one slot, which is fine since each adapter has only one IP address. I then add this array to the resultItems list (line 26). Line 29 returns the resultItems list. But to where? Or what? This is where the IEnumerable class comes back into play. For CLR TVFs, the data being returned (resultItems list in this case) is operated on by the FillRow method, row by row.

So, the resultItems list, when returned, is passed through the FillRow method. Lines 34 and 35 show how values are set for one output row. The IP output in these lines corresponds to the [IP] column in the table definition. This happens for each output row in the resultItems list that is passed into the FillRow method. The results are sent back to the calling client as they are returned.

Conclusion

While there are several postings about CLR TVFs, I felt none of them explained their code well enough, namely the FillRow method. Jonathan Kehayias has a decent post at SQLCLR.net. I took the concepts from his post and applied them to my situation. Hopefully my explanation will help you better understand how to create a meaningful CLR TVF.