如何允许SQL CLR函数在并行查询计划中运行，并且还具有数据访问权限

I have a written a number of SQL CLR functions (UDF) that reads data from an external DB2 database hosted on an IBM iSeries (using IBM DB2 .Net Provider). So that the function has the necessary permissions to read this data, I need to decorate the function with the SqlFunction attribute having the DataAccess property set to DataAccessKind.Read. I also deploy the assembly as UNSAFE.

我编写了许多SQL CLR函数（UDF），它从IBM iSeries上托管的外部DB2数据库（使用IBM DB2 .Net Provider）读取数据。为了使该函数具有读取此数据所需的权限，我需要使用将DataAccess属性设置为DataAccessKind.Read的SqlFunction属性来修饰该函数。我还将程序集部署为UNSAFE。

The time taken to read data from the DB2 database is relatively slow (eg, 3ms for the simplest ExecuteScalar).

从DB2数据库读取数据所花费的时间相对较慢（例如，对于最简单的ExecuteScalar，为3ms）。

I use these UDFs to effectively merge data from the DB2 database into Sql Server views.

我使用这些UDF将DB2数据库中的数据有效地合并到Sql Server视图中。

For example, suppose my UDF is defined as

例如，假设我的UDF定义为

[SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true)] 
public static SqlMoney GetCostPrice(SqlString partNumber)
{
    decimal costPrice;
    // open DB2 connection and retrieve cost price for part
    return new SqlMoney(costPrice);
}

and then use this in my SQL View as:

然后在我的SQL视图中使用它：

select Parts.PartNumber,
       dbo.GetCostPrice(Parts.PartNumber) as CostPrice
from Parts

The poor performance problems could be impacted significantly if I could run my SQL Views with a parallel query plan.

如果我可以使用并行查询计划运行SQL视图，那么性能问题可能会受到严重影响。

There are documented techniques on how to force a query plan to run in parallel rather than serial but these techniques have limitations imposed by SQL Server, one of which is that a SQL CLR defined function MUST have DataAccess = DataAccessKind.None.

有关如何强制查询计划并行而不是串行运行的文档化技术，但这些技术具有SQL Server强加的限制，其中之一是SQL CLR定义的函数必须具有DataAccess = DataAccessKind.None。

But if I set DataAcessKind to None then I get an exception when attempting to open any DbConnection within the function.

但是如果我将DataAcessKind设置为None，那么在尝试打开函数中的任何DbConnection时会出现异常。

And that is my problem! How can I run my UDF in a parallel query plan while still allowing it to read data from the external database?

那是我的问题！如何在并行查询计划中运行UDF，同时仍允许它从外部数据库中读取数据？

The best idea I have to address this is to hard-code DataAccess = DataAccessKind.None in my SqlFunction attribute and then, at runtime, within the body of the function elevate permissions with Code Access Security so that subsequent code has permissions to open DbConnection objects.

我必须解决这个问题的最好方法是在我的SqlFunction属性中硬编码DataAccess = DataAccessKind.None，然后在运行时，在函数体内使用Code Access Security提升权限，以便后续代码有权打开DbConnection对象。

But I can't figure out how to do it? As an experiment, I have tried the following

但我无法弄清楚该怎么做？作为实验，我尝试了以下内容

    [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
    public static SqlMoney TestFunction()
    {
        var sqlPerm = new SqlClientPermission(PermissionState.Unrestricted);
        sqlPerm.Assert();

        using (var conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
        }

        return new SqlMoney();
    }

and invoke from Sql Server Management Studio with:

并从Sql Server Management Studio调用：

select dbo.TestFunction()

but I continue to get a security exception...

但我继续得到安全例外......

A .NET Framework error occurred during execution of user-defined routine or aggregate "TestFunction": System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method. System.InvalidOperationException: at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc) at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation) at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink) at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext() at System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectionString options, Object providerInfo, DbConnection owningConnection) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at UserDefinedFunctions.UserDefinedFunctions.TestFunction()

在执行用户定义的例程或聚合“TestFunction”期间发生.NET Framework错误：System.InvalidOperationException：在此上下文中不允许数据访问。上下文是未使用DataAccessKind.Read或SystemDataAccessKind.Read标记的函数或方法，是从表值函数的FillRow方法获取数据的回调，或者是UDT验证方法。 System.InvalidOperationException：位于Microsoft.SqlServer.Server.InProcLink的System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext（SmiEventSink接收器，布尔throwIfNotASqlClrThread，布尔fAllowImpersonation）中的System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode（SqlAccessApiReturnCode eRc） System.Data.SqlClient.SqlConnectionFactory.CreateConnection（DbConnectionOptions）上的System.SData.SqlClient.SqlConnectionFactory.GetContextConnection（SqlConnectionString选项，Object providerInfo，DbConnection owningConnection）中的Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext（）的.GetCurrentContext（SmiEventSink eventSink）。在系统上的System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection（DbConnection owningConnection，DbConnectionPoolGroup poolGroup）处的System.Data.ProviderBase.DbConnectionFactory.GetConnection（DbConnection owningConnection）处的options，Object poolGroupProviderInfo，DbConnectionPool pool，DbConnection owningConnection）位于UserDefinedFunctions.UserDefinedFunctions.TestFunction（）的System.Data.SqlClient.SqlConnection.Open（）的m.Data.ProviderBase.DbConnectionClosed.OpenConnection（DbConnection outerConnection，DbConnectionFactory connectionFactory）

Anyone got any ideas?

有人有任何想法吗？

Thanks in advance.

提前致谢。

(btw, I am running on SQL 2008 using .Net 3.5)

（顺便说一句，我使用.Net 3.5在SQL 2008上运行）

1 个解决方案

#1

As far as my testing (against SqlConnection to SQL Server) shows, this can only be accomplished by using a regular / external connection (i.e. not Context Connection = true) and adding the Enlist keyword to the Connection String, set to false:

至于我的测试（针对SqlConnection到SQL Server）显示，这只能通过使用常规/外部连接（即不是Context Connection = true）并将Enlist关键字添加到Connection String，设置为false来实现：

Server=DB2; Enlist=false;

But there does not seem to be any way to make this work when using Context Connection = true. The Context Connection is automatically part of the current transaction and you cannot specify any other connection string keywords when using the Context Connection. What does the transaction have to do with it? Well, the default for Enlist is true, so even if you do have a regular / external connection, if you don't specify Enlist=false;, then you get the same

但是在使用Context Connection = true时似乎没有任何方法可以使这项工作。 Context Connection自动成为当前事务的一部分，在使用Context Connection时不能指定任何其他连接字符串关键字。交易与它有什么关系？好吧，Enlist的默认值是true，所以即使你有常规/外部连接，如果你没有指定Enlist = false;那么你也会得到相同的

Data access is not allowed in this context.

在此上下文中不允许数据访问。

error that you are getting now.

你现在得到的错误。

Of course, this is a moot point because there is no purpose in using the Context Connection in this particular case as it would then require using a Linked Server, and it was pointed out in a comment on the Question that the "The Linked Server Db2 Providers (from MS) are unbelievably slow".

当然，这是一个没有实际意义的问题，因为在这种特殊情况下使用Context Connection没有任何意义，因为它需要使用链接服务器，并且在问题的评论中指出“链接服务器Db2”提供商（来自MS）的速度令人难以置信“。

It was also pointed out that maybe using TransactionScope with an option of Suppress might work. This can't work because you aren't allowed to instantiate a TransactionScope object (with any of the three options: Required, RequiresNew, or Suppress) if both DataAccess and SystemDataAccess are set to None (which is their default value).

还有人指出，使用带有Suppress选项的TransactionScope可能会有效。这不起作用，因为如果DataAccess和SystemDataAccess都设置为None（这是它们的默认值），则不允许实例化TransactionScope对象（具有三个选项中的任何一个：Required，RequiresNew或Suppress）。

Also, regarding the desire to

另外，关于欲望

elevate the UserDataAccess status of a UDF at runtime.

在运行时提升UDF的UserDataAccess状态。

this is just not possible due to UserDataAccess not being a run-time option. It is determined when the CREATE FUNCTION statement is executed (the one that has AS EXTERNAL NAME [Assembly]... as the definition. The UserDataAccess and SystemDataAccess properties are meta-data that is stored with the Function. You can see the setting of either of these by using the OBJECTPROPERTYEX built-in function:

由于UserDataAccess不是运行时选项，因此这是不可能的。确定执行CREATE FUNCTION语句的时间（具有AS EXTERNAL NAME [Assembly] ...的语句作为定义.UserDataAccess和SystemDataAccess属性是与Function一起存储的元数据。您可以看到设置通过使用OBJECTPROPERTYEX内置函数中的任何一个：

SELECT OBJECTPROPERTYEX(OBJECT_ID(N'SchemaName.FunctionName'), 'UserDataAccess');

Your two options seem to be:

你的两个选择似乎是：

Use a provider that supports the Enlist keyword so that it can be set to false, or if it does not enlist by default, then doesn't otherwise require DataAccess to be set to Read. According to the suggested documentation to review ( Integrating DB2 Universal Universal Database for iSeries with for iSeries with Microsoft ADO .NET ), the options appear to be:

使用支持Enlist关键字的提供程序，以便将其设置为false，或者如果默认情况下不注册，则不要求将DataAccess设置为Read。根据建议的文档（用于iSeries的DB2通用通用数据库与Microsoft ADO .NET的集成），选项似乎是：
- OleDb
- 的OleDb
- ODBC
- ODBC
- IBM DB2 for LUW .NET
- IBM DB2 for LUW .NET
Build a middle-tear being a web service that a SQLCLR function can pass the request to, it will use whatever provider to get the info, and it will respond with the info. The SQLCLR function is then not doing any direct data access, and the web service can do its own caching (you said that the source data doesn't change that often) to improve performance (even if only caching the values for 1 - 5 minutes). Yes, this does introduce an external dependency, but it should otherwise work as desired.

构建一个中间撕裂是一个SQLCLR功能可以传递请求的Web服务，它将使用任何提供者来获取信息，它将响应信息。然后，SQLCLR函数不进行任何直接数据访问，并且Web服务可以执行自己的缓存（您说源数据不会经常更改）以提高性能（即使只缓存值1到5分钟））。是的，这确实引入了外部依赖，但它应该按照需要工作。

#1

Server=DB2; Enlist=false;