CREATE ASSEMBLY fails with "Unable to resolve token"

3

I am trying to upload some CLR code to an SQL Server 2016 (Developer edition) instance. Overall, the structure is like this:

  • One CLR UDF depends on assembly A
  • Another CLR UDF depends on assembly B
  • Both A and B depend on assembly C

The target framework of the .sqlproj is 4.0. Assemblies A and B are built targeting .net4.0. Assembly C was built targeting .net2.0. All assemblies are set to Model Aware: True, Permission Set: Safe. All assemblies are not signed.

When I publish the .sqlproj to the database server, assemblies C and B go well, but assembly A fails with:

(276,1): SQL72014: .Net SqlClient Data Provider: Msg 6218, Level 16, State 2,
Line 1 CREATE ASSEMBLY for assembly 'A' failed because assembly 'A' failed
verification. Check if the referenced assemblies are up-to-date and trusted
(for external_access or unsafe) to execute in the database. CLR Verifier error
messages if any will follow this message
[ : A.Class1::Method1][mdToken=0x6000010][offset 0x00000001] Unable to resolve token.
[ : A.Class2::Method2][mdToken=0x6000014][offset 0x0000004C] Unable to resolve token.
[ : A.Class3::Method3][mdToken=0x6000017][offset 0x00000001] Unable to resolve token.
[ : A.Class4::Method4][mdToken=0x6000021][offset 0x0000000C] Unable to resolve token.
(276,0): SQL72045: Script execution error.  The executed script:
CREATE ASSEMBLY [A]
    AUTHORIZATION [dbo]
    FROM 0x4D5A9...002A0

I spent about a day researching the topic and didn't find anything that helped. So any ideas would be much appreciated.

UPDATE 1: Setting the Permission Set attribute of all the assemblies and setting the database trustworthy property to ON worked, the assemblies are now being deployed successfully. However, now I'm unable to call the UDFs as they are untrusted :) This is solvable, I'm sure, but that's not the real solution to the issue. And I still don't understand why it doesn't work with Permission Set: Safe.

Regarding the .NET versions on the server and on the dev machine. Dev machine is Win 10. The SQL Server is running in a VM on a WinServer 2012 R2 Standard Core. Both with all the latest updates installed. The versions of .NET installed on the server are (using this snippet https://stackoverflow.com/a/3495491/664178):

PSChildName           Version               Release    Product
-----------           -------               -------    -------
Client                4.6.01055              394271    4.6.1
Full                  4.6.01055              394271    4.6.1
Client                4.0.0.0

On the dev machine:

PSChildName                        Version          Release  Product
-----------                        -------          -------  -------
v2.0.50727                         2.0.50727.4927
v3.0                               3.0.30729.4926
Windows Communication Foundation   3.0.4506.4926
Windows Presentation Foundation    3.0.6920.4902
v3.5                               3.5.30729.4926
Client                             4.6.01038        394254   4.6.1
Full                               4.6.01038        394254   4.6.1
Client                             4.0.0.0

I can't seem to update the .NET on the dev machine to the same version as the server. Potentially because dev machine's Windows is set to defer updates... Can this version mismatch be the source of trouble though?

UPDATE 2: Apparently, these .NET versions are the latest versions for respective platforms (https://msdn.microsoft.com/en-us/library/hh925568(v=vs.110).aspx)

UPDATE 3: Some further things I've tried.

Deploying the database project to a local SQL Server 2016 Express database yields identical results, so looks like the .NET versions mismatch on the dev and server boxes is not the issue.

Also, exactly same behavior was observed when deploying to a LocalDB v12.0 (SQL Server 2014 engine), so probably issue is not with specifically the SQL Server 2016.

Installing .NET 3.5 on Windows Server (Install-WindowsFeature NET-Framework-Core) did not affect the situation as well.

.net
sql-server
.net-assembly
sqlclr
sql-server-2016
asked on Stack Overflow Jun 26, 2016 by bazzilic • edited May 23, 2017 by Community

1 Answer

1

My testing indicates that the issue here is how the reference to the BigInteger Class Library is specified. Looking at the PaillierExt.csproj file, it shows the reference as including the version info:

<Reference Include="BigInteger, Version=1.0.4.36383, Culture=neutral, processorArchitecture=MSIL">

However, the ElGamalExt.csproj file shows the reference (for the same DLL) as being just the name of the DLL:

<Reference Include="BigInteger">

It appears that the default behavior for references in Database Projects is that the reference, if given a version #, is implied Version Specific = false, whereas in a Class Library, if the reference is given a version #, Version Specific is implied as true. In this case it might help to explicitly set the reference to Specific Version = false.

I was able to get all 3 of these projects to build and load successfully by copying the source code (the original that didn't work, not the updated code that moved things around) into 3 newly created Class Library projects. I copied things like Project GUID and references into the new .csproj files, but not the specific version info on that particular reference. I was then able to create a Database Project and a SQLCLR object that referenced the PaillierExt and ElGamalExt methods. Everything worked as expected.

I believe this also explains why it worked when the Assembly was registered as PERMISSION_SET = UNSAFE: it could very well be that the specific version info and implied Specific Version = true property are a recommendation to the CLR that is followed when Assemblies are either SAFE or EXTERNAL_ACCESS, but setting them to UNSAFE allows for by-passing that stated preference.

P.S. I'm not sure if this matters, but another difference from what I normally do is that in the AssemblyInfo.cs file, the AssemblyVersion attribute is using a * for the build #. I did not find that to be a complicating factor here, but if someone is still having an issue after removing the version specific info from the reference, then try replacing the * with a static value. The reason is that if the version # needs to be "specific", then it probably doesn't help to have part of the version # change on each build ;-).

Details regarding the tests, etc can be found in the discussion.

answered on Stack Overflow Sep 26, 2016 by Solomon Rutzky • edited Jan 18, 2021 by Community

User contributions licensed under CC BY-SA 3.0