CODEWITHSUNDEEP
c#.netvisual-studio-2012sql-server-2008-r2sqlclr
N West
N West

Reputation: 6819

Referencing Signed Assembly in SQL Server 2008R2

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

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

Both the referenced assembly and the CLR objects in my database project are set to SAFE since they don't need anything except what's in the database.

The project builds, 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's crazy is that if I build my <<REFERENCED_DLL>> assembly and do not sign it, the project publishes fine and the functions work as expected. I tried signing my database project, and adding AllowPartiallyTrustedCallers to the referenced DLL.

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

Upvotes: 4

Views: 586

Answers (2)

Phil Blackburn
Phil Blackburn

Reputation: 1067

Have you registered the strong key with the database?

  1. Create a master key in the master database, if one does not exist:

    USE MASTER;

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your_password';

  2. Create an asymmetric key in the master database for the strong key. You'll need to copy the .snk file to the database server, but onces it registered you can remove it frm the server.

    CREATE ASYMMETRIC KEY sqlkey FROM FILE = 'path_to_the_strong_name_key';

  3. Create a login from the asymmetric key in the master database.

    CREATE LOGIN yourlogin FROM ASYMMETRIC KEY yourkey;

  4. Give the login you've just created the UNSAFE ASSEMBLY permission

    GRANT UNSAFE ASSEMBLY TO yourlogin;

I prefer to publish directly from Visual Studio, but you should be able do the same with. 

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

Upvotes: 1

Phil Blackburn
Phil Blackburn

Reputation: 1067

For security and stability, SQLCLR has limited access to only a small subset of approved GAC assemblies. All other must be registered explicitly within the database.

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

SQL Server CLR only allows the following to be loaded from the 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

Upvotes: 1

Related Questions