SQL CLR Trigger, How to make a build trusted because of transparent critical code?

Question

I immersed myself in SQL CLR research. Unfortunately, my first example has a problem with transparent code call to security code .

The point in my SQL CLR Trigger is seen as transparent code. And in the trigger, I use Quartz to call the 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, AfterMarketSessionInserted procedure, Line 1..NET Framework error occurred while executing a user routine or aggregate "AfterMarketSessionInserted": System.MethodAccessException: Attempt the transparent security method '.Database.Triggers.MarketSessionTriggers.AfterMarketSessionInserted ()' to access the critical critical is critical . 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 completely transparent, regardless of the transparency of the annotation in the assembly itself. To gain access to critical code security, this assembly must be fully trusted. System.MethodAccessException: at Database.Triggers.FinancialMarketSessionTriggers.AfterFinancialMarketSessionInserted ()

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

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

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

I am open to suggestions.

Answer 1

Why is the SQLCLR code only partially trusted?

By default, the CLR code running inside SQL Server (ie, "SQLCLR") is severely limited so as not to impair the security or stability of SQL Server.

How to make SQLCLR fully trust?

What the CLR code in the assembly can do is controlled (mainly) by using the PERMISSION_SET property for each assembly. If you do not specify PERMISSION_SET when loading the assembly through CREATE ASSEMBLY , the default will be SAFE , which is the most limited and not fully trusted. In order for the CLR code to go beyond SQL Server (to the network, file system, OS, etc.), you need to set Assembly to at least EXTERNAL_ACCESS , but it is still not fully trusted. To be considered fully trusted , you need to install Assembly on UNSAFE .

To set any assembly to 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 database owner has UNSAFE ASSEMBLY permission at the server level (this is usually the case). Although this option is faster / simpler, it is not preferred because TRUSTWORTHY = ON is a fairly wide open security hole.
• Sign the assembly with a password, create an asymmetric key from the assembly, create an input from an asymmetric key, provide a login to the UNSAFE ASSEMBLY system. This is the preferred method.

If you want to learn more about SQLCLR security, especially with regard to how SAFE builds are limited, see the article that I wrote in SQL Server Central (free registration is required to read articles on this site).

Answer 2

Additional information: if you choose the preferred method of signing the assembly and creating an asymmetric key, see the MSDN article

The following is a snippet of SQL from the above article. In the main database, create a 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;