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
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.
3) Make sure you’re in the Master database.
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’
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
6) Move to the database where you’ll be deploying your assembly.
7) Make a database user to run under.
CREATE USER FSHARP_CLR_Login
FOR LOGIN FSHARP_CLR_Login
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
9) Wire up the API with CREATE FUNCTION or CREATE PROCEDURE calls as you would normally.
CREATE FUNCTION [dbo].[StringDistance]
EXTERNAL NAME [FSHARP_CLR].[Namespace.ClassName]
10) And now we can call it easily in SQL!
SELECT dbo.StringDistance(‘Richard’, ‘Rick’)
Please let me know if there are any future incompatibilities.
Leave a Reply