How to allow a SQL CLR function to run in parallel query plan and also have data access permissions

Question

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.

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

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

For example, suppose my UDF is defined as

[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:

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.

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.

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

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?

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.

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:

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()

Anyone got any ideas?

Thanks in advance.

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

Answer 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:

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

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".

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).

Also, regarding the desire to

elevate the UserDataAccess status of a UDF at runtime.

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:

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

---

Your two options seem to be:

1. 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:

   • OleDb
   • ODBC
   • IBM DB2 for LUW .NET

2. 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.