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):
- Create a Certificate in the Database where the Assembly resides
- Back up the Certificate (both Public and Private Keys) to files (optional)
- Sign the Assembly (or Assemblies) with the Certificate
- Remove the Private Key (it can be restored later from the backup file) (optional)
- For each additional Database that has unsigned,
SAFE Assemblies:
- 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
- Do the following (this will result in one Certificate total, which will be used for all Databases):
- Create the same Certificate from the backup files (including the Private Key!)
- Sign the Assembly (or Assemblies) with the Certificate
- Remove the Private Key (it can be restored later from the backup file)
- Copy Certificate to
[master] (Public Key only!)
- Create Login from Certificate
- 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
