How to Disable CLR Strict Security on SQL Server 2017+

New configuration option in SQL Server 2017 called CLR strict security, enabled by default, causes problems when using CLR.

CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. A CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges.

When enabled, it treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE, and any assemblies that are not signed will fail to load.

What is SQL CLR in SQL Server?

The CLR, SQL CLR or SQLCLR is the SQL Common Language Runtime. It is a technology that runs .NET code within an SQL server. The CLR allows managed code (C#, VB.Net) to be hosted and run in the Microsoft SQL Server environment.

Microsoft recommends that a certificate or asymmetric key sign all assemblies with a corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database.

Signing Assemblies

The proper way around this is to install only signed assemblies. Use these steps to sign your own assemblies, or contact your vendor to obtain updated signed assemblies.

Step 1 - Create a certificate

There are a few ways to do this, including the MAKECERT utility, PowerShell or even SQL Server (if you use SQL Server, you must BACKUP CERTIFICATE, including the private key). In the end, the certificate will be sent to both the file system and SQL Server. Once created, you shouldn't ever need to do this again. But it might be a good idea to add the .cer file to the repository.

Step 2 - Password Protect

Use the PVK2PFX utility to combine the .cer and .pvk files into a password-protected .pfx file. Once combined, you shouldn't ever need to do this again.

Step 3 - Signing Assembly

This new .pfx file will be used to sign your assembly DLL using the SignTool utility. This can be automated by adding it as a Post-Build Event. This only needs to be run if the assembly is ever re-compiled, and it is helpful to have this part automated.

Step 4 - Update Build Process

Update the build process such that the deployment has the following being executed before any CREATE ASSEMBLY statements (even if the assemblies are being created as SAFE):

• All three steps are executed in [master]
• If you need the installation compatible with SQL Server versions before 2012, then wrap steps 1 - 3 in an IF block based on the SQL Server "major" version. The two DROP statements, 8 and 9, should be wrapped in an IF EXISTS (or similar) check so they don't need to check for the version (if those objects weren't created due to running on SQL Server 2008, for example, then they won't do anything).
• 1. CREATE CERTIFICATE [InstallationKey] from a VARBINARY literal of the .cer file.
  2. CREATE LOGIN [tmp] from that certificate.
  3. GRANT that cert-based login the UNSAFE ASSEMBLY permission.
  4. CREATE ASSEMBLY [tSQLtExternalAccessKey] ...;
  5. CREATE ASYMMETRIC KEY [tSQLtExternalAccessKey] ...;
  6. CREATE LOGIN from that asymmetric key.
  7. GRANT that key-based login the UNSAFE ASSEMBLY permission.
  8. DROP LOGIN [tmp];
  9. DROP CERTIFICATE [InstallationKey];
  10. DROP ASSEMBLY [tSQLtExternalAccessKey];

This might initially seem complicated, but this is all just a one-time setup.

Disable CLR Strict Security

The strict security option for CLR can be turned off for backward compatibility, but this is not recommended.

-- to disable
EXEC sys.sp_configure N'show advanced options', N'1';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'clr strict security', N'0';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0';
RECONFIGURE WITH OVERRIDE;
GO

EXEC tSQLt.InstallExternalAccessKey;
EXEC master.sys.sp_executesql N'GRANT UNSAFE ASSEMBLY TO [tSQLtExternalAccessKey];';
EXEC sp_configure 'clr strict security', 1; RECONFIGURE;

There should be no need to keep CLR strict security disabled after installing an assembly. So, the following steps should be executed after installation to re-enable strict security for CLR.

-- to re-enable
EXEC sys.sp_configure N'show advanced options', N'1';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'clr strict security', N'1';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0';
RECONFIGURE WITH OVERRIDE;
GO

Why Did Microsoft Change CLR Strict Security?

These changes were made due to the risk of hacking from exploits known as Meltdown and Spectre, which are ways of hacking CPUs. Because this is a hardware fault with the CPU, the only way to solve this (aside from upgrading to a newer, patched processor) is to apply software patches.

All versions of SQL Server are affected, and Microsoft recommends installing these security updates as soon as possible. Based on Microsoft testing of SQL workloads, this should have a negligible-to-minimal performance effect on existing applications. However, Microsoft does recommend that you test all updates before you deploy them to a production environment.

For more information, see this Microsoft KB Article