Installing a CLR Stored Procedure on SQL Server

Introduction

This tip details how to install a .NET Common Language Runtime Stored Procedure on SQL Server. It does NOT cover writing the .NET code, just the installation in SQL Server.

There are several good articles on creating CLR stored procedures, so this one will only focus on the installation process.

• CLR Stored Procedure and Creating It Step by Step
• Create CLR Stored Procedure with Visual Studio Express Edition

  Notes:

• For SQL Server <= 2008r2 you must compile for the .NET 2.0 framework.
• A warning on one problem I had: I had set the registry key HKLM:\SOFTWARE\Microsoft\.NETFramework - UseOnlyLatestCLR to true, and it prevented SQL Server from loading the correct framework.

STEP 1: Configure SQL Server

1a: Run this query to make sure the CLR is enabled in SQL Server:

select case [value]
        when 0 then 'You NEED to run the sp_configure.'
        else 'You do NOT need to run the sp_configure.'
        end
from sys.configurations
where name = 'clr enabled';
go

1b: IF you NEED to run sp_configure, continue, ELSE go to STEP 2:.

Run the following SQL. This will enable the .NET Framework (.NET 2.0 in SqlSvr 2008r2). The "reconfigure" commands requires SERVER admin privileges to run.

sp_configure @configname=clr_enabled, @configvalue=1
go
reconfigure
go

STEP 2: Grant the User, and Assembly "external access" Rights.

Login to the database as a user in the system admin role, OTHER THAN the user you are going to use to execute the rest of the process (e.g. use the "sa" system account). This is necessary because a user cannot grant these rights to themselves. Execute the following SQL:

use master;
grant external access assembly to [Domain\Login]; -- The domain\login of the original user.
go

-- This allows the CLR stored procedures to have external_access rights.
alter database {database name} set trustworthy on;
go

STEP 3: Get the .NET CLR Assembly

Compile the .NET CLR project in Visual Studio, or get an already compiled DLL file.

Copy the CLR.dll file to a location that can be referenced by the SQL Server machine as a file path.

STEP 4: Import the .NET assembly into SQL Server

Execute the following SQL. This imports the .NET assembly into SQL Server.

declare @dllPath nvarchar(255) = '{The file system path to your CLR DLL (e.g. C:\Temp\CLR.dll)}'
create assembly {choose assembly name} from @dllPath with permission_set = external_access;
go

• Notes:
• The "external_access" permission set is needed if you want to access resources outside of SQL Server, if you don't use "safe" instead.
• You can avoid the problem of having the SQL Server account access the file system by converting the assembly to hex, and passing that instead.
  See the tip Installing a SQL Server CLR Stored Procedure as a Hex String for an example of turning a .dll file into a hex string for SQL Server.
• If you receive an error that the database owner SID is different than in the master, execute the following SQL as a system admin:
  SQL

  alter authorization on database::{database name} to sa;
  

STEP 5: Create the Procedure in SQL Server

Execute the following SQL. This creates stored procedures from the methods in the assembly. Replace the (@msg nvarchar(max), @result nvarchar(max) output) parameters with the parameters and return defined in the .NET function.

create procedure {choose SP name} (@msg nvarchar(max), @result nvarchar(max) output)
as external name {choose Assembly name}._
[{.NET namespace}.{.NET class name}].{.NET function name};
go

STEP 6: Call the Stored Procedure

Executing the following will run the stored procedure (substitute the parameters for what you created in Step 5).

declare @res nvarchar(max);
exec {SP name chosen in Step 5} @msg = 'The msg', @result = @res output;
print @res;