Learning never exhausts the mind

Last Updated by

There is a new configuration option in SQL Server 2017 called CLR strict security, it is enabled by default, and can cause 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.

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

Signing Assemblies

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

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 will need to BACKUP CERTIFICATE including the private key). In the end, the certificate will be in both places: 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 rather easily by adding it as a Post-Build Event. This only needs to be run if the assembly is ever re-compiled and is useful 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 3 steps are executed in [master]
  • If you need the installation to be compatible for SQL Server versions prior to 2012, then wrap steps 1 - 3 in an IF block based on SQL Server "major" version. The two DROP statements, 8 and 9, should be wrapped in an IF EXISTS (or similar) check so those 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 just 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];

All of this might seem complicated at first, but this is all just a one-time setup.

Disable CLR strict security

The CLR strict security option can be disabled 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 should be executed post-install to re-enable CLR strict security.

-- 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 This?

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 itself, 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 a matter of urgency. This should have a negligible-to-minimal performance effect on existing applications, based on Microsoft testing of SQL workloads. 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

Leave a Reply

Fields marked with * are mandatory.

We respect your privacy, and will not make your email public. Hashed email address may be checked against Gravatar service to retrieve avatars. This site uses Akismet to reduce spam. Learn how your comment data is processed.