Deploy CLR UDF in SQL Server 2017: procedure

Question

I am trying to deploy a CLR assembly in a SQL Server 2017 DB, following the procedure described at https://sqlquantumleap.com/2017/08/09/sqlclr-vs-sql-server-2017-part-2-clr-strict-security-solution-1/. Basically, for testing purposes I'm just including an assembly with a couple of Regex-based functions:

public class TextUdf
{
    [SqlFunction(Name = "RegexIsMatch", IsDeterministic = true, IsPrecise = true)]
    public static SqlBoolean RegexIsMatch(SqlString text, SqlString pattern,
        SqlInt32 options)
    {
        if (text.IsNull) return SqlBoolean.Null;
        if (pattern.IsNull) pattern = "";

        return Regex.IsMatch((string)text,
            (string)pattern,
            options.IsNull? RegexOptions.None : (RegexOptions)options.Value,
            new TimeSpan(0, 0, 10))
            ? SqlBoolean.True
            : SqlBoolean.False;
    }

    [SqlFunction(Name = "RegexReplace", IsDeterministic = true, IsPrecise = true)]
    public static SqlString RegexReplace(SqlString text, SqlString pattern,
        SqlString replacement, SqlInt32 options)
    {
        if (text.IsNull || pattern.IsNull) return text;

        return Regex.Replace((string)text, (string)pattern,
            (string)replacement,
            options.IsNull ? RegexOptions.None : (RegexOptions)options.Value);
    }
}

I have created a full repro solution at https://github.com/Myrmex/sqlclr. I can follow the whole procedure described there (readme) up to the point where I have to assign the PFX certificate to the CLR assembly to be deployed. At this point, I get this error:

MSB3325: Cannot import the following key file: pfx. The key file may be password protected. To correct this, try to import the certificate again or manually install the certificate to the Strong Name CSP with the following key container name: ...

Following the error message guidance, I then found that I could solve this by installing the PFX with sn, so that I can enter the password manually when prompted (see Cannot import the keyfile 'blah.pfx' - error 'The keyfile may be password protected').

Once done this, I could compile my CLR assembly with the UDF functions. Now, when I try to install it in a test database (just an empty database created for this purpose), via CREATE ASSEMBLY [SqlServerUdf] FROM 0x...binary stuff..., I get this error:

CREATE or ALTER ASSEMBLY for assembly 'SqlServerUdf' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.

which just defeats the purpose of the long procedure I had to follow in order to let SQL Server accept my CLR without dropping strict security.

Clearly I'm missing something, but I'm not sure about many details of the tricky procedure so it would be a hard guesswork. Could anyone suggest we what's wrong with the procedure, so that we can have a quick and dirty step-by-step reference on how to insert a CLR assembly in SQL Server? This simple task seems to have become very hard with the latest version...

Answer 1

Based on what I see in the GitHub repository, there seems to be some steps that you skipped:

1. You did not set a password on the original SQL2017_KeyAsm project. That's why it's still an .snk file (i.e. SQL2017_KeyAsm.snk) instead of a .pfx file (i.e. SQL2017_KeyAsm.pfx). I am guessing that you unchecked the "Protect my key file with a password" checkbox (I think it is checked by default), because you should not have an .snk at all. Also, in your SQL2017_KeyAsm.sqlproj file, you currently have:

   <AssemblyOriginatorKeyFile>SQL2017_KeyAsm.snk</AssemblyOriginatorKeyFile>
   

   When you should have the following:

   <AssemblyOriginatorKeyFile>SQL2017_KeyAsm.pfx</AssemblyOriginatorKeyFile>
   

   Although, your instructions in the readme are correct, as you have stated there: "enter in a password". Still, this probably explains the password error you got, since there is no password protecting the private key. Either that, or the password error is due to using the wrong pfx file (see below). I guess try fixing the below items first as it might be possible to get away with a password-less SNK, I've just never tried it.

2. At the end of the One-Time Procedures section, in the last two steps just after the paragraph starting with, "Finally, the following steps...", step 2 is using the wrong .pfx file. I am guessing that this is a direct result of not having a .pfx file in the previous step (due to not setting a password), so you grabbed the only .pfx file that was there. This is why you are getting the error when attempting to load the SqlServerUdf assembly: you signed the assembly with the certificate, but that certificate was only used as a mechanism to load the KeyAsm assembly into [master] so that the Asymmetric Key could be extracted from it. After that the certificate is dropped. The Asymmetric Key is the public key of the .snk file that was created when you told Visual Studio that you wanted the SQL2017_KeyAsm project signed. And so that .snk file (or .pfx if you protect it with a password) is what you need to select when you are telling Visual Studio to sign the SqlServerUdf project. Doing this will use the same private key to sign both projects / assemblies.

So, first thing to do is change the private key used to sign the SqlServerUdf assembly. It should not be the certificate .pfx file. It needs to be the same key used to sign the KeyAsm file. In your case, this would be SQL2017_KeyAsm.snk.

Try making that one change and see if everything works. If not, then go back and add a password to the snk file (via Visual Studio). You shouldn't need to re-generate the Certificate or its .pfx file because the public key should be staying the same, and that's all that gets loaded into [master] anyway. But, do let me know if this still doesn't work. In the mean time, I will update that post to be more explicit about the password, and about which pfx file to use when.