Security or performance risks using SQL CLR

Posted on

Question :

Are there any particular security or performance risks in using the CLR in SQL Server ?

Answer :

The question, as Remus pointed out, is too generic to get an answer as the answer depends on the context of what functionality is to be used and how will it be used.

Regarding “Security”:

If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = SAFE, then there aren’t any issues that I have ever been able to find. And SQLCLR is “safer” than using xp_cmdshell or the wonderful (that was sarcasm) sp_OA* procs (or even Extended Stored Procedures, but hopefully nobody is creating any of those anymore).

If you would like a walk-through of what “SAFE” means in practical terms, please see this article: Stairway to SQLCLR Level 3: Security (General and SAFE Assemblies) (free registration required).

If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = EXTERNAL_ACCESS, then there are certainly risks, again, depending on what functionality is being used. If you write a routine to read directories and file names (i.e. read-only), then it is just a matter of what should be seen and not seen. If you are writing code that allows for deleting a file, the risk increases. But what can be done with those external resources is controlled by:

  • whether or not you are using Impersonation:
    • no Impersonation means accessing external resources is done via the “Log on as” account of the SQL Server service. Whatever that account has access to, your SQLCLR function will be able to do.
    • using Impersonation means that the Login in SQL Server that is running the function, if that Login corresponds to a Windows Login, can do whatever that particular Windows Login is allowed to do. If the Login in SQL Server is a SQL Server Login, then attempting to use Impersonation will get an error.
  • What permissions are set up on the external resource. For filesystem access, this is controlled through ACLs on NTFS drives.

If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = UNSAFE, that is fairly open-ended. A lot of functionality is deemed only usable in UNSAFE assemblies because they are issues of stability and/or consistent behavior more than security or performance. For example, in an UNSAFE assembly it is possible to have a writable static variable. This is typically not a good thing to do since the SQLCLR classes are shared across all sessions. If your intention is to share data in memory across all sessions and have planned for race conditions (and done plenty of testing), then you should be fine as you are expecting this behavior. But if you simply wanted a writable static variable to cache a value for a particular session to not have to look it up again or calculate it again, and were not aware that other sessions are reading that value and possibly over-writing it, well, that would be an issue.

But if you are worried about someone writing to the Registry, but yet don’t have any code that actually writes to the Registry, then you probably don’t need to worry about this ;-).

If you would like a walk-through of how EXTERNAL_ACCESS and UNSAFE work in practical terms, and the difference between setting TRUSTWORTHY ON (not preferred) vs using an Asymmetric Key- or Certificate- based Login, please see this article: Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies) (free registration required).

Regarding “Performance”:

This is all a matter of what you are trying to do and how you go about doing it. There are some things that are much faster in SQLCLR, and some things that are slower. But just like with T-SQL, it is possible to take a somewhat simple and/or efficient task and make it complicated and/or inefficient by doing things incorrectly. But using SQL CLR is not inherently, by its very nature, slower.

SQLCLR assemblies can be installed with three levels of security access: SAFE | EXTERNAL_ACCESS | UNSAFE. This is amply documented, refer to CREATE ASSEMBLY and Designing Assemblies:

Managing Assembly Security
You can control how much an assembly can access resources protected by .NET Code Access Security when it runs managed code. You do this by specifying one of three permission sets when you create or modify an assembly: SAFE, EXTERNAL_ACCESS, or UNSAFE.

SAFE is the default permission set and it is the most restrictive. Code run by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry. SAFE code can access data from the local SQL Server databases or perform computations and business logic that do not involve accessing resources outside the local databases.
Most assemblies perform computation and data management tasks without having to access resources outside SQL Server. Therefore, we recommend SAFE as the assembly permission set.

EXTERNAL_ACCESS allows for assemblies to access certain external system resources such as files, networks, Web services, environmental variables, and the registry. Only SQL Server logins with EXTERNAL ACCESS permissions can create EXTERNAL_ACCESS assemblies.
SAFE and EXTERNAL_ACCESS assemblies can contain only code that is verifiably type-safe. This means that these assemblies can only access classes through well-defined entry points that are valid for the type definition. Therefore, they cannot arbitrarily access memory buffers not owned by the code. Additionally, they cannot perform operations that might have an adverse effect on the robustness of the SQL Server process.

UNSAFE gives assemblies unrestricted access to resources, both within and outside SQL Server. Code that is running from within an UNSAFE assembly can call unmanaged code.
Also, specifying UNSAFE allows for the code in the assembly to perform operations that are considered type-unsafe by the CLR verifier. These operations can potentially access memory buffers in the SQL Server process space in an uncontrolled manner. UNSAFE assemblies can also potentially subvert the security system of either SQL Server or the common language runtime. UNSAFE permissions should be granted only to highly trusted assemblies by experienced developers or administrators. Only members of the sysadmin fixed server role can create UNSAFE assemblies.

There are further restrictions on allowed CLR attributes and only a subset of the .Net Framework Assemblies are supported. Again, refer to the linked documentation.

As for performance, the most important think is to remember that SQL Server is a cooperative multi-tasking environment, while CLR is not. The SQLCLR code must call Thread.BeginThreadAffinity() anytime it hogs the CPU for any duration (including blocking). Adam Machanic has an excellent presentation on the topic, watch Data, Faster: Microsoft SQL Server Performance Techniques with SQLCLR.

The topic is vast and the question vague. SQLCLR can perform some unique tasks no other feature can match. And SQLCLR is just another weapon in the SQL Server arsenal you can shoot yourself in the foot with, performance or security. Read the documentation.

Leave a Reply

Your email address will not be published. Required fields are marked *