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.
[<assembly:AssemblyKeyFileAttribute("MyKeyPair.snk")>] 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](64), @name2 [nvarchar](64)) 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')
This code was tested in SQL Server 2008r2. Please let me know if there are any future incompatibilities.