F # with sqlclr in a safe enough way and building scripts

I am skeptical about the approach shown in your first link ( http://richardminerich.com/2015/12/a-safer-way-to-use-f-with-sql-clr/ ) as it doesn't show loading directly FSharp.Core , so it's not clear that the author didn't need to install TRUSTWORTHY ON

to even get this part. What seems very suspicious is that the Asymmetric Key Based Input got the wrong permission in Step 5 :

GRANT EXTERNAL ACCESS ASSEMBLY TO FSHARP_CLR_Login

      

        
        
        
      

    

The grant EXTERNAL ACCESS ASSEMBLY

does not allow the assembly to be installed in UNSAFE

. This requires permission UNSAFE ASSEMBLY

. It could be a copy / paste error while writing the post, but no proof (i.e. From sys.databases

) that it TRUSTWORTHY

is currently displayed OFF

, or that the author's code was not working prior to creating this entry and granting it this permission.

So, I just tried it by installing the latest FSharp.Core build - 4.1.2 - and this is what I found:

• Validate which TRUSTWORTHY
  
  is OFF
  
  (i.e. 0
  
  ) via:

  SELECT [name], is_trustworthy_on FROM sys.databases WHERE [database_id] = DB_ID();
  
        
  
          
          
          
        
  
      

• Try loading FSharp.Core like SAFE
  
  to see if it works:

  USE [TestDB];
  
  CREATE ASSEMBLY [FSharp.Core]
  FROM N'C:\path\to\project\packages\FSharp.Core.4.1.2\lib\net45\FSharp.Core.dll'
  WITH PERMISSION_SET = SAFE;
  
        
  
          
          
          
        
  
      

  This gets the following error:

  Msg 6211, Level 16, State 1, Line 32
  CREATE ASSEMBLY failed because the type "Microsoft.FSharp.Collections.FSharpMap`2" in the secure assembly "FSharp.Core" has a static field "empty". Static field attributes in safe assemblies should only be checked in Visual C #, ReadOnly in Visual Basic, or initonly in Visual C ++ and intermediate language.

• Try to load FSharp.Core again , but how UNSAFE
  
  :

  USE [TestDB];
  
  CREATE ASSEMBLY [FSharp.Core]
  FROM N'C:\path\to\project\packages\FSharp.Core.4.1.2\lib\net45\FSharp.Core.dll'
  WITH PERMISSION_SET = UNSAFE;
  
        
  
          
          
          
        
  
      

  It works. But I didn’t set the database in TRUSTWORTHY ON
  
  and I didn’t create a login and grant it permission EXTERNAL ACCESS ASSEMBLY
  
  . Meaning: The violation is likely to occur through a runtime check rather than a load time check. And I have no way to test this part, but I expect an error to occur.

• If UNSAFE
  
  there is an error regarding the Permission Set for this assembly, you can handle it without configuring TRUSTWORTHY ON
  
  , but you will need to create a certificate in master and based on Certficate Login:

  USE [master];
  
  CREATE CERTIFICATE [FSharpCert45]
  FROM EXECUTABLE FILE =
          N'C:\path\to\project\packages\FSharp.Core.4.1.2\lib\net45\FSharp.Core.dll';
  
  CREATE LOGIN [FSharpLogin45] FROM CERTIFICATE [FSharpCert45];
  
  GRANT UNSAFE ASSEMBLY TO [FSharpLogin45];
  
        
  
          
          
          
        
  
      

• If your assembly also needs to be marked as UNSAFE
  
  , then you can generate an asymmetric key from the DLL in master
  
  and then log in using a key based on that asymmetric key and then provide a Key based sign in UNSAFE ASSEMBLY
  
  . (this assumes your assembly is signed - and password protected)

• Of course, all of the above assumes that you can get the DLL to the server, or at least a share that the SQL Server service account has access to, and you mentioned having to expand this across hex bytes.This should be possible if :

  • In Visual Studio, under References in Solution Explorer, go to Properties for FSharp.Core and set Model Aware to True
    
    and Resolution - UNSAFE
    
    . This will force the publishing process to include the DLL in the assembly script.
  • If the DLL is already in your target DB, it probably won't generate a statement CREATE ASSEMBLY
    
    for that assembly, as the publish scripts are incremental changes. If so, go to your project properties and under Project Settings , check the Generate script (.sql file) box (if not already checked). This will cause the build process to always create a _Create.sql script, and it will necessarily have a statement CREATE ASSEMBLY
    
    for FSharp.Core .
  • This statement CREATE ASSEMBLY [FSharp.Core] FROM 0x...
    
    will obviously be used to load the assembly into the target DB (i.e. where your assembly is loaded as well).

  • This operator CREATE ASSEMBLY [FSharp.Core] FROM 0x...
    
    will also be your ticket for creating objects master
    
    like this:

    USE [master];
    
    CREATE ASSEMBLY [FSharp.Core]
    FROM 0x4D....
    WITH PERMISSION_SET = UNSAFE;
    
    CREATE CERTIFICATE [FSharpCert45]
    FROM ASSEMBLY [FSharp.Core];
    
    DROP ASSEMBLY [FSharp.Core];
    
    CREATE LOGIN [FSharpLogin45] FROM CERTIFICATE [FSharpCert45];
    
    GRANT UNSAFE ASSEMBLY TO [FSharpLogin45];
    
          
    
            
            
            
          
    
        

    This worked for me on SQL Server 2012, the only difference being that I used the filepath instead of hex bytes.