Using SQLCLR safely

The SQLCLR feature lets you run managed .NET code inside SQL Server. It is powerful, but you should enable and use it carefully to avoid security and stability problems.

First, enable CLR integration must be enabled on the server. You can check the status and enable it as follows:

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

Prefer SAFE (see below) assemblies whenever possible. Mark your C# project appropriately:

[assembly: System.Security.Permissions.SecurityPermission(
  SecurityAction.RequestMinimum, SkipVerification = true)]

When you create the assembly in SQL Server, use the aforementioned SAFE option:

CREATE ASSEMBLY MyClrAssembly
FROM 'C:\Assemblies\MyClrAssembly.dll'
WITH PERMISSION_SET = SAFE;

Then create the function or procedure wrapper:

CREATE FUNCTION dbo.MyClrFunction(@value int)
RETURNS int
AS EXTERNAL NAME MyClrAssembly.[MyNamespace.MyType].MyClrMethod;

General guidelines:

• Keep SQLCLR code small and focused on tasks T-SQL is not good at (parsing, complex math, etc.).
• Avoid EXTERNAL_ACCESS and UNSAFE unless you have a very specific reason and understand the risk.
• Sign assemblies and avoid setting databases as TRUSTWORTHY just to get CLR working.

Used sparingly and with SAFE permission, SQLCLR can solve a few niche problems without opening big security holes.