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.

Solomon Rutzky · Accepted Answer · 2019-12-02 18:30:21Z

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.

Tom Regan · Accepted Answer · 2015-07-02 17:02:55Z

0

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;

answered Jul 2, 2015 at 17:02

Tom Regan

3,8719 gold badges48 silver badges83 bronze badges

Collectives™ on Stack Overflow

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

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related