F#和sqlclr以合理的安全方式进行脚本编写 [英] F# with sqlclr in a reasonably safe way and scripted assembly

问题描述

存在一些有关如何在SQL Server中将F#与SQLCLR一起使用的博客文章，这些文章很有帮助: http://thelastexpression.blogspot.com/2012/04/f-agents-and-sqlclr.html ， https://rojepp .wordpress.com/2011/08/03/f_on_sqlclr/， F#核心库是否可以通过SQLCLR批准? 对于C#方法: http://www.sqlservercentral.com/Authors/Articles /Solomon_Rutzky/294002/

There exist some blog posts about how to use F# with SQLCLR in SQL Server that are helpful: http://richardminerich.com/2015/12/a-safer-way-to-use-f-with-sql-clr/, http://thelastexpression.blogspot.com/2012/04/f-agents-and-sqlclr.html, https://rojepp.wordpress.com/2011/08/03/f_on_sqlclr/, Can the F# core libs be SQLCLR-approved? and for the C# approach: http://www.sqlservercentral.com/Authors/Articles/Solomon_Rutzky/294002/

我想知道/希望随着时间的流逝那里有一篇博客文章，我还没有找到它，或者在这里找到答案，该文章解决了如何在SQLCLR中使用F#，以便程序集可以可以使用Visual Studio(或其他一些工具)编写成16进制脚本，就像C#部署一样(除非通过SQL Server Management Studio，否则我无权在服务器上安装代码)，并且至少比使用值得信赖"或不安全".我之前已经写过F#和许多T-SQL，并且我在Common Lisp中编写的原型(现在必须驻留在SQL Server中)可以更好地映射到F#(并使我比使用C#更快乐).

I am wondering/hoping that with the passage of time there is a blog post out there, which I haven't been able to find yet or an answer here, which addresses how to use F# with SQLCLR such that the assembly can be scripted into hex using Visual Studio (or some other tool), like is done with C# deployment (I don't have access to install code on the server except through SQL Server Management Studio), and is at least more safe than using 'trustworthy on' or 'unsafe'. I've written F# and lots of T-SQL before and the prototype I wrote (which now must live in SQL Server) in Common Lisp would map better to F# (and make me happier than using C#).

推荐答案

我对第一个链接(> http://richardminerich.com/2015/12/a-safer-way-to-use-f-with-sql -clr/)，因为它不会直接显示 FSharp.Core 库的加载，因此不清楚作者是否不必设置TRUSTWORTHY ON即可最起码要让那部分工作.似乎非常可疑的是，在第5步中，基于不对称密钥的登录名被授予错误的权限:

I'm skeptical of the approach shown in your first link ( http://richardminerich.com/2015/12/a-safer-way-to-use-f-with-sql-clr/ ) as it does not directly show the loading of the FSharp.Core library, hence it is not clear that the author did not have to set TRUSTWORTHY ON in order to at least get that part working. What seems highly suspicious is that in Step 5, the Asymmetric Key-based Login is granted the wrong permission:

GRANT EXTERNAL ACCESS ASSEMBLY TO FSHARP_CLR_Login

授予EXTERNAL ACCESS ASSEMBLY不允许不允许将程序集设置为UNSAFE. 那个需要UNSAFE ASSEMBLY权限.撰写帖子时可能是复制/粘贴错误，但没有显示(例如，来自sys.databases的)TRUSTWORTHY当前为OFF的证据，或者未提供作者的代码在创建该登录名并授予该登录名之前先进行工作.

Granting EXTERNAL ACCESS ASSEMBLY does not allow for setting an Assembly to UNSAFE. That requires the UNSAFE ASSEMBLY permission. It could be a copy / paste error when writing the post, but no proof is shown (i.e. from sys.databases) that TRUSTWORTHY is currently OFF, or that the author's code wasn't working prior to creating that Login and granting that permission to it.

因此，我通过安装最新版本的FSharp.Core来尝试了此操作. 4.1.2–这是我发现的:

So, I just tried this by installing the most recent build of FSharp.Core – 4.1.2 – and here is what I found:

1. 通过以下方式确认TRUSTWORTHY是OFF(即0):

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

尝试将 FSharp.Core 加载为SAFE，只是看它是否有效:

Attempt to load FSharp.Core as SAFE, just 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;

出现以下错误:

Msg 6211，第16级，状态1，第32行
CREATE ASSEMBLY失败，因为安全程序集"FSharp.Core"中的类型"Microsoft.FSharp.Collections.FSharpMap`2"具有静态字段"empty".安全程序集中静态字段的属性必须在Visual C#中标记为只读，在Visual Basic中标记为只读，或者在Visual C ++和中间语言中标记为initonly.

Msg 6211, Level 16, State 1, Line 32
CREATE ASSEMBLY failed because type 'Microsoft.FSharp.Collections.FSharpMap`2' in safe assembly 'FSharp.Core' has a static field 'empty'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.

尝试再次加载 FSharp.Core ，但以UNSAFE:

Attempt to load FSharp.Core again, but as 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;

那行得通.但是，我没有将数据库设置为TRUSTWORTHY ON，也没有创建登录名并授予它EXTERNAL ACCESS ASSEMBLY权限.含义:违反可能是通过运行时验证而不是加载时验证发现的.而且除了这一部分，我无法进行测试，但是我希望会发生错误.

That works. But, I didn't set the Database to TRUSTWORTHY ON, nor did I create a Login and grant it the EXTERNAL ACCESS ASSEMBLY permission. Meaning: the violation is probably found via a run-time verification instead of a load-time verification. And I have no way to test beyond this part, but I would expect that an error will occur.

如果有关此程序集的UNSAFE权限集确实发生错误，那么您可以在不诉诸设置TRUSTWORTHY ON的情况下进行处理，但是您将需要在master和基于证书的证书中创建证书登录:

If an error does occur regarding the UNSAFE Permission Set for this Assembly, then you can handle that without resorting to setting TRUSTWORTHY ON, but you will need to create a Certificate in master and a Certficate-based 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];

如果还需要将Assembly标记为UNSAFE，则可以从master中的DLL创建一个非对称密钥，然后从该非对称密钥中创建一个基于密钥的登录名，然后授予该权限基于密钥的登录UNSAFE ASSEMBLY权限. (这假设您的程序集已签名-并且受密码保护)

IF your Assembly is also required to be marked as UNSAFE, then you can create an Asymmetric Key from the DLL in master and then a Key-based Login from that Asymmetric Key, and then grant that Key-based Login the UNSAFE ASSEMBLY permission. (this assumes that your Assembly is signed -- and protected with a password)

当然，以上所有条件都假定您可以将DLL放到服务器上，或者至少放到SQL Server服务帐户可以访问的共享上，并且您确实提到过要通过十六进制字节来部署此DLL. .应该可以这样做:

Of course, all of the above assumes that you can get the DLL onto the server or at least onto a share that the SQL Server service account has access to, and you did mention wanting to deploy this via hex bytes. That should be possible by doing:

1. 在Visual Studio中，在解决方案资源管理器"中的参考"下，转到 FSharp.Core 的属性"，然后将模型感知设置为True并将权限集设置为Unsafe.这将导致发布过程在构建脚本中包含DLL.
2. 如果DLL已经在目标数据库中，则它可能不会为该Assembly生成CREATE ASSEMBLY语句，因为发布脚本是增量更改.在这种情况下，请转到项目属性，然后在项目设置下，选中创建脚本(.sql文件)的框(如果尚未选中).这将导致构建过程始终生成 _Create.sql 脚本，并且肯定会有 FSharp.Core 的CREATE ASSEMBLY语句.
3. 该CREATE ASSEMBLY [FSharp.Core] FROM 0x...语句显然将用于将Assembly加载到目标DB(即，您的Assembly也要加载到的位置)中.

4. 该CREATE ASSEMBLY [FSharp.Core] FROM 0x...语句还将 成为您在master中创建对象的票证，如下所示:

1. In Visual Studio, under "References" in the "Solution Explorer", go to the "Properties" for FSharp.Core and set Model Aware to True and Permission Set to Unsafe. This will cause the publish process to include the DLL in the build script.
2. If the DLL is already in your target DB, then it probably won't generate the CREATE ASSEMBLY statement for this Assembly since publish scripts are incremental changes. If this is the case, then go to the project properties, and under Project Settings, check the box for Create script (.sql file) (if not already checked). This will cause the build process to always produce a _Create.sql script, and in there will definitely be the CREATE ASSEMBLY statement for FSharp.Core.
3. That CREATE ASSEMBLY [FSharp.Core] FROM 0x... statement will obviously be used to load the Assembly into the target DB (i.e. where your Assembly is also getting loaded into).

4. That CREATE ASSEMBLY [FSharp.Core] FROM 0x... statement will also be your ticket to creating the objects in master as follows:

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];

这在SQL Server 2012上对我有用，唯一的区别是我使用文件路径而不是十六进制字节.

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

这篇关于F#和sqlclr以合理的安全方式进行脚本编写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！