Microsoft introduced the ability to use .NET CLR stored procedures and functions in SQL Server some time ago, starting with SQL Server 2005. Now more than 8 years later I think many developers are like me: I acknowledge the power of CLR routines, but try to avoid using CLR.

Part of the reason for this avoidance has to do with technical considerations. But truthfully for me, part of the reason also has to do with the increased complexity that CLR introduces into development, deployment, and maintenance of the database.

This article will demonstrate an approach to deploying and managing CLR routines that may be more comfortable for T-SQL developers and DBA's, and one that does not involve use of Visual Studio. This approach also encapsulates everything needed to deploy the CLR assembly within the database, meaning that a database backup will store all needed dependencies.

The basic goal of this exercise is to create a stored procedure that when executed will compile C# code, sign the .DLL, register the assembly in SQL, and create the wrapper SQL objects, all within this stored procedure. In this way, deployment of the CLR assembly is as easy as running a stored procedure. Everything is taken care of, and is all in one place: no independent .DLL 's, Visual Studio projects, or C# source to keep track of.

Additionally, this exercise attempts to follow best practices for deployment, such as signing the assembly and properly securing it in SQL. These are things that often get omitted when in a hurry to set up a CLR assembly in SQL.

CREATE PROCEDURE dbo.spExample_RegisterAssembly_PDFCLR
AS
BEGIN
  DECLARE @FilePath varchar(1024)
  SET @FilePath = 'c:\ServerEnvironment\'
  CREATE TABLE #References (AssemblyName sysname, FQFileName varchar(1024))
  INSERT INTO #References (AssemblyName, FQFileName) VALUES ('System.Drawing', 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll')
  INSERT INTO #References (AssemblyName, FQFileName) VALUES ('itextsharp', @FilePath + 'itextsharp.dll')
  DECLARE @DropWrapperSQL varchar(MAX)
  SET @DropWrapperSQL = '
    IF OBJECT_ID(''dbo.udfRenderPDF'') IS NOT NULL BEGIN
      DROP FUNCTION dbo.udfRenderPDF;
    END
  '
  DECLARE @CreateWrapperSQL varchar(MAX)
  SET @CreateWrapperSQL = '
    CREATE FUNCTION [dbo].[udfRenderPDF](
    @TemplatePDF varbinary(MAX),
    @FieldsXML xml
    )
    RETURNS [varbinary](max) WITH EXECUTE AS CALLER
    AS
    EXTERNAL NAME [PDFCLR].[Functions].[RenderPDF]
  '
  --C# Source Code.
  --Paste CLR source in below.  Replace all occurrences a single quote with two single quotes.
  DECLARE @SourceCode nvarchar(MAX)
  SET @SourceCode = '
  //------start of CLR Source------
  using System;
  using System.Data;
  using System.Data.SqlClient;
  using System.Data.SqlTypes;
  using Microsoft.SqlServer.Server;
  ....Rest of C# source code goes here
  //------end of CLR Source------  '

  EXEC dbo.spsysBuildCLRAssembly
    @AssemblyName = 'PDFCLR',
    @FileName = 'PDFCLR_SQLCLR.cs',
    @FilePath = @FilePath,
    @DropWrapperSQL = @DropWrapperSQL,
    @CreateWrapperSQL = @CreateWrapperSQL,
    @SourceCode = @SourceCode
END

Here is a step-by-step list of the work this stored procedure needs to do:

1. Retrieve C# source from SQL
2. Write C# source to a temporary .cs file
3. Enable CLR support in the database (if necessary)
4. Temporarily enable xp_cmdshell (just for the duration of execution of this procedure)
5. Write and execute a batch file that does the following:
   • Generate signature with the command line "sn" (Strong Name) tool
   • Build C# source into a signed DLL using the command line "csc" (C Sharp Compiler) compiler
6. Disable xp_cmdshell (for security reasons)
7. Drop the SQL wrapper function that wraps the CLR method (if it exists)
8. Drop the CLR assembly (if it exists)
9. Create key to secure the assembly:
   • Create an asymmetric key (dropping if it exists)
   • Create a SQL login from the key (dropping if it exists)
   • Grant rights to the login
10. Create the assembly in SQL
11. Create the SQL wrapper function that wraps the CLR method

As easy as 1, 2, 3...11. And that is part of what I mean about the complexity of deploying and maintaining CLR assemblies in SQL: there are lots of steps to learn how to do (and then remember to do them). These steps need to be done every time you deploy this database to a new server. Being able to do all of these things by executing a single stored procedure simplifies things greatly.

(Note that the sequence of some of these steps has been altered slightly in the final version of the code that is attached.)

Step 1: Retrieve C# Source from SQL

We are trying to avoid storing the C# source in a file because we want everything that is needed to create the assembly to be encapsulated in the database. The source could be stored in a table, or, as I have done here, the source code can be stored as a string literal inside the stored procedure.

What I have done is copy-and-pasted the C# source from Visual Studio, then used search-and-replace to replace single quote characters with two single quote characters, and then assigned this string to a variable which will later get written out to a temporary .cs file.

Example:

DECLARE @CLRSource nvarchar(MAX)
SET @CLRSource = '
//------start of CLR Source------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
....
//------end of CLR Source------
'

Step 2: Write C# source to a temporary .cs file

Phil Factor writes some usefull SQL code. One of his articles gives us a utility procedure we can use to easily write a string to a file. I use this procedure to write the C# source to a .cs file.

Example:

EXEC dbo.sputilWriteStringToFile
  @FileData = @CLRSource,
  @FilePath = @FilePath,
  @FileName = @FileName

Step 3: Enable CLR support

This procedure will deploy a CLR assembly. Obviously we need CLR support enabled in the database.

Example:

IF NOT EXISTS(SELECT * FROM sys.configurations WHERE name = 'clr enabled') BEGIN
  SET @SQL ='
  EXEC master.dbo.sp_configure ''show advanced options'', 1
  RECONFIGURE
  EXEC master.dbo.sp_configure ''clr'', 1
  RECONFIGURE'
  EXEC(@SQL)
END

Step 4: Temporarily enable xp_cmdshell

I fully understand and agree that xp_cmdshell can introduce a number of security problems, and is best avoided in production databases. My approach here is that this stored procedure will enable xp_cmdshell temporarily. It will be enabled just long enough to call a batch file that the procedure will dynamically create.

In my opinion, this use of xp_cmdshell is safe and appropriate: it will only be called at deploy-time by an administrator, will be used to execute carefully scripted statements, and will be immediately disabled.

Example:

SET @SQL = '
  EXEC master.dbo.sp_configure ''show advanced options'', 1
  RECONFIGURE
  EXEC master.dbo.sp_configure ''xp_cmdshell'', 1
  RECONFIGURE'
EXEC(@SQL)

Step 5: Create a batch file that will be executed

We need to execute the strong name command line application (sn.exe), and also the command line C# compiler (csc.exe)

PREREQUISITE:

This CLR assembly requires iTextSharp, an open source library for creating PDF 's (from http://sourceforge.net/projects/itextsharp/ ). Download, and copy the itextsharp.dll file to c:\ServerEnvironment (or a folder of your choosing, updating the script as needed).

PREREQUISITE:

The sn.exe and csc.exe utilities are part of the "Windows SDK for Windows Server 2008 and .NET Framework 3.5 ", available as a free download at http://www.microsoft.com/en-us/download/details.aspx?id=11310

NOTES:

SQL Server 2005 and 2008 CLR support is limited to .NET Framework 3.5. SQL Server 2012 introduces support for .NET Framework 4.0, but can run .NET Framework 3.5. This procedure uses .NET Framework 3.5--which is our only option on SQL 2005, 2008, and 2008 R2.

Figuring out all the command line parameters necessary took a bit of research, but now that is done the procedure can automatically output the needed parameters to the batch file.

Example:

DECLARE @Command varchar(2048)
SET @Command =
  "C:\Program Files\Microsoft SDKs\Windows\v6.1\Bin\sn" -k ' +
  @FilePath + '\' + 'PDFCLR_keyPair.snk' + @CRLF +
  '"C:\Windows\Microsoft.NET\Framework\v3.5\csc" /t:library' +
  ' /reference:c:\ServerEnvironment\itextsharp.dll' +
  ' /out:' + @FilePath + '\' + REPLACE(@FileName, '.cs', '.dll') +
  ' /keyfile:' + @FilePath + '\' + 'PDFCLR_keyPair.snk' +
  ' ' + @FilePath + '\' + @FileName
EXEC dbo.sputilWriteStringToFile
  @FileData = @Command,
  @FilePath = @FilePath,
  @FileName = 'tmp.bat'

Step 6: Disable xp_cmdshell

We don 't want to leave xp_cmdshell enabled, and the procedure is done with it.

Example:

SET @SQL = '
  EXEC master.dbo.sp_configure ''show advanced options'', 1
  RECONFIGURE
  EXEC master.dbo.sp_configure ''xp_cmdshell'', 0
  RECONFIGURE'
EXEC(@SQL)

Step 7: Drop the wrapper SQL function

CLR assemblies expose methods, but SQL requires a SQL function that is tied to the method in the assembly. Since we want to drop the assembly if it exists, we must first drop the wrapper function.

Example:

IF OBJECT_ID('dbo.udfRenderPDF') IS NOT NULL BEGIN
  IF @Debug = 1 PRINT '***Dropping existing function'
  SET @SQL = 'DROP FUNCTION dbo.udfRenderPDF'
  EXEC(@SQL)
END

Step 8: Drop the existing CLR assembly, if it exists

We want to replace the existing assembly (if any), so we have to drop it if it exists.

Example:

IF ASSEMBLYPROPERTY ('PDFCLR' , 'MvID') IS NOT NULL BEGIN
  IF @Debug = 1 PRINT '***Dropping existing CLR assembly'
  SET @SQL = 'DROP ASSEMBLY PDFCLR'
  EXEC(@SQL)
END

Step 9: Create key to secure the assembly

This is one of the harder parts to understand, but a detailed explanation is beyond the scope of this article. I 'll try to provide a brief overview:

CLR code can do anything, including destructive or malicious things. CLR code that does potentially dangerous things (such as deleting files from the file system) gets flagged as "unsafe ". SQL prevents "unsafe " CLR assemblies from being loaded in an effort to protect the server environment from destructive or malicious things. SQL will allow "unsafe " CLR assemblies if one of two things is true: a) the TRUSTWORTHY database property is enabled, or b) the assembly is signed and tied to a key and login in SQL.

TRUSTWORTHY is a bad idea, because basically it says that ANY "unsafe " assembly can be loaded. We don 't want to open the door to load any and every "unsafe " assembly. If we did, a user could register dangerous or malicious .DLLs without the DBA's knowledge. Also, someone could potentially change the .DLL in the file system without the DBA's knowledge--and SQL would then continue to allow users to call methods in the now-rogue assembly. (Think of TRUSTWORTHY as being SQL deeming the entire physical server and everything on it as being safe or "trustworthy".)

Signing the assembly is a much better idea. It is slightly complicated to do, but the concept isn 't too hard. This involves signing the assembly with a cryptographic signature, creating an asynchronous key in SQL based on this signature, creating a SQL login associated with the key, and granting appropriate rights to this login. This in effect gives us the ability to say that the specified user is allowed to load this specific "unsafe " assembly.

Putting this another way, signing the assembly guarantees the DBA that only assemblies approved by the DBA will be used by SQL. I will not dig into what all is going on in the mechanics of signing the code, but will instead just show you how to do it.

Example:

SET @SQL = '
  USE MASTER;
  IF EXISTS(SELECT * FROM sys.syslogins WHERE name = ''PDFCLR_SQLCLRLogin'')
    DROP LOGIN PDFCLR_SQLCLRLogin
  IF EXISTS(SELECT * FROM sys.asymmetric_keys WHERE name =''PDFCLR_SQLCLRKey '')
    DROP ASYMMETRIC KEY PDFCLR_SQLCLRKey
  CREATE ASYMMETRIC KEY PDFCLR_SQLCLRKey FROM EXECUTABLE FILE = ''' +
  @FilePath + '\' + REPLACE(@FileName, '.cs', '.dll') + '''
  CREATE LOGIN PDFCLR_SQLCLRLogin FROM ASYMMETRIC KEY PDFCLR_SQLCLRKey
  GRANT EXTERNAL ACCESS ASSEMBLY TO PDFCLR_SQLCLRLogin'
EXEC(@SQL)

Step 10: Create the assembly in SQL

Now we can create the assembly in SQL. This terminology can be a little confusing, as the .NET assembly is already created (i.e. the C# code has already been compiled and the .DLL already exists.) Really what we are doing here is "registering " the assembly for use by SQL, though the SQL command is "CREATE ASSEMBLY ".

Example:

SET @SQL = '
  CREATE ASSEMBLY PDFCLR
    FROM ''' + @FilePath + '\' + REPLACE(@FileName, '.cs', '.dll') + '''
    WITH PERMISSION_SET = UNSAFE'
EXEC(@SQL)

NOTE: This particular assembly (that renders PDF documents) requires "unsafe " operations. Some assemblies may not require "unsafe " operations, and can thus have a different setting for PERMISSION_SET.

Step 11: Create the SQL wrapper function

Finally we can create the SQL wrapper function associated with the method in the CLR assembly. Parameters and types in the SQL wrapper must exactly match those in the C# code.

Example:

SET @SQL = '
  CREATE FUNCTION [dbo].[udfRenderPDF](
  @TemplatePDF varbinary(MAX),
  @FieldsXML xml
  )
  RETURNS [varbinary](max) WITH EXECUTE AS CALLER
  AS
  EXTERNAL NAME [PDFCLR].[Functions].[RenderPDF]'
EXEC(@SQL)

Trying it out

Finally, we can try out the results of all our hard work by executing the new function we just created, and seeing how it renders a PDF file.

(Little is said here of what this assembly actually does or how to use it. Say tuned for tomorrow's article ""Rendering PDFs Natively in SQL" for details on this particular assembly.)

Example:

DECLARE @FieldsXML xml
SET @FieldsXML = CAST(
'<Fields>
  <Field>
    <TextValue>Hello World</TextValue>
    <XPos>100</XPos>
    <YPos>700</YPos>
    <FontSize>18</FontSize>
  </Field>
  <Field>
    <TextValue>One more line, just for fun.</TextValue>
    <XPos>150</XPos>
    <YPos>650</YPos>
    <FontSize>12</FontSize>
  </Field>
</Fields>'
 AS xml)
DECLARE @PDFTemplate varbinary(MAX)
SET @PDFTemplate = NULL
DECLARE @ResultPDF varbinary(MAX)
SET @ResultPDF = dbo.udfRenderPDF(@PDFTemplate, @FieldsXML)
/*The PDF file now exists in the @ResultPDF variable.  You can do whatever you want with the data.  To write the binary data to a file on the server so that you can open it in Adobe Acrobat Reader you can use this utility procedure (see attached).
*/EXEC [dbo].[sputilWriteBinaryToFile]
  @FileData = @ResultPDF,
  @FilePath = 'C:\Temp',
  @Filename = 'test.pdf'

Summary

There are a lot of steps involved in properly deploying a CLR assembly in SQL. But the good news is that once these steps are encapsulated within a stored procedure, the procedure can be executed any time the CLR source code is updated and whenever you need to deploy the CLR assembly to a different machine.

Both the C# source and the script to build, sign and register it are resident in the SQL database--and as such get backed up and restored along with all other SQL objects. The DBA can see exactly what is going on in the assembly--both in terms of the C# source and the various compiler options--all in one place, by simply looking at the source of this stored procedure. Also, the DBA doesn't even need to open Visual Studio: everything can be done from native T-SQL

Visual Studio is a fine development tool, and is useful when developing the C# code. But in my opinion, a deployment script implemented in a SQL stored procedure is a much nicer way for a T-SQL developer or DBA to deploy and update CLR assemblies in SQL.

Will I use CLR for everything? No, definitely not. But now when I need to use a CLR assembly I can now do so with greater safety and greater ease than I could without the techniques described in this article.

(See attached file for full source code.  You can download and execute BuildAndRegisterCLRAssembly.sql to create all procedures and functions referenced here, as well as to execute the example shown above.)