SQL CLR Trigger, How to make an assembly trusted due to transparent code call critical code?

Question

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.

Answer 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:

• 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.
• 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.

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.