Security risks of setting trustworthy = on in sql server 2012

Question

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?

Solution

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:

• PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining
• Guidelines for using the TRUSTWORTHY database setting in SQL Server
• Extending Database Impersonation by Using EXECUTE AS
  This is a very informative document that covers most aspects of this topic, and is also referenced in the linked page above.
• Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies)
  This is an article I wrote as part of a series on SQLCLR that has examples which illustrate the differences between the TRUSTWORTHY method and the Signed Assembly-based Login method; Free registration is required.

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.

Comments

[cdub]

They actually are signed with an asymmetric key and set to unsafe but still get that error. not sure why

[Adir D]

+1 for offering a valid workaround, but you haven't really addressed the real question (specific and explicit risks about TRUSTWORTHY in general).

[Solomon Rutzky]

@chris Are you sure that the assembly is set to UNSAFE? The error you posted is what you get when the assembly is not set to unsafe. Or, when it was set to UNSAFE but then the Login was removed or at least had the UNSAFE ASSEMBLY permission removed. If any of that is not the case, then what framework methods are you calling in your proc method?

[Solomon Rutzky]

@AaronBertrand : Thanks. And I have now updated the answer to provide some details and links to more info regarding implications of TRUSTWORTHY.