How to make existing assemblies on SQL server older than 2017 work with 2017

1

I have many assemblies on SQL server 2008, that i don't have their dll, so when i'm trying to create those assemblies on SQL 2017 an error occors, "Msg 10343, Level 14, State 1, Line 106
CREATE or ALTER ASSEMBLY for assembly 'SqlRegularExpression' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly." reading about possible solutions i have found that we can use the following options:
1- Disable CLR Strict Security feature (not recommended by microsoft)
2- Enable Database Trustworthy feature (not recommended )
3- Sign CLR Assembly with Asymmetric Key (recommended but the source is required and we haven't it)
4- Sign CLR Assembly with Certificate Key (recommended but dll is required and not exists)
*So Any expert can help us to sign assemblies stored in database? *

1 Like
2

Hello. Fortunately, solving this is fairly easy. All you need to do is the following steps:

If an unsigned Assembly needs to be loaded into SQL Server:

  1. TEMPORARILY enable TRUSTWORTHY for the Database where you are loading the Assembly (using ALTER DATABASE)
  2. Create the Assembly (using CREATE ASSEMBLY ; this step requires that the Login for the Database Owner has the UNSAFE ASSEMBLY permission, which it will already have if the DB is owned by sa or another sysadmin)
  3. Disable TRUSTWORTHY for this Database

Assuming that one or more unsigned Assemblies already exist:

  1. Create a Certificate in the Database where the Assembly resides (using CREATE CERTIFICATE)
  2. Sign the Assembly (or Assemblies) with the Certificate (using ADD SIGNATURE)
  3. Copy Certificate (Public Key only!) to [master] (using CERTENCODED(), CERT_ID(), and CREATE CERTIFICATE)
  4. Create Login from Certificate (using CREATE LOGIN)
  5. Grant that Login the UNSAFE ASSEMBLY permission (using GRANT)

For example:

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 full explanation of the above steps can be found in the following post of mine:
SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment ( sqlquantumleap．com/2017/08/28/sqlclr-vs-sql-server-2017-part-4-trusted-assemblies-the-disappointment ). In fact, I have a demo script on PasteBin that does all 8 of the steps noted above: pastebin．com/mwi5BidL

And, since you are using SQLCLR to get Regular Expression (RegEx) functions, I should mention that the quickest and easiest way to get quite a few RegEx functions is to get SQL# ( SQLsharp．com ), a SQLCLR library (that I wrote) with over 300 functions. All of the assemblies are signed, TRUSTWORTHY is not enabled, and clr strict security is not disabled.

Take care,
Solomon...
SqlQuantumLift．com
SqlQuantumLeap．com
SQLsharp．com