Signed assembly reference in SQL Server 2008R2

Question

I have a C # .NET 3.5 class library, which is used in some SSIS script tasks, so it needs to be loaded into the GAC, which means it needs to be signed. I created a key and signed a DLL, and it works in SSIS.

Now I also want to load this DLL into the SQL server and create some CLR objects that reference the methods in the class in this DLL. I have a VS2012 database project and added a link to the DLL by setting the IsVisible and ModelAware properties to true. I extended the UserDefinedFunctions class to add two of my functions that call static methods in the DLL.

Both the referenced assembly and the CLR objects in my database project are set to SAFEbecause they don't need anything other than what's in the database.

The project is being built, but fails when publishing to my local database with this strange error:

Creating [<<REFERENCED_DLL>>]...
Creating [<<CURRENT_DATABASE_CLR>>]...
(288,1): SQL72014: .Net SqlClient Data Provider: 
   Msg 6218, Level 16, State 2, Line 1 
   CREATE ASSEMBLY for assembly '<<CURRENT_DATABASE_CLR>>' failed
   because assembly '<<CURRENT_DATABASE_CLR>>' 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
[ : UserDefinedFunctions::sf_Function1][mdToken=0x6000001][offset 0x00000005]
   Unable to resolve token.
[ : UserDefinedFunctions::sf_Function2][mdToken=0x6000002][offset 0x00000005] 
   Unable to resolve token.

What madness is that if I create an assembly <<REFERENCED_DLL>>and do not sign it, the project will be published perfectly, and the functions will work as expected. I tried to sign my database project and added AllowPartiallyTrustedCallersto the reference DLL.

I really want to have only one version of this DLL that can be used in both the GAC and the SQL server. How can i do this?

Answer 1

SQLCLR GAC. .

. https://msdn.microsoft.com/en-us/library/ms403279(v=sql.105).aspx

SQL Server CLR GAC :

CustomMarshalers
Microsoft.VisualBasic
Microsoft.VisualC
mscorlib
System
System.Configuration
System.Data
System.Data.OracleClient
System.Data.SqlXml
System.Deployment
System.Security
System.Transactions
System.Web.Services
System.Xml 

Answer 2

?

• , :

  ;

  - = 'your_password';

• . .snk , , .

  CREATE ASYMMETRIC KEY sqlkey FROM FILE = 'path_to_the_strong_name_key';

• .

  yourlogin FROM ASYMMETRIC KEY yourkey;

• , , UNSAFE ASSEMBLY

  ;

Visual Studio, .

 CREATE ASSEMBLY yourSQLCLR
 FROM 'C:\\yourSignedSQLCLR.dll' 
 WITH PERMISSION_SET = UNSAFE | SAFE |  EXTERNAL_ACCESS