The problem is that you are creating a random new Asymmetric Key in SQL Server, which does nothing to associate the Login to the Assembly, which is what the Asymmetric Key (or Certificate) is used for.
You need to first sign the Assembly prior to loading it into SQL Server. Whether it is signed with a strong name key (which equates to an Asymmetric Key within SQL Server) or a Certificate, you then create that same Asymmetric Key or Certificate (only the public key is required) in
master and then create the signature-based Login from that.
For examples on how to accomplish this (and in a way that can be automated via Visual Studio / SSDT), please see the series I am writing on working with SQLCLR (on SQL Server Central): Stairway to SQLCLR (Levels 7 and 8 in particular).
If you are working with SQL Server 2017 or newer, then there is an additional complication to account for which is not part of the method described in the SQL Server central articles. For that, please see the two options for the revised approach in my posts:
P.S. Thank you for spending the extra few minutes to not enable TRUSTWORTHY :-)
Since you are trying to load an unsupported .NET Framework library, you won't be compiling that yourself so the steps I outlined above will not work here (though they are still relevant for someone simply misunderstanding and trying to create the Asymmetric Key initially within SQL Server as shown in the question).
But, given that:
- You are attempting to load
System.ServiceModel.Internals and not
- You are not getting the error about a particular library being "malformed or not a pure .NET assembly"
then you can accomplish this (while keeping
TRUSTWORTHY OFF!) by doing the following:
CREATE CERTIFICATE [UnsupportedFrameworkLibraryPermissions]
FROM EXECUTABLE FILE =
CREATE LOGIN [UnsupportedFrameworkLibraryPermissions]
FROM CERTIFICATE [UnsupportedFrameworkLibraryPermissions];
GRANT UNSAFE ASSEMBLY TO [UnsupportedFrameworkLibraryPermissions];
CREATE ASSEMBLY [System.ServiceModel.Internals]
WITH PERMISSION_SET = UNSAFE;
Just keep in mind that this technique does not work with
System.ServiceModel.dll. That library went from being pure MSIL pre-.NET 4.0 to mixed in .NET 4.0, and only pure MSIL libraries can be loaded into SQL Server. Using
ServiceModel worked in SQL Server 2005, 2008, and 2008 R2 because those versions use CLR 2.0 which is tied to .NET Framework versions 2.0, 3.0, and 3.5 in which
ServiceModel was pure-MSIL.
But in general, David Browne is correct in that you should probably be using
HttpWebRequest instead (depending on what you need System.ServiceModel.Internals for).