.NET Framework SQLCLR support

[edwardneal]

This partially comes from the netfx/netcore project merge, but has opened into a more general question: is SqlClient's .NET Framebuild build supported for use in SQLCLR scenarios? My experience so far has been that it isn't working, but some review of the code suggests to me that it can't work.

Background context

I'm trying to test the context connection. When Context Connection is true, SqlClient starts to use a lot of the *Smi and Smi* classes which are in the .NET Framework project, plus various branches within other classes. My library is the foundation of a testbed to prove that the functionality works: first with .NET Framework's intrinsic System.Data.SqlClient, second with Microsoft.Data.SqlClient as-is, and third after any code merges.

System.Data.SqlClient

The sample library which I've used as a testbed is pretty simple. It contains one class, as below:

public class NetFxStoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void uspEcho(System.Data.SqlTypes.SqlString spToEcho)
    {
        var record = new Microsoft.SqlServer.Server.SqlDataRecord(
            new Microsoft.SqlServer.Server.SqlMetaData("Echo", System.Data.SqlDbType.NVarChar, 200)
        );

        record.SetSqlString(0, spToEcho);

        Microsoft.SqlServer.Server.SqlContext.Pipe.Send(record);
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void uspPrintDate()
    {
        using (var cConn = new System.Data.SqlClient.SqlConnection("Context Connection=true"))
        {
            cConn.Open();

            var cmd = new System.Data.SqlClient.SqlCommand("select getdate() as MyDate", cConn);

            DateTime dt = (DateTime)cmd.ExecuteScalar();

            Microsoft.SqlServer.Server.SqlContext.Pipe.Send("The current date is: " + dt.ToString("O"));
        }
    }
}

I then load it into SQL Server with the script:

use [master]

create database SqlClrTestDb

alter database SqlClrTestDb
    set trustworthy on

go

use [SqlClrTestDb]

create assembly NetFx from '<path>\SqlClrTestbed.dll'
    with permission_set = unsafe
go

create procedure usp_NetFxEcho
    @spToEcho nvarchar(200)
as external name NetFx.[SqlClrTestbed.NetFxStoredProcedures].uspEcho
go

create procedure usp_NetFxPrint
as external name NetFx.[SqlClrTestbed.NetFxStoredProcedures].uspPrintDate
go

exec usp_NetFxEcho 'Hello, world!'
exec usp_NetFxPrint
go

drop procedure usp_NetFxEcho
drop procedure usp_NetFxPrint
drop assembly NetFx
go

use [master]
drop database SqlClrTestDb
go

This tests three situations:

1. SQL Server can load the DLL at all
2. uspEcho allows me to issue a result set back to the caller
3. uspPrintDate can make use of the Context Connection connection string parameter and run a query

It works as expected.

Microsoft.Data.SqlClient

Microsoft.Data.SqlClient has a few problems. I've adapted the class as below:

public class MdsStoredProcedures
{
    /*[Microsoft.SqlServer.Server.SqlProcedure]
    public static void uspEcho(System.Data.SqlTypes.SqlString spToEcho)
    {
        var record = new Microsoft.Data.SqlClient.Server.SqlDataRecord(
            new Microsoft.Data.SqlClient.Server.SqlMetaData("Echo", System.Data.SqlDbType.NVarChar, 200)
        );

        record.SetSqlString(0, spToEcho);

        Microsoft.SqlServer.Server.SqlContext.Pipe.Send(record);
    }*/

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void uspPrintDate()
    {
        using (var cConn = new Microsoft.Data.SqlClient.SqlConnection("Context Connection=true"))
        {
            cConn.Open();

            using (var cmd = new Microsoft.Data.SqlClient.SqlCommand("select getdate() as MyDate", cConn))
            {
                DateTime dt = (DateTime)cmd.ExecuteScalar();

                Microsoft.SqlServer.Server.SqlContext.Pipe.Send("The current date is: " + dt.ToString("O"));
            }
        }
    }
}

uspEcho is laid out as per the documentation here. The primary problem for this method is that there's no Microsoft.Data.SqlClient.Server.SqlContext class (or its supporting classes) and thus, no way to return result sets. I did try to use Microsoft.SqlServer.Server.SqlContext, but there's no type forwarding from Microsoft.SqlServer.Server.SqlDataRecord to Microsoft.Data.SqlClient.Server.SqlDataRecord. I commented it out to avoid the compilation error.

uspPrintDate was going to be a bit more important, since it exercises the context connection. This is almost identical to the System.Data.SqlClient classes, and it compiles - I don't think there's much to look at here.

Failure

Once compiled, I can't load the library which uses Microsoft.Data.SqlClient into SQL Server. I'm using this SQL script:

use [master]

create database SqlClrTestDb

alter database SqlClrTestDb
    set trustworthy on

go

use [SqlClrTestDb]

create assembly NetFx from '<path>\SqlClrTestbed.dll'
    with permission_set = unsafe
go

create procedure usp_MdsPrint
as external name NetFx.[SqlClrTestbed.MdsStoredProcedures].uspPrintDate
go

exec usp_MdsPrint
go

drop procedure usp_MdsPrint
drop assembly NetFx
go

use [master]
drop database SqlClrTestDb
go

This behaves unexpectedly; I'm unable to create the assembly as a result of SQL Server trying to load two versions of System.Runtime.CompilerServices.Unsafe. The output I receive is:

Warning: The Microsoft .NET Framework assembly 'system.numerics.vectors, version=4.1.4.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
Warning: The Microsoft .NET Framework assembly 'system.runtime.compilerservices.unsafe, version=6.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
Msg 10300, Level 16, State 1, Line 68
Assembly 'System.Memory' references assembly 'system.runtime.compilerservices.unsafe, version=4.0.4.1, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request.

Interestingly, this occurs even with a binding redirect in sqlservr.exe.config, excerpted below. I'm a little suspicious that SQL Server's assembly load rules might be ignoring this though.

<dependentAssembly>
  <assemblyIdentity name="System.Runtime.CompilerServices.Unsafe" publicKeyToken="b03f5f7f11d50a3a" culture="neutral" />
  <bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0" />
</dependentAssembly>

System.Runtime.CompilerServices.Unsafe v4.0.4.1 corresponds to the NuGet package version 4.5.3. I'm not sure why it's binding to that version of the package - all of the direct and transitive dependencies require at least version 6.0.0.0, and I've walked through the references of every built DLL and found no other version.

I located the v4.0.4.1 System.Runtime.CompilerServices.Unsafe DLLs and tried adding them as assemblies manually, but SQL Server won't allow two versions of the same assembly to be loaded. I thus can't load my testbed DLL to check whether context connections work.

Speculative failure

I'm trying to test context connections because I'm not sure they can actually work. The core of the context connection logic comes from SmiContextFactory. In the constructor, we see the Microsoft.SqlServer.Server.InProcLink.Instance field is converted to a Microsoft.Data.SqlClient.Server.SmiLink object, then the result of SmiLink.GetCurrentContext is passed a Microsoft.Data.SqlClient.Server.SmiEventSink instance and its return value is converted to a Microsoft.Data.SqlClient.Server.SmiContext object. However, when I look at the SqlAccess assembly which is shipped as part of SQL Server, all of these types are in the Microsoft.SqlServer.Server namespace in System.Data; I think the type conversion will fail, leaving the connection unable to open.

Issue/question

I've raised this as an issue because adding Microsoft.Data.SqlClient to a clean .NET Framework project results in SQL Server being unable to load the resultant DLL. My broader question is whether SQLCLR usage is supported in SqlClient's .NET Framework build - both for context connections, and for returning result sets.

If M.D.S' .NET Framework build is supposed to support SQLCLR usage, does a working sample environment exist? I didn't see anything in the test projects. If it's not supposed to support SQLCLR, I'll close this issue with a couple of PRs to make sure that the Context Connection connection string parameter throws and to remove the underlying classes from the .NET Framework library.

[David-Engel]

is SqlClient's .NET Framework build supported for use in SQLCLR scenarios?

Not at this time.

If M.D.S' .NET Framework build is supposed to support SQLCLR usage, does a working sample environment exist? I didn't see anything in the test projects. If it's not supposed to support SQLCLR, I'll close this issue with a couple of PRs to make sure that the Context Connection connection string parameter throws and to remove the underlying classes from the .NET Framework library.

No, a working sample doesn't exist.

First, thanks for actually trying it out. I knew it wouldn't work, but I've never actually tried it to see what the errors look like.

While MDS in SQLCLR isn't supported at this time, there is the open question of whether it will be supported in the future. That question hasn't been answered. Since SDS feature development has stopped, it seems obvious that, just like every other product that has an SDS dependency, SQL will either need to move forward with SQLCLR support with MDS or deprecate SQLCLR. I imagine the answer will eventually be made based on customer demand. (Not my call.) In the meantime, we've tried to keep the SQLCLR-related code intact in the inherited netfx code in the hopes that it will make any future SQLCLR support decision easier to implement. So please leave the existing code there, for now.

David

[edwardneal]

Thanks @David-Engel - that makes sense.

I'm afraid I'm not able to test what the MDS context connections' errors would actually look like though - I can't get SQLCLR to load any DLL referencing MDS. I've been able to get further than before, but I quickly hit a roadblock which had the same effect. The details are at the end, but they're not all that relevant - "MDS isn't supported for use in SQLCLR environments" is clear enough. I wasn't able to find that in any documentation though, and the connection string doesn't throw. Could the MDS and the SQLCLR documentation be changed to make that clearer please?

Since SDS feature development has stopped, it seems obvious that, just like every other product that has an SDS dependency, SQL will either need to move forward with SQLCLR support with MDS or deprecate SQLCLR. I imagine the answer will eventually be made based on customer demand. (Not my call.)

To speculate here: I think deprecation's slightly more likely. Between SQL Server Language Extensions (for .NET Core integration and code execution) and the recent JSON support (potentially for data transport) it feels like SQL Server's headed in that direction. It'd be nice if there was more clarity on that though.

---

The original issue was essentially that SQL Server wouldn't allow two versions of the same assembly to be loaded, and wasn't respecting the binding rules in app.config. That description of behaviour is accurate - this blog post confirms that SQLCLR doesn't support using the app.config file to perform assembly redirection. Instead, the correct approach is to create the oldest required version of the assembly, then use ALTER ASSEMBLY multiple times to direct it to each newer version required.

I also need to load some of my SQLCLR testbed's dependencies into SQL Server manually via CREATE ASSEMBLY. This is because the DLLs don't pass SQL Server's verification process, so can't load automatically. Oddly enough, if they're loaded manually once and then their assembly is dropped, they'll load automatically following that. It's strange and would need to be fixed before MDS introduced SQLCLR support, but I was simply trying to shoehorn the assemblies in so that I could test the use of a context connection!

I loaded the assemblies below, in order:

DLL	Version	NuGet version	Path within package
System.Runtime	4.0.0.0	4.0.0	ref/dotnet
System.Runtime.CompilerServices.Unsafe	4.0.4.0	4.5.1	lib/netstandard2.0
System.Runtime.CompilerServices.Unsafe	4.0.4.1	4.5.2	lib/netstandard2.0
System.Runtime.Caching	4.0.0.0	(GAC)
System.Runtime.Serialization	4.0.0.0	(GAC)
System.Net.Http	4.0.0.0	(GAC)
System.Windows.Forms	4.0.0.0	(GAC)
System.Threading.Tasks	4.0.0.0	(GAC)
System.Collections	4.0.0.0	(GAC)
System.Threading.Tasks.Extensions	4.0.0.0	(GAC)
System.Runtime.CompilerServices.Unsafe	6.0.0.0	6.0.0	lib/net461

I wasn't able to load System.Runtime.CompilerServices.Unsafe 6.0.0.0. When I tried to alter the assembly based on the file from lib/net461 in the NuGet package, I received the error:

ALTER ASSEMBLY failed because the referenced assemblies would change. The referenced assembly list must remain the same.

This is because earlier versions of the library referenced System.Runtime, while this one doesn't. When I tried to do the same thing based on the file from lib/netstandard2.0, I had to load the .NET Standard DLL first. Retrying the ALTER ASSEMBLY for System.Runtime.CompilerServices.Unsafe resulted in the same error: the referenced assemblies would change.

Wrapping that attempt up, I'm reasonably sure that there'll need to be changes to SQL Server simply to allow any assembly referencing MDS to be loaded, plus the ones you've mentioned to let context connections work. None of that's a SqlClient issue exactly; I just can't find a reference to the fact that it's unsupported in the documentation.

[edwardneal]

With the merge of #2862, the use of Context Connection=true in a connection string is blocked. While the .NET Framework build of Microsoft.Data.SqlClient could potentially work when run in a SQLCLR context, it doesn't support connections to the context connection.

I've got a set of PRs following this which will remove the now-unused set of classes. To track this:

• Prevent users from accessing Context Connection functionality: Merge ContextConnection handling #2862
• Remove SqlCommand code paths. Removes SqlDataReaderSmi, SqlClientWrapperSmiStream, SqlClientWrapperSmiStream, SqlClientWrapperSmiStreamChars, SqlSequentialTextReaderSmi, SqlSequentialStreamSmi: Remove SqlCommand code paths for context connections #2996
• Remove SqlConnection code paths. Removes SqlInternalConnectionSmi, SmiLink, SmiContext, SmiRequestExecutor, SmiEventStream
• Clean up references to SmiContextFactory. Removes SmiContextFactory, SmiStream, .NET Framework-only components of SmiXetterAccessMap, TriggerAction, SmiConnection
• Modify ValueUtilsSmi et all, removing references to the rarely-referenced and now unused SmiEventSink type hierarchy
• Remove SmiEventSink type hierarchy
• Simplify the ITypedSetters, ITypedGetters and SmiRecordBuffer type hierarchies. Removes SmiRecordBuffer

I don't expect any of these besides #2862 to require public API surface changes. They'll all be quite large PRs though, so I might spread the changes out over more of them as makes sense over the coming months.

[JuergenAuer]

Hi @edwardneal

but some review of the code suggests to me that it can't work.

it can work - but with a completely different solution.

Short: Create a web service (use old asp, use wcf, use NET.8 minimal web api) that can talk via raw http, SOAP or POST + Xml or JSON.

Create a background procedure in NET.4.8 with a class that inherits System.Web.Services.Protocols.HttpGetClientProtocol (POST is included). This procedure talks with your web service.

Create some CLR-SP in NET.4.8, they send the data to that procedure.

So only DLL listet

https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/supported-net-framework-libraries?view=sql-server-ver16

are used. System.Web.Services is supported. No additional dll must be loaded, only GAC dlls used.

--

I'm running an old application, startet 2006. Switched to a newer SQL server with CLR-integration. Loading additional assemblies is a terrible idea. Breaks updates / patches. So first I've started using NET.Remoting (loading assemblies unsafe). Later switched to that solution with wcf and a SOAP client.

Last year switched to a NET.8 service, connected from a small NET.4.8 client. The client creates a Xml-document, via POST, a different Xml-document is returned.

Sending mails, checking other servers, routing calculations - all you want to do with NET is possible.

[edwardneal]

Thanks @JuergenAuer. The approach you're describing makes sense, and once a SQLCLR SP is running, as you've said, it can naturally POST to any HTTP endpoint - whether it's backed by .NET Framework, .NET Core, or any other runtime/language.

The problems that this issue relates to are twofold:

• Microsoft.Data.SqlClient can't connect to a context connection
• I can't figure out how to load assemblies referencing Microsoft.Data.SqlClient for SQLCLR purposes

The first problem is partially resolved by #2862: we're explicitly stating that SqlClient doesn't support context connections (and can't - SQL Server reflects into a type with the same name in System.Data.SqlClient.) The more complete resolution will come as progressively more of the never-functional SQLCLR infrastructure is removed.

I'm planning to re-run the tests in this issue at some point after the release of SqlClient v6.0. Its updated references might have fixed this problem.

[JuergenAuer]

Hi @edwardneal

• I can't figure out how to load assemblies referencing Microsoft.Data.SqlClient for SQLCLR purposes

simple answer: Don't try it, forget it. I've used such things between 2008 and 2012. It's terrible, a database with tons of system-assemblies, every patch day trouble.

Then switched to the "small client", no single system assembly loaded. Now life is much easier. Using only the published list of supported DLL was an excellent decision.