An important principal of software design is that of “least privilege.” Basically, in any given layer of a program, you should only grant minimal access such that the code has rights to only exactly the resources it needs to get its job done-and nothing more.

Most SQL Server developers understand this concept: one of the main reasons to use stored procedures is to encapsulate permission to data behind controlled and auditable interfaces, thereby not giving the caller direct access.

Bringing the Common Language Runtime (CLR) into SQL Server presents an entirely new set of challenges with regard to privilege and some of the rules that SQL Server developers are used to do not completely translate. Simple grant/deny/revoke logic still applies, but the CLR also brings its own set of specialized permissions, which require slightly different handling in order to properly manipulate.

By not carefully considering these issues when designing your SQLCLR code base, you may be allowing your code too much access.

This article focuses on what the SQLCLR permission sets do to control security and reliability, and what you need to understand when working with them in order to design a system that takes advantage of least privilege. By not carefully considering these issues when designing your SQLCLR code base, you may allow your code too much access, thereby creating potential vectors for security problems down the road.

I will also show you how to work with the permissions system to create fine-grained, reusable utility methods. Creating a core set of well-audited methods will help you not only to reduce your higher-privilege surface area, but also to write less code by reusing your existing logic.

CREATE ASSEMBLY and Permission Buckets

Before you can expose a SQLCLR routine within SQL Server, you must catalog the assembly in which it resides. You do this via SQL Server’s CREATE ASSEMBLY statement. In addition to loading the assembly into SQL Server, the statement allows the DBA to specify one of three code access security “buckets” that dictate what the code in the assembly is allowed to do.

These buckets are SAFE, EXTERNAL_ACCESS, and UNSAFE. SQL Server nests the permissions it grants to each level to include the lower levels’ permissions. The SAFE bucket provides limited access to math and string functions, along with data access to the host database only. EXTERNAL_ACCESS adds the ability to communicate outside of the SQL Server instance. And UNSAFE allows the ability to do pretty much anything you want-including running unmanaged code.

What may not be readily apparent is that each bucket actually controls two distinct methods by which the SQLCLR environment enforces security. Enforcement is done both via Code Access Security (CAS) grants and permissions that are based on a new .NET 2.0 attribute called HostProtectionAttribute (HPA). On the surface, the difference between HPA and CAS is that they are opposites: CAS permissions dictate what an assembly can do, whereas HPA permissions dictate what an assembly cannot do.

But beyond this basic difference is a much more important differentiation: The SQLCLR environment checks CAS grants dynamically at run time by doing a stack walk as code executes, whereas it checks HPA permissions before calling methods in an assembly. To observe what this means, create a new assembly containing the following CLR stored procedure:

[SqlProcedure]
public static void CAS_Exception()
{
    SqlContext.Pipe.Send("Starting...");
    
    using (System.IO.FileStream fs =
        new FileStream(@"c:\b.txt",
        FileMode.Open))
    {
        //Do nothing...
    }
    
    SqlContext.Pipe.Send("Finished...");
    
    return;
}

Catalog the assembly as SAFE and execute the stored procedure. This procedure will result in the following output (truncated for brevity):

Starting...
Msg 6522, Level 16, State 1,
Procedure CAS_Exception, Line 0
A .NET Framework error occurred during
execution of user-defined routine
or aggregate "CAS_Exception":
System.Security.SecurityException:
Request for the permission of type
'System.Security.Permissions.
FileIOPermission,

The exception thrown in this case was a SecurityException, indicating that this was a CAS violation (of the FileIOPermission type). But before it hit the exception, the procedure successfully called the SqlPipe.Send method, referenced in the first line. This is made apparent by the inclusion of the string “Starting…” in the output. Avoiding this particular violation using the SQLCLR security buckets would require cataloging the assembly using the EXTERNAL_ACCESS permission.

To see how HPA exceptions behave, try the same experiment again, this time with the following stored procedure (again, cataloged as SAFE):

[SqlProcedure]
public static void HPA_Exception()
{
  SqlContext.Pipe.Send("Starting...");
    
    lock (SqlContext.Pipe)
    {
        //Do nothing...
    }
    
  SqlContext.Pipe.Send("Finished...");
    
    return;
}

As before an exception occurs, but this time it is a bit different:

Msg 6522, Level 16, State 1, Procedure
HPA_Exception, Line 0
A .NET Framework error occurred during
execution of user-defined routine
or aggregate "HPA_Exception":
System.Security.
  HostProtectionException: Attempted
to perform an operation that was
forbidden by the CLR host.

Unlike when executing the previous stored procedure, this method did not call the SqlPipe.Send method before hitting the exception-the “Sending…” string does not appear in the output. As a matter of fact, the execution context never entered the HPA_Exception method at all. The runtime threw the exception based on reflection done before actually running it. You should also note that the wording of the exception is a bit different: This time the runtime did not deny a request, but rather the code attempted a “forbidden” operation! The SQLCLR environment considers threading and synchronization to be threats to reliability and therefore explicitly disallows them, using the HPA model, for any code cataloged in the SAFE or EXTERNAL_ACCESS buckets.

Generally speaking, CAS grants are all about security-keeping code from being able to access protected resources. On the other hand, HPA permissions are more geared towards reliability and ensuring that SQL Server runs smoothly and efficiently. But both types of permission have the same net effect of controlling what code can and cannot do, and developers can control both to some degree.

A full list of what the SQLCLR environment does and does not allow based on the CAS and HPA models is beyond the scope of this article, but is well-documented. Refer to the following MSDN topics:

When SAFE Just Isn’t Enough

Up to this point, I have not addressed why you should care about permissions behavior when you catalog assemblies as SAFE. The fact is, it’s easy enough to fix these exceptions: Simply raise the permission level of the assemblies to EXTERNAL_ACCESS or UNSAFE and give the code access to do what it needs to do!

Unfortunately, as with most simple workarounds, the fix does not come without cost. In the SQLCLR environment, you grant permissions at the assembly level, rather than the method or line level. Raising the permission of a given assembly can affect many different modules contained within. And granting extra permissions en masse on every module in the assembly creates a maintenance burden: If you want to be certain that there are no security problems, you must review each and every line of code to make sure it’s not doing anything it’s not supposed to do-you can no longer trust the engine to check for you.

Even if it were possible to set permissions at the module level, that may not be granular enough. Consider a complex, 5000-line module, which requires a single file IO operation to read some lines from a text file. By giving the entire module EXTERNAL_ACCESS permissions, you’re creating yet another maintenance nightmare-you now have to check all of the remaining code to make sure it’s not doing anything unauthorized.

The situation gets even worse with threading and synchronization code. A fairly common SQLCLR pattern is to create static collections that can be shared amongst callers. However, properly synchronizing access is important in case you need to update some of the data after the collection has been initially loaded. But due to the fact that threading and synchronization require UNSAFE access, this creates a rather unappealing situation from a permissions point of view.

Is doing a stringent code review every time you make a change enough to ensure that the code won’t cause problems? And do you really want to have to do a full review every time you make a change? Ideally, you want to safeguard the majority of code, which doesn’t do anything that requires a high level of privileges, and yet still allow access to do the occasionally necessary privileged operations.

Enter the Solution: Encapsulation!

The general solution to this situation is to encapsulate higher-privileged operations inside of their own specially granted assemblies and reference those assemblies from SAFE assemblies. Proper implementation of this solution has two key benefits.

First, encapsulation greatly reduces the maintenance issue. Although you still need to do a rigorous code review for the higher-privilege assemblies, overall you’ll review a lot less code. Furthermore, you will not have to do an intensive security review for the SAFE assemblies-the engine will make sure they behave. And if you’re really concerned, you can encapsulate logging within the higher-privileged methods in order to help find out when and if the callers try to do anything strange.

In addition, you can potentially reuse this encapsulation solution. For example, consider the module that needs to read a few lines of a text file. Instead of coding the file system work directly in the module, you can catalog an external assembly that exposes a method that takes a file name as input and returns a collection of lines. Future modules that need to read lines from text files can reference the same assembly, and therefore not have to re-implement this logic. And since you will have already reviewed the assembly, you don’t need to take further action on that front just because a new caller uses it. Reusing modules like this is a common pattern in the world of object-oriented development, and it certainly has its place when working with SQLCLR integration.

As mentioned previously, there are two distinct methods that the runtime uses to enforce code security, both of which it handles differently at run time. As a result, granting lower-privileged code access to protected resources requires slightly different techniques depending on whether the code needs to override HPA or CAS permissions. In the following sections, I’ll go through the encapsulation techniques necessary to make this work.

Encapsulation of HostProtection Privileges

For an example of where you might use the encapsulation technique to get around HPA restrictions, consider a SQLCLR UDF used to do currency conversions based on exchange rates:

[SqlFunction]
public static SqlDecimal GetConvertedAmount(
    SqlDecimal InputAmount,
    SqlString InCurrency,
    SqlString OutCurrency)
{
    //Convert the input amount to
    //the base
    decimal BaseAmount =
       GetRate(InCurrency.Value) *
       InputAmount.Value;
    
    //Return the converted base amount
    return (new SqlDecimal(
       GetRate(OutCurrency.Value) * BaseAmount));
}

This method makes use of another method, GetRate:

private static decimal GetRate(
    string Currency)
{
    decimal theRate;
    rwl.AcquireReaderLock(100);
    
    try
    {
        theRate = rates[Currency];
    }
    finally
    {
        rwl.ReleaseLock();
    }
    
    return (theRate);
}

GetRate performs a simple lookup in a static generic Dictionary<string, decimal> called rates to find the exchange rate for a given currency. Because another thread might be updating the rates concurrently, GetRate handles synchronization using a static instance of ReaderWriterLock, called rwl. Because you’re working in the SQLCLR world and want to keep your assemblies cataloged as SAFE, you must mark both the dictionary and ReaderWriterLock as readonly (this is a SQLCLR restriction):

static readonly Dictionary<string,
                           decimal>
    rates = new Dictionary<string,
                           decimal>();
static readonly ReaderWriterLock
    rwl = new ReaderWriterLock();

Alas, setting the readonly property on the static members is not enough. If you catalog this code as SAFE, it fails due to its use of synchronization (the ReaderWriterLock)-running it produces a HostProtectionException.

Encapsulate higher-privileged operations inside of their own specially granted assemblies and reference those assemblies from SAFE assemblies.

To solve this, move the affected code into its own assembly cataloged as UNSAFE. Because the runtime evaluates the host protection check for each method called, you can mark the outer method SAFE and temporarily escalate its permissions by calling into an UNSAFE core.

Think about what code you should actually move into the core. The Dictionary isn’t causing the problem-the ReaderWriterLock is. But wrapping methods around a ReaderWriterLock does not seem like it would promote very much reuse; at least, not in the scenarios I commonly see. Instead, if you wrap the Dictionary and the ReaderWriterLock together you’ll create a new ThreadSafeDictionary class (Listing 1). I haven’t implemented all of the methods, but you should see enough there to get you started.

You should place this class into an assembly cataloged as UNSAFE. Use a reference to the UNSAFE assembly in the exchange rates conversion assembly, after which you will have to change a few lines of code. First of all, the only static object that you must create is an instance of ThreadSafeDictionary:

static readonly
    ThreadSafeDictionary<string,
                      decimal> rates =
    new ThreadSafeDictionary<string,
                      decimal>();

Secondly, the GetRate method no longer needs to be concerned with synchronization. Lacking this requirement, its code becomes greatly simplified:

private static decimal GetRate(
    string Currency)
{
    return (rates[Currency]);
}

You can still mark the exchange rates conversion assembly SAFE and can use the encapsulated synchronization code without throwing a HostProtectionException. Furthermore, the assembly’s code will not be able to use resources that violate the permissions allowed by the SAFE bucket. This is quite an improvement over the initial implementation, from a security perspective.

Encapsulation of Code Access Security Privileges

Given the ease with which you can encapsulate HPA-protected resources, you might assume that code access permissions are equally straightforward to deal with. Unfortunately, because the runtime checks CAS grants dynamically via a stack walk, you cannot simply reference a second assembly-the runtime walks the entire stack each time, without regard to assembly boundaries.

To illustrate this, create a new assembly containing the following method, which reads all of the lines from a text file and returns them as a collection of strings:

public static string[] ReadFileLines(
    string FilePath)
{
    List<string> theLines = new
    List<string>();
    
    using (System.IO.StreamReader sr =
     new
     System.IO.StreamReader(FilePath))
    {
        string line;
        while ((line = sr.ReadLine())
        != null)
            theLines.Add(line);
    }
    
    return (theLines.ToArray());
}

Catalog the assembly in SQL Server with EXTERNAL_ACCESS permission, and reference it from the assembly that contains the CAS_Exception stored procedure (you should still catalog the assembly as SAFE). Modify that stored procedure as follows:

[SqlProcedure]
public static void CAS_Exception()
{
    SqlContext.Pipe.Send("Starting...");
    
    string[] theLines =
        FileLines.ReadFileLines(@"C:\b.txt");
    
    SqlContext.Pipe.Send("Finished...");
    
    return;
}

Note that I created my ReadFileLines method inside a class called FileLines; reference yours appropriately depending on what class name you used.

Running the modified version of this stored procedure, you’ll find that you receive the same exception as before. The CAS grant did not change simply because you referenced a higher-privileged assembly, due to the fact that the stack walk does not take into account referenced assembly permissions.

Working around this issue requires taking control of the stack walk within the referenced assembly. Due to the fact that the assembly has enough privilege to do file operations, it can internally demand that the stack walk ignore those operations, even when you call it from another assembly that does not have the requisite permissions. To do this, use the Assert method of the IStackWalk interface, exposed in .NET’s System.Security namespace.

Take a second look at the CAS violation and you’ll note that the required permission is FileIOPermission, which is in the System.Security.Permissions namespace. The FileIOPermission class happens to implement the IStackWalk interface. To solve this particular encapsulation problem, instantiate an instance of the FileIOPermission class and call the Assert method.

Listing 2 shows a modified version of ReadFileLines that uses the FileIOPermission class to enable all callers to do whatever file I/O-related activities that the assembly has permission to do. Note that the “unrestricted” permission only allows the same amount of unrestricted access as the assembly could get anyway. There are also other overloads for the FileIOPermission constructor that you can use to control things in a more granular level. For instance, you can pass an enumeration called FileIOPermissionAccess to some of the overloads to do things like allow read access to only a specific file.

File I/O is only one kind of permission from which you might see a CAS exception. The System.Security.Permissions namespace contains several classes that deal with various types of code access. You can determine which class you need to use by looking at the exception and seeing which permission the runtime denied. In every case, you can use the same basic pattern I’ve outlined here to perform the required encapsulation.

Better “Safe” Than…

SQLCLR integration in SQL Server 2005 brings several ideas to the table that SQL Server developers must fully understand in order to effectively work within the new environment. Permissions are but one class of these issues, but probably the most important.

The idea of encapsulation should be nothing new to SQL Server or .NET developers, and the methods I’ve presented here are just another way that you can apply encapsulation. Although the focus is on working towards least privilege, the potential for greater reuse is a definite bonus that you should not overlook. By putting in a bit of time and effort to learn how to work with the permissions system instead of against it, you can create more secure, robust code with much less exposed surface area-and that is always better than being sorry later!

I would like to acknowledge Bob Beauchemin of SQL Skills and Steven Hemingray of Microsoft, both of whom helped me through a few of the rough spots I encountered while researching this article. Thank you both for your help!