SQL CLR: Streaming table valued function results

Question

My issue is very similar to this issue.

However, I'm using SQL Server 2005 Service Pack 2 (SP2) (v9.0.3042) and the solution posted there does not work for me. I tried using both connection strings. One is commented out in my code.

I realize I can store all the results in a List or ArrayList in memory and return that. I've done that successfully, but that is not the goal here. The goal is to be able to stream the results as they are available.

Is this possible using my version of SQL Server?

Here's my code : (Note that the parameters aren't actually being used currently. I did this for debugging)

public static class StoredProcs
{
    [SqlFunction(
        DataAccess = DataAccessKind.Read,
        SystemDataAccess=SystemDataAccessKind.Read,
        FillRowMethodName="FillBaseline",
        TableDefinition = "[baseline_id] [int], [baseline_name] [nvarchar](256), [description] [nvarchar](max), [locked] [bit]"
        )]
    public static IEnumerable fnGetBaselineByID(SqlString projectName, SqlInt32 baselineID)
    {
        string connStr = "context connection=true";
        //string connStr = "data source=.;initial catalog=DBName;integrated security=SSPI;enlist=false";
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(String.Format(@"
                SELECT *
                FROM [DBName].[dbo].[Baseline] WITH (NOLOCK)
            "), conn))
            {
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return new Baseline(reader);
                    }
                }
            }
        };
    }

    public static void FillBaseline(Object obj, out SqlInt32 id, out SqlString name, out SqlString description, out bool locked)
    {
        Baseline baseline = (Baseline)obj;
        id = baseline.mID;
        name = baseline.nName;
        description = baseline.mDescription;
        locked = baseline.mLocked;
    }
}

Here's part of my SQL deploy script:

CREATE ASSEMBLY [MyService_Stored_Procs]
FROM 'C:\temp\assemblyName.dll'
WITH PERMISSION_SET = SAFE

When I use the connection string "context connection=true" I get this error:

An error occurred while getting new row from user defined Table Valued Function : 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.

When I use the other connection string I get this error:

An error occurred while getting new row from user defined Table Valued Function : System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Answer 1

Upon further research and trial and error I found my solution. The article that I mentioned here says

your assembly must be created with permission_set=external_access

This is much easier said than done, but was a good starting point. Simply using that line in place of permission_set=safe gives the error:

CREATE ASSEMBLY for assembly 'assemblyName' failed because assembly 'assemblyName' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

So the first thing I had to do was sign my dll file. To do that in Visual Studio 2010, you go to the project properties, Signing tab, and check "Sign the assembly" and give it a name. For this example, the name is MyDllKey. I chose not to protect it with a password. Then, of course, I copied the dll file to the sql server: C:\Temp

Using this page as a reference, I created a SQL login based on the above key using these 3 commands:

CREATE ASYMMETRIC KEY MyDllKey FROM EXECUTABLE FILE = 'C:\Temp\MyDll.dll'
CREATE LOGIN MyDllLogin FROM ASYMMETRIC KEY MyDllKey
GRANT EXTERNAL ACCESS ASSEMBLY TO MyDllLogin

Once the login is created as above, I can now create the assembly using this:

CREATE ASSEMBLY [MyDll]
FROM 'C:\Temp\MyDll.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

Now the only thing left to do is use the proper connection string. Apparently using enlist=false in combination with connection=true is not possible. Here is an example of the connection string I used.

string connStr = @"data source=serverName\instanceName;initial catalog=DBName;integrated security=SSPI;enlist=false";

And it works!