Every blog post I’ve read about using F# with SQL CLR involves turning off SQL CLR security for the entire database like so:

alter database mydatabase set trustworthy on

This is because there is not currently a “safe” FSharp.Core, and so even when building with –standalone you must load the assemblies in to SQL as “unsafe”.

In our business we deal with real live bank data, and I would never be able to sell this to our ops team. Fortunately, there is a way to allow unsafe assemblies on a per-assembly basis as I found out here. It’s a little more complex, but not awful.

I’m going to gloss over some of the wiring things up code here, as it can be found in many other places.

1) As with any SQL CLR, you need to turn it on before you can use it

SP_CONFIGURE ‘clr enabled’, 1
GO
RECONFIGURE
GO

2) You must sign your assemblies to-be-loaded with an asymmetric key pair.
In F# you do this by adding an attribute to your assembly. I like to put these kinds of things in my AssemblyInfo.fs. You can find out more information on how to create a public-private key pair here. I also recommend compiling with the F# –standalone flag, so as to avoid having to pull in FSharp.Core as well.

[]
do ()

3) Make sure you’re in the Master database.

USE [Master]
GO

4) Pull in that asymmetric key into SQL Server and give it a name.

CREATE ASYMMETRIC KEY FSHARP_CLR_Key
FROM EXECUTABLE FILE = ‘C:\MyProj\bin\Release\FSHARPCLR.dll’
GO

5) Create a login linked to this asymmetric key. You also need to give this user external assembly access.

CREATE LOGIN FSHARP_CLR_Login
FROM ASYMMETRIC KEY FSHARP_CLR_Key

GRANT EXTERNAL ACCESS ASSEMBLY TO FSHARP_CLR_Login

GO

6) Move to the database where you’ll be deploying your assembly.

USE [Resources]
GO

7) Make a database user to run under.

CREATE USER FSHARP_CLR_Login
FOR LOGIN FSHARP_CLR_Login
GO

8) Pull your assembly into SQL!
It’s still unsafe, but in this case it’s special dispensation for a single assembly with controllable permissions instead of whole-hog access.

CREATE ASSEMBLY FSHARP_CLR
FROM ‘C:\MyProj\bin\Release\FSHARPCLR.dll’
WITH PERMISSION_SET=UNSAFE
GO

9) Wire up the API with CREATE FUNCTION or CREATE PROCEDURE calls as you would normally.

CREATE FUNCTION [dbo].[StringDistance]
(@name1 nvarchar,
@name2 nvarchar)
RETURNS float
AS
EXTERNAL NAME [FSHARP_CLR].[Namespace.ClassName]
.[CalcStringDistance]
GO

10) And now we can call it easily in SQL!

SELECT dbo.StringDistance(‘Richard’, ‘Rick’)

That’s it!
Please let me know if there are any future incompatibilities.