SqlClient UDT support needs an assembly-plugin model

[dotMorten]

I built a .NET Standard implementation of the Microsoft.SqlServer.Types classes. This works great for insertion, but when deserializing I'll get an exception like this:

Instead I have to write something like:

var binvalue = reader.GetSqlBytes(rowid);
var g = SqlGeometry.Deserialize(binvalue);

This makes sense, because SqlClient has no idea I provided an implementation it could use. Assembly redirect only works for assemblies with the same name and public key, so that's not really an option.
How do we across all the different platforms allow us to use UDT in assemblies defined on each platform, and get the SqlClient to import the types?

The issue is somewhat called out in the doc:

SqlClient may fail to load a UDT in the event of mismatched UDT versions or other problems. (1)

A local copy of the UDT assembly must be available to the client project. (2)

The assembly loaded in SQL Server and the assembly on the client must be compatible in order for the UDT to be created on the client. For UDTs defined with the Native serialization format, the assemblies must be structurally compatible. For assemblies defined with the UserDefined format, the assembly must be available on the client.
You do not need a copy of the UDT assembly on the client in order to retrieve the raw data from a UDT column in a table. (3)

In some situations, you may want to retrieve the raw data from the UDT column. Perhaps the type is not available locally (4)

Warning about client side usage of GEOMETRY, GEOGRAPHY and HIERARCHYID

A .NET Standard Implementation of the geometry types for reference which can be used as an example (just tweak unit tests to call GetValue instead of GetSqlBytes on the data reader):
https://github.com/dotMorten/Microsoft.SqlServer.Types/blob/89c5e6158867d9603d93fde6286a0f32f01fa96d/src/Microsoft.SqlServer.Types.Tests/DBTests.cs#L81

[dotMorten]

I'm realizing this is phrased like a question, when it was more of a rhetorical question (since you can't do this today)
I was looking for commitment to extending SqlClient to allow registering/redirecting to other assemblies that doesn't necessarily match the assembly used inside SqlServer (ie different assembly name, version and/or token).

[bricelam]

I wonder if it would work with "open source signing" (see this special MSBuild task). It uses the public key to give an assembly the same identity without requiring the private key. The only restriction is that you can't GAC it.

[bricelam]

Get the original public key using sn.exe -e Microsoft.SqlServer.Types.dll key.snk

[dotMorten]

Given the new guidance that you should check in your signing keys to your repo, I'd love to be able to just sign my assembly with the same key, so assembly redirect could "just work". However since Microsoft..SqlServer.Types isn't open-source there is no repo to check their key in.

[dotMorten]

Wait what?!? I can extract and re-use their key?

[bricelam]

Note this is different than the recent open source guidance around strong name keys. It's a bit of magic for stealing an assembly's identity without having its private key. This is why strong naming is not about security. 😉

[bricelam]

1. Delay sign using just the public key
2. Flip a bit saying it's actually signed (but never actually sign it using the private key)
3. Hope nobody verifies the signature (only GACing does)

[bricelam]

Also note strong naming is different than code signing. Code signing is about security; those signatures are verified.

[dotMorten]

So I did get it working just by delay-signing with the public key. Not sure what the repercussions about this (I couldn't get the special OpenSourceSign task to run) but since it's doable, we could probably close this thread.

[divega]

@dotMorten I would vote for keeping this open. I am thinking about what you said in #1 (comment), and I believe there is still an opportunity to enable plugging in your own types without having to hack with signing.

[divega]

As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues.

[cheenamalhotra]

Since we have open issues for supporting missing SqlTypes in .NET Core, closing this issue.

• Spatial types (SqlGeometry and SqlGeography) Implement SqlGeography and SqlGeometry for Spatial Types support for .Net Core #30
• SqlHierarchyId Implement SqlHierarchyId type for .NET Core #322