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.

Community
  • 1
  • 1
bazzilic
  • 826
  • 2
  • 8
  • 22
  • What `PERMISSION_SET` is Assembly "A" using? Is your Assembly signed? Have you tried loading these Assemblies on a prior version of SQL Server – Solomon Rutzky Jun 26 '16 at 04:55
  • @srutzky I added this information in the post. All assemblies are `PERMSSION_SET = SAFE`, none of the assemblies are signed. I don't have access to SQL Server 2014 so I didn't try it. But I might start preparing a VM with MSSQL2014 to test it. – bazzilic Jun 26 '16 at 05:00
  • 1
    Ok. Can you test setting them to `UNSAFE`, and while I don't recommend this for Production usage, set the DB to `TRUSTWORTHY = ON`, for the sake of quicker/easier testing? – Solomon Rutzky Jun 26 '16 at 05:02
  • Also, is that SQL Server 2016 Instance running on your dev box? If not, is it at the same level of .NET Framework patches as your dev box? – Solomon Rutzky Jun 26 '16 at 05:07
  • @srutzky updated the question with more information! – bazzilic Jun 26 '16 at 07:19
  • Regarding not being able to call the code now that it is UNSAFE, you probably need to add [\[assembly:AllowPartiallyTrustedCallers\]](https://msdn.microsoft.com/en-us/library/system.security.allowpartiallytrustedcallersattribute.aspx) to your **AssemblyInfo.cs** file. – Solomon Rutzky Jun 26 '16 at 17:58
  • But yes, I think the root cause has to do with .NET Framework differences between the machines. Assembly `C` isn't the stated problem, but it's .NET 2.0 and that's not on the server. Should be a non-issue but still a difference. Is the target Framework for Assembly `A` really 4.0, or is it 4.5 or 4.6 or whatever? Doesn't need to be 4.0, but not sure if 4.6.x is an issue. Maybe try 4.5.2? Also, can you try loading SQL Server Express 2016 on your dev box? Even if just LocalDB, just to test? Is there a non-Windows 10 box you could compile the Assembly on? The .NET builds are Win 10 / non-Win 10. – Solomon Rutzky Jun 26 '16 at 18:04
  • @srutzky I've added update 3 to the post with results of some of your advices. Assembly `C` is built with `v2.0`, assemblies `A` and `B` are both built with `v4.0`. Do you suggest rebuilding them targeting a different version? – bazzilic Jun 27 '16 at 11:05
  • So, what exactly is Assembly `A` using? What classes and methods? Have you gotten this to work anywhere? Is it possible that this is something that _should_ be marked as `UNSAFE`? – Solomon Rutzky Jun 27 '16 at 15:07
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115749/discussion-between-srutzky-and-bazzilic). – Solomon Rutzky Jun 27 '16 at 15:36

1 Answers1

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.

Community
  • 1
  • 1
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • 1
    Thanks a lot for your help and all the effort you put into resolving this issue. Really appreciate it! – bazzilic Sep 26 '16 at 15:37