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
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.
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).
- CREATE CERTIFICATE [InstallationKey] from a VARBINARY literal of the .cer file.
- CREATE LOGIN [tmp] from that certificate.
- GRANT that cert-based login the UNSAFE ASSEMBLY permission.
- CREATE ASSEMBLY [tSQLtExternalAccessKey] ...;
- CREATE ASYMMETRIC KEY [tSQLtExternalAccessKey] ...;
- CREATE LOGIN from that asymmetric key.
- GRANT that key-based login the UNSAFE ASSEMBLY permission.
- DROP LOGIN [tmp];
- DROP CERTIFICATE [InstallationKey];
- 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