3

I have a pre-SQL Server 2017 database that I restored into / upgraded to SQL Server 2017. In this database I have a SQLCLR Assembly. The Assembly is marked as SAFE since it doesn't do anything requiring a higher level of permissions, and the database has TRUSTWORTHY disabled / OFF. The SQLCLR functions and stored procedures worked as expected before moving to SQL Server 2017, but now when I try to execute any of them I get the following error:

Msg 10314, Level 16, State 11, Server XXXXXXXXXXX, Line YYYYYY
      An error occurred in the Microsoft .NET Framework while trying to load assembly id ZZZZZ. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
      System.IO.FileLoadException: Could not load file or assembly '{assembly_name}, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

I have confirmed that CLR Integration / SQLCLR has been enabled on the server / instance.

1 Answer 1

6

The error is a result of the new clr strict security server-level configuration option in SQL Server 2017. This new security setting disallows any Assembly, even those marked as SAFE, from being either created or loaded into memory for execution, unless:

  • the Database property of TRUSTWORTHY is ON (don't do this as it is an unnecessary security risk) and the Login that is the database owner (i.e. same SID used by the [dbo] User) needs to have the UNSAFE ASSEMBLY permission (which it might already have if the owner is sa or a member of the sysadmin fixed server role or maybe has the CONTROL SERVER permission).
  • the Server-level configuration option of clr strict security is disabled / 0 (don't do this as it is an unnecessary security risk)
  • the Assembly is signed and you have a corresponding signature-based Login that has been granted the UNSAFE ASSEMBLY permission (please do this)
  • the SHA-512 hash of the Assembly is registered as a "Trusted Assembly" (absolutely never do this as there are numerous problems with it, not to mention that it was never needed in the first place)

All you need to do in order to fix this situation are the following steps (which are not difficult and provide the highest level of security):

  1. Create a Certificate in the Database where the Assembly resides
  2. Back up the Certificate (both Public and Private Keys) to files (optional)
  3. Sign the Assembly (or Assemblies) with the Certificate
  4. Remove the Private Key (it can be restored later from the backup file) (optional)
  5. For each additional Database that has unsigned, SAFE Assemblies:
    1. Either skip this sub-step (i.e. skip to Step 6), complete the steps, and repeat the whole process (this will result in one Certificate per each Database needing one), OR
    2. Do the following (this will result in one Certificate total, which will be used for all Databases):
      1. Create the same Certificate from the backup files (including the Private Key!)
      2. Sign the Assembly (or Assemblies) with the Certificate
      3. Remove the Private Key (it can be restored later from the backup file)
  6. Copy Certificate to [master] (Public Key only!)
  7. Create Login from Certificate
  8. Grant the Certificate-based Login the UNSAFE ASSEMBLY permission

A simple example of this (minus the optional backup and Private Key removal) would be:

USE [{database_containing_unsigned_safe_assembly}];

CREATE CERTIFICATE [{certificate_name}]
  ENCRYPTION BY PASSWORD = '{some password}'
  WITH SUBJECT = '{simple description}',
  EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
  TO Assembly::[{assembly_name}]
  BY CERTIFICATE [{certificate_name}]
  WITH PASSWORD = '{some password}';

DECLARE @PublicKey VARBINARY(MAX),
        @SQL NVARCHAR(MAX);

SET @PublicKey = CERTENCODED(CERT_ID(N'{certificate_name}'));

SET @SQL = N'
CREATE CERTIFICATE [{certificate_name}]
  FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @PublicKey, 1) + N';';
PRINT @SQL; -- DEBUG

EXEC [master].[sys].[sp_executesql] @SQL;

EXEC [master].[sys].[sp_executesql] N'
CREATE LOGIN [{login_name}]
  FROM CERTIFICATE [{certificate_name}];

GRANT UNSAFE ASSEMBLY TO [{login_name}]; -- REQUIRED!!!!
';

A fully working example of this (minus the backup and Private Key removal) can be found on PasteBin:
Avoiding "Trusted Assemblies" - Demo

A detailed explanation of why you should use Certificates, and why you should not use "Trusted Assemblies", to fix this problem is provided in my blog post:
SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment

Also, please consider supporting my request to have Microsoft remove the new "Trusted Assemblies" feature given the numerous problems surrounding it (including security concerns), and the overall lack of benefits for having it:

Trusted Assemblies are more problematic yet less functional than Certificates - Please Remove

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.