SQL Server Security: Best Practices with CLR

What is SQL CLR?

CLR (The Common Language Runtime) is a component of .NET framework that allows SQL Developers to use some .net functionality inside of their SQL Server code. A feature that makes the relational purists freak out (or rollover in their graves, Codd Bless you) – but can extend the functionality of SQL code. You’ll find it in use in applications purchased off the shelf and in code your own developers write. It’s allowing SQL Server to execute managed code in the database, though. That’s not “free” from a performance POV or a security POV.

We’ll focus on a few thoughts about the security POV here.

When you don’t actually need SQL CLR

When you don’t actually need to use CLR in your SQL Server? Just disable it. A good best practice for SQL Server security is to only enable the features and services you actually require. Keep the rest disabled or uninstalled – make your users and developers and vendors request the feature to be turned on. Then you can run through a series of questions and answers to see if it’s even needed or if you can work around it. So, when possible, disable it!

EXEC sp_configure 'clr enabled', 0;
RECONFIGURE;

But – don’t stop here. Disabling CLR won’t impact CLR objects already in existence, it just prevents CLR objects from being created or modified. You’ll also find, if you start exploring the DMVs about CLR that there are several CLR objects in existence out of the box courtesy of Microsoft.

Security Best Practices when you are using CLR in SQL Server – keep it safe.

To be clear – this applies whether you disable CLR or not. You should still check to make sure that existing CLR code is “safe” and that you are following all of the security best practices we’ve blogged about in the SQL Server security blog series. You should make sure you have an understanding of what the CLR is doing and in what context it runs.

Using SAFE assemblies is generally speaking a best practice and you can find all of the assemblies which aren’t “SAFE” and see what is going on with them.

USE MASTER;

SELECT *
FROM sys.assemblies
WHERE permission_set_desc != 'SAFE'
AND is_user_defined = 1;

Review those and see why they are created and marked as something other than safe. Ideally, they should be altered to be unsafe or external permissions (“our assembly” here is called “TestAssmbly”):

EXTERNAL_ACCESS means you need to access resources out of the SQL Server instance (files/network/etc). When you use this – the code will execute impersonating the context of the user calling the code.

UNSAFE means this is not verifiably safe and it needs external resources and you are basically throwing in the towel on security.

SAFE tells SQL Server that that assembly doesn’t need external resources and is the ideal setting to use here.

A full list of permissions granted by the permission levels is found in this article from Microsoft.

Really verify those external accesses

If someone truly needs an external access and it can’t be done another way and you are following best practices like locking down the SQL Server service account, not having an SA privileged user own the database, not having the DB marked as trustworthy and keeping the surface area locked down on the SQL server – it sort of is what it is. It’s needed.

SQL Server 2017 and higher: use CLR strict security

If you are on SQL Server 2017 or higher, there is a configuration option that is enabled by default called “CLR strict security” – Microsoft explains this in this article thusly:

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. Beginning with SQL Server 2017 (14.x), an sp_configure option called clr strict security is introduced to enhance the security of CLR assemblies. clr strict security is enabled by default, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. The clr strict security option can be disabled for backward compatibility, but this is not recommended. 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. SQL Server administrators can also add assemblies to a list of assemblies, which the Database Engine should trust. For more information, see sys.sp_add_trusted_assembly.

In short – some of the permissions and best practices around CLR get a little hazy with various .net updates and SQL versions. You want to find the unsafe assemblies in use and external_access and figure out what they are doing. And you may need to dig deeper and start looking at certificates and other permissions when you need CLR. Ideally, I’d love to just say “To be most safe with CLR in SQL Server, just don’t” – and I still mostly feel that way – but in the real world needs exist or at least are created. Don’t just accept the CLR without verifying and understanding what you are allowing.