I get the following errors in my development database:
A .NET Framework error occurred during execution of user-defined routine or aggregate "SpCreateTable":
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: Synchronization, ExternalThreading
Is the correct solution to set trustworthy = on? What are security concerns with this?
The TRUSTWORTHY
property of a database (when set to ON
) essentially declares to SQL Server that code contained within that database, and executing in an impersonated context, should be allowed to reach outside of that database while maintaining that impersonated security context. It also allows for all SQLCLR Assemblies in that Database to be set to EXTERNAL_ACCESS
and UNSAFE
, whether or not that code reaches outside of the server (outside meaning: network access, file system access, registry access, environment access, etc).
It is a rather generic means of allowing for this as it covers all code within the database. Using Certificates and/or Asymmetric Keys to sign modules--procs and/or assemblies--allow for more granular control over what code has what permissions.
Setting a Database to TRUSTWORTHY
also allows any process starting in this Database to reach up to the Server-level and/or across to other Databases. Normally a process is confined / quarantined to the Database where it started. If the Database is owned by the "sa" Login, then any process initiated in that Database and running as "dbo" will effectively have "sa" privileges (yikes!).
Rather than trying to describe here, in the amount of detail required to fully communicate the specifics about impersonation, extending said impersonation, signing modules, etc, I recommend perusing the following resources on this topic:
You should avoid setting your database to TRUSTWORTHY
as much as possible. If you really must have multithreading / async calls AND if you have the source code and are compiling the assembly, then I cannot think of a reason to use the SET TRUSTWORTHY ON
option. Instead, you should sign the assembly with a password and use the following commands to set up the preferred method of allowing EXTERNAL_ACCESS
and UNSAFE
assemblies:
USE [master];
CREATE ASYMMETRIC KEY [ClrPermissionsKey]
AUTHORIZATION [dbo]
FROM EXECUTABLE FILE = 'C:\path\to\my\assembly.dll';
CREATE LOGIN [ClrPermissionsLogin]
FROM ASYMMETRIC KEY [ClrPermissionsKey];
GRANT UNSAFE ASSEMBLY TO [ClrPermissionsLogin];
Once that is in place, you can go to the database where your assembly has been loaded and run:
ALTER ASSEMBLY [MyAssembly] WITH PERMISSION_SET = UNSAFE;
Or you could have included WITH PERMISSION_SET = UNSAFE
at the end of the CREATE ASSEMBLY
command.