SQL CLR Trigger, How to make an assembly trusted d

问题:

I have dived into researching the SQL CLR. Unfortunately, my first example has problem with transparent code call to security code.

The point is my SQL CLR Trigger is treated as transparent code. And in trigger, I use Quartz to call to Quartz Windows service :

var properties = new NameValueCollection();
properties["quartz.scheduler.instanceName"] = "ServerScheduler";

properties["quartz.scheduler.proxy"] = "true";
properties["quartz.scheduler.proxy.address"] = string.Format("tcp://{0}:{1}/{2}", "localhost", "555",
                "QuartzScheduler");

var schedulerFactory = new StdSchedulerFactory(properties);

IScheduler scheduler = schedulerFactory.GetScheduler();

Error:

(135,1): SQL72014: .Net SqlClient Data Provider: Msg 6522, Level 16, State 1, Procedure AfterMarketSessionInserted, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "AfterMarketSessionInserted": System.MethodAccessException: Attempt by security transparent method '.Database.Triggers.MarketSessionTriggers.AfterMarketSessionInserted()' to access security critical method 'Quartz.Impl.StdSchedulerFactory..ctor(System.Collections.Specialized.NameValueCollection)' failed.

Assembly 'Database, Version=1.0.5275.15169, Culture=neutral, PublicKeyToken=null' is partially trusted, which causes the CLR to make it entirely security transparent regardless of any transparency annotations in the assembly itself. In order to access security critical code, this assembly must be fully trusted. System.MethodAccessException: at Database.Triggers.FinancialMarketSessionTriggers.AfterFinancialMarketSessionInserted()

--------------

Why the SQL CLR Trigger code is considered as transparent code and trusted partially?

How to make SQL CLR Trigger code not a transparent code or make it trusted fully?

I'm open to suggesstions.

回答1:

Why is SQLCLR code only trusted partially?

By default, CLR code running inside of SQL Server (i.e. "SQLCLR") is highly restricted so as to not degrade security or stability of SQL Server.

How do you make SQLCLR fully trusted?

What the CLR code within an Assembly can do is controlled (mostly) by the PERMISSION_SET property of each Assembly. If you do not specify a PERMISSION_SET when loading the Assembly via CREATE ASSEMBLY, the default will be SAFE which is the most restricted and not fully trusted. In order for the CLR code to be able to reach outside of SQL Server (to the network, file system, OS, etc.) you would need to set the Assembly to at least EXTERNAL_ACCESS, but this is still not fully trusted. In order to be considered fully trusted you need to set the Assembly to UNSAFE.

In order to set any Assembly to either EXTERNAL_ACCESS or UNSAFE, you need to do one of the following:

• Set the database containing the Assembly to TRUSTWORTHY = ON. This assumes that the owner of the database has the UNSAFE ASSEMBLY server-level permission (which is typically the case). While this option is quicker / easier, it is not preferred due to TRUSTWORTHY = ON being a fairly wide-open security hole.
• Sign the Assembly with a password, create an Asymmetric Key from the Assembly, create a Login from the Asymmetric Key, grant the Login the UNSAFE ASSEMBLY permission. This is the preferred method.

If you want a more detailed look at SQLCLR security, especially in relation to how restricted SAFE Assemblies are, check out this article that I wrote on SQL Server Central (free registration is required to read articles on that site).

回答2:

Some additional information: if you choose the preferred method of signing the assembly and creating an asymetric key, see this MSDN article

The SQL snippet below is from the above-mentioned article. In the master database create the key and login:

USE master; 
GO  

CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\MyDBApp\SQLCLRTest.dll';   
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey ;  
GRANT UNSAFE ASSEMBLY TO SQLCLRTestLogin ;
GO

Then create the assembly:

CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
WITH PERMISSION_SET = UNSAFE;