Using Signed Assemblies for SQLCLR: Doing the Safety Dance.

You know that song. Yes, that song. The beeping. The arm flailing. The Safety Dance. I so wanted it stay in the 80s – along side the uncounted Wild Turkey inflicted hangovers. It just won’t. Every now and then that damn beeping rhythm creeps into my conscious thought when I least need it to. Like today. I’ve been trying to figure out how to use the last-minute-added ability to catalog signed assemblies that need External Access or Unsafe permission without having to set the database trustworthy bit. Getting frustrated with the interesting example in Books Online, the beeping started.

So here’s what you really need to know:

1. The first you need is a certificate that can establish a chain of trust to some trusted root certificate authority on the target machine. If you’ve already got one thanks to having Certificate Server on your network or you’ve purchased one, great. If not, you can make one for yourself as we’ll do there.
2. You need to understand the *interesting* inter-play of certificates, logins and signing assemblies. It is not hard once you understand that you can use a single certificate to do all of that.
3. You will have to comfortable using the Command Shell and a couple of tools in the .NET Software Development Kit (SDK), namely SignTool and MakeCert.

In this case, the what of what I’m trying to deploy a simple enough method to be used as a stored procedure. The code gets has two parameters: the first is the name of parameter-less stored procedure to be executed; the second is the path for a file where the results of the called procedure will be written to in a comma-delimited format. The procedure returns the number of rows successfully written. Nothing hard, here is the code for that:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
public partial class CSVUtilities {
  public static SqlInt32 WriteCSV(SqlString ProcToExec, SqlString OutputPath) {
    int rows = 0;
    bool writeComma = false;
    string outstr;
    DataTable dt = new DataTable();

    using (SqlConnection conn =
               new SqlConnection("Context Connection = true")) {
      using (SqlCommand cmd = new SqlCommand(ProcToExec.Value, conn)) {
        using (SqlDataAdapter da = new SqlDataAdapter(cmd)) {
          conn.Open();
          da.Fill(dt);
        }
      }
    }
    using (StreamWriter output = File.CreateText(OutputPath.Value)) {
      // Write the column headers
      foreach (DataColumn dc in dt.Columns) {
        if (writeComma)
          output.Write(',');
        output.Write('"');
        output.Write(dc.ColumnName);
        output.Write('"');
        writeComma = true;
      }
      output.WriteLine();
      // Write the rows
      writeComma = false;
      foreach (DataRow dr in dt.Rows) {
        foreach (object field in dr.ItemArray) {
          outstr = field.ToString();
          if (outstr.Contains(@",") || outstr.Contains("\""))
            outstr = '"' + outstr.Replace("\"", "\"\"") + '"';
          if (writeComma)
            output.Write(',');
          output.Write(outstr);
          writeComma = true;
        }
        output.WriteLine();
        rows++;
        writeComma = false;
      }
      output.Flush();
    }
    return rows;
  }
};

The first thing we need a certificate issued by a Trusted Root Certificate Authority that we will sign our Assembly with. Remember that certificates represent the serialization of an asymmetric encryption key pair. Both the public and the private keys can be written to files as hexadecimal-encoded bytes. However, I simply do not have one of those, so we actually need two certificates here: one that we load into the local machine as a Trusted Root Certificate Authority and then the first certificate we wanted. Generating these certificates is easy you have the MakeCert.exe tool that ships with the .NET SDK. You should find it on the path C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\. This tool allows us to issue “self-signed” certificates. To generate the certificate we want to use as our Trusted Root, make sure that the path to MakeCert.exe is in your PATH environment variable then start a command shell. The command we will issue is:

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

The arguments for that command mean:

• -sv: this is the name of a file where will write the private key for this certificate
• -cy: indicating that we intend to use this certificate as a root authority
• -r: the name of the file that the public key will be written too
• -n: the description we want used for the certificate

Note that you’ll have to provide a password for the private key. Once we have that key, we need to get into the local computers collection of certificates for Trusted Root Certificate Authorities. Start that process by running the public key file. Yes, you read that correctly: run it – type the signroot.cer and press enter. This will open a dialog called “Certificate” and on that, you should see a button labeled “Install Certificate.” Click that and the Certificate Import Wizard. Click next. On the next panel, select “place all certificates in the following store” then “browse” and select “Trusted Root Certificate Authority.” Once you have that selected, click next then click finish. We will use this certificate as our “issuer” certificate for the next. Now we can use MakeCert again to generate the certificate we will use to sign our Assembly. Here is the command for that, followed by its parameters:

makecert –m 360 –n “CN=Demo Signing Certificate” –iv signroot.pvk –ic signroot.cer –cy end –pe –sv signcert.pvk signcert.cer

• -m: this indicates a number of months from today before until this certificate expires.
• -iv: this is the private key file for the issuer certificate
• -ic: this is the public key for the issuer certificate
• -cy: this indicates that this is an “end-of-chain” certificate
• -pe: causes the private key for this certificate to marked as exportable
• -sv: the name of the file for this certificate’s private key

Our next step is compile the assembly. You may have noticed that my code did not have any SQL-Server specific attributes indicating how we want the method deployed as a procedure. That’s simply because I’m going to manually deploy this assembly and those steps follow. There’s no reason I could not have used Visual Studio to deploy a signed assembly, but here the manual deployment allows us to see the inner workings to the process. The command for compilation is simple enough:

csc.exe /t:library /warnaserror+ /out:csvlibrary.dll CsvUtility1.cs

Where the /t option tells the compiler that we are generating a library instead of an executable. The /warnaserror+ causes the compiler to treat any warning as an error, forcing me to fix those before moving on. The /out indicates that I want the assembly compiled into a file named csvlibrary.dll. Once we have the assembly built, we are ready to sign it with the new certificate. The command line for that is:

signtool signwizard csvlibrary.dll

This command brings up a new version of the SignCode utility you already may be familiar with. Microsoft has upgraded SignCode to SignTool for .NET 2.0. Let us walk through the signing wizard:

1. On the title screen, click next.
2. The file-to-be-sign information should already be filled in, so click next
3. Select Custom and click next
4. Select “select from file” and find the signcode.cer file. Be sure not to select the signroot.cer file. Once you have that selected, click next
5. Pick the “private key from disk option,” browse and select the signcert.pvk file. Again, make sure not select the signroot.pvk file
6. Click next five more times to get to the “Completing the Digital Signing Wizard” screen, then click “finish.” You’ll have to enter the password one more time.

The assembly should now be signed. We can verify that with one last command line option:

signtool verify csvlibrary.dll

We are about half-done with the process: we have a signed assembly and certificate suitable for use with SQL Server 2005. We can dance, sure, but can we sing? Let us try. Fire up management studio and start a new query to your test database. I am using esql_01sqlclr for this example. Make sure you have disabled trustworthy access for this database (e.g., issue alter database esql_01sqlclr set trustworthy off  if needed). We need to issue a few start-up commands:

use master
go
create certificate CSVAsmCert from file = 'c:\esql\01_sqlclr\signcert.cer'
create login CSVAssembler from certificate CSVAsmCert
revoke connect sql from csvassembler
grant external access assembly to CSVAssembler
grant unsafe assembly to CSVAssembler
go

This probably seems like an odd series of commands, so let us break them down. The first two simply change our command context to the master database. Next, we create a SQL Server Certificate object from the certificate we used to sign the assembly with, then we create a login on from that certificate. Yes, you can create a login from a certificate in SQL Server 2005. In this case that is useful because cataloging an external access or unsafe assembly in a non-trustworthy database requires that the assembly be signed and that a security principal (typically a login) based on the same certificate also exist. This provides a two-factor security mechanism for authenticating and authorizing assemblies. Ideally, this makes it harder to someone to inject a potentially harmful assembly into SQL Server since they would also have to have the correct permissions to create a login based on the same certificate used to sign the assembly. Of course, it also makes for extra steps for us. Note that we really do not need or want that login to be used for anything other than providing context for our cataloging operation. This is why I issue the revoke connect on the newly created login. However, that login does need to be able to catalog assemblies, so I do explicitly grant it those permissions. So now we can use this principal to catalog objects into any database we would like to going forward.

The next step takes us back to the database where we want to catalog the assembly and create a master key for that database to use. We need that key to correctly create and store certificates within that database. The next commands to issue are:

use esql_01sqlclr
go
create master key encryption by password = 'p4ssw0rd!'

Yeah, I know, it is a lame password. You’ll do better. Now we need to create another SQL Server Certificate. This one maps the same certificate we signed the assembly with into the database. The command is:

create certificate SignCert from file = 'c:\esql\01_sqlclr\signcert.cer' with private key (file = 'c:\esql\01_sqlclr\signcert.pvk',decryptionpassword='p4ssw0rd!',encryption by password='p4ssw0rd!')

All that’s really different here compared to what we did in the master database – aside from the name – is that we specified where the private key comes, the password required to open that file and the password we want SQL Server 2005 to use to store the private key as well. We do that so we can reuse this certificate again to catalog additional assemblies without having to recreate this certificate.

We are just about done. What remains is cataloging the assembly and creating the desired stored procedure from it. That is easy enough:

create assembly CSVUtil from 'c:\esql\01_sqlclr\csvlibrary.dll' with permission_set = external_access;
go
create procedure dbo.writeCSV
(@procToExecName nvarchar(4000),@outputPath nvarchar(4000))
as
external name CSVUtil.CSVUtilities.WriteCSV;
go

And that’s all there is to it. Yes, it is quite a procedure, but that is by design. Remember that the idea here is make it as hard as possible to inject hostile assemblies into database. Microsoft does that requiring a security principal – in this case a login – that’s bound to the same trusted certificate that the assembly was signed with. You have to be able make changes in both the master and target databases as well. Then you have to actually catalog the assembly, map a T-SQL object to it execute that object. On one hand, that makes it a pretty hard barrier for many attackers to work around. On the other hand, it complicates the loading of desired assembly. But as long as you remember that need a trusted certificate for both the login and for signing the object, that you can use MakeCert to create the needed certificate(s) if you do not already have them and that you now need to use SignTool instead of SignCode, you can dance this Safety Dance if you want to.

Then surprise them with a victory cry!

Feel free to the use the “contact me” link on this site if you’d like the code and scripts talked about herein.