One of the major benefits of writing .NET code to run in the Common Language Runtime (CLR) hosted in any environment is code access security (CAS).

CAS provides a code-based-rather than user-based-authorization scheme to prevent various kinds of luring and other code attacks. But how does that security scheme coexist with SQL Server 2005’s own, newly enhanced security features? By default your .NET code is reasonably secure, but it’s all too easy for the two security schemes to butt heads and cause you grief. In this article I’ll look briefly at the concept behind CAS and a few new security features in SQL Server 2005, then explore how to make the two systems work for you instead of against you as you take advantage of these advanced programming features in SQL Server.

The ability to write stored procedures and other code modules in C#, VB, or any other .NET language has long been one of the most enticing features in what was then the upcoming SQL Server 2005. Both developers and DBAs are finally able to break the shackles of Transact-SQL (T-SQL) and C++ in extended stored procedures, and write database code in a real, productive language!

SQLCLR code can’t do anything more in a database than an equivalent T-SQL code module running under the same security context.

At the same time, the prospect of running .NET code within the database server’s memory space scared some people to death, notably some DBAs who are responsible for protecting the integrity of data and make sure that the server stays up and running as close to 24/7 as possible. The thought of running some developer’s code, code that has full access to the .NET Framework and the Win32 API, caused many a DBA to swear that such code will run in the server over his or her dead body.

I speak at conferences and do a lot of training, and I’ve asked both students and clients whether .NET code in the server scares them and why. Here are a few typical concerns:

• Vague security issues, mostly dealing with an increased surface area for attacks, but apparently more a nervousness about what is new and not yet understood
• Needing to learn a whole new set of skills in order to be able to assess whether code is safe and secure
• A blurring between data and code, particularly with the new ability to create user-defined types in .NET code
• Yet another way that code can mess up the server, even though the OLE automation (SP_OS*) and command shell system (xp_cmdshell) stored procedures have long been available

As a practical matter, .NET Framework code in SQL Server 2005, often referred to as SQLCLR code because it’s based on the .NET Common Language Runtime (CLR), is just another code module that exists and runs within SQL Server. It’s new and it’s cool, but it’s just code. It isn’t a plug-in replacement for T-SQL, which is still best for data access code. But SQLCLR code opens up whole new possibilities for sophisticated database applications. Sooner or later most every DBA will be faced with a compelling use for it and will have to make a final decision about whether to let it into the database.

In this article, I’ll explore one of the biggest concerns about SQLCLR code: just how secure is it? Actually, I’ll deliberately blur two important considerations-security and reliability. Security means keeping data safe and reliability means keeping SQL Server safe; reliability is often confused with security. So while my main focus here is on security, I’ll talk a little about reliability as well.

I’ll assume that you are familiar with the benefits and basics of writing .NET code in SQL Server 2005, including these concepts and topics:

• Assemblies as the unit of packaging, deployment, and versioning
• .NET code access security basics
• The new security features in SQL Server 2005

In other words, this is not an introductory article on SQLCLR code. See the Resources sidebar to explore these basic concepts in more depth.

Securely Hosting SQLCLR Code

With the introduction of SQLCLR code, SQL Server 2005 now supports two entirely different runtime environments: good old reliable T-SQL and the new upstart SQLCLR. T-SQL has grown up with SQL Server over the years and is closely integrated with the data and objects stored in a database, tied neatly with the security systems in SQL Server. SQLCLR code, in stark contrast, internally uses a completely different security system provided by the CLR, a warm, safe, fuzzy environment in which code runs not with the security credentials of the user who runs it but credentials based on the characteristics of the code itself. At the same time, SQLCLR code has to execute within the security confines of the database and server. These two security systems are fundamentally different, and the SQL Sever team at Microsoft had to figure out a way to make them both coexist and work together.

Sooner or later most every DBA will be faced with a compelling use for SQLCLR and will have to make a final decision about whether to let it into the database.

The ability to host the CLR within another application reliably and securely is a new feature of version 2.0 of the .NET Framework. This hosting environment, and SQL Server’s implementation of it, is the magic that makes the two security systems peacefully coexist, since the host-here SQL Server-can exert a great deal of control over the code that runs. This means that from a security perspective managed SQLCLR code isn’t allowed to access database objects it isn’t authorized for. The code must run under the SQL Server security context of the user session that invoked it with the associated permissions, same as T-SQL code.

The bottom line: SQLCLR code can’t do anything more in a database than an equivalent T-SQL code module running under the same security context. This fact alone should take the edge off of any DBAs nervousness!

Microsoft had three primary design goals when designing how to host the CLR:

• The CLR and the code running within it cannot compromise the security and stability of SQL Server.
• SQLCLR code must follow SQL Server authentication and authorization rules, which in part means that it runs under security context of the user session.
• Admins must be able to control access to operating system resources. This means that there must be a secure way to access machine resources from within the SQL Server process.

One of the clearest manifestations of these goals is that CLR integration is turned off by default. If you want to run .NET code in a database an administrator has to turn it on. The T-SQL code to turn it on requires using sp_configure:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

You can also use the new Surface Area Configuration tool installed with SQL Server 2005, as shown in Figure 1. Start the tool from the Windows Start menu, selecting Microsoft SQL Server 2005, Configuration Tools, and SQL Server Surface Area Configuration. Select Surface Area Configuration for Features, then select CLR Integration from the list of features.

Figure 1: Enabling SQLCLR using the Surface Area Configuration tool.

It is important to understand what turning the CLR Integration feature on and off does. The only thing it affects is whether SQLCLR code will run. If it’s off, no SQLCLR code will execute in that server instance; if it’s on, any code can execute (assuming, of course, the user has the proper execution permission). If it is off, it does not prevent you from installing SQLCLR assemblies into the database. You can install all the assemblies you want (assuming, of course, you have the property permissions to do so) but they won’t run under any circumstances until you enable CLR integration.

When SQLCLR code executes, it is within a rigid security environment, one layer among many that protects both operating system resources as well as data and objects within SQL Server.

Figure 2 shows these layers of security. The operating system imposes its own control, using the familiar user and group paradigm for granting access to resources that are decorated with Access Control Lists (ACLs). Every application running in Windows needs to run within a login’s security context that has proper permissions to access the resource. Even SQL Server has to operate within this framework.

Figure 2: SQLCLR code doesn’t run in a security vacuum. It is the low man on the security totem pole.

SQL Server controls the security of its own environment, using logins either of its own making or mapped to operating system logins. Within its environment, it grants or denies access to data and objects based on permissions assigned by the object’s owner or an administrator. T-SQL operates within this permission scheme as a kind of final arbiter of who can access what. SQLCLR code executes within the same security environment as T-SQL code, but also executes within its own security environment provided by the CLR. The CLR implements Code Access Security (CAS) to grant its own permissions to running code. I’ll get into more details about CAS in a few paragraphs, after I cover a couple more security details about the SQL Server host environment.

SQL Server-Level Security

SQL Server 2005 has implemented a far more granular permissions scheme than earlier versions of SQL Server. This granularity extends to SQLCLR code, for which there are three primary permissions required to install and run the code.

• The CREATE ASSEMBLY permission is required to run the CREATE ASSEMBLY statement that installs a SQLCLR assembly into a database.
• A non-admin must have the EXECUTE permission on a code module in order to run the code. A sysadmin can run any code.
• The owner of the code must have the REFERENCES permission to reference other objects, such as with foreign keys and create view with schemabinding. (This permission is not required to run code located within the same .NET assembly.)

Beyond these permissions, the user or login that causes SQLCLR code to execute must have the usual SELECT, INSERT, DELETE, or UPDATE permissions on tables referenced within the code. There is nothing in how the SQLCLR is hosted within SQL Server 2005 that circumvents the need for these permissions, depending on what the code does to the data in the table. Further, the permission checks hook into the new execution context feature in SQL Server 2005, so that when defining a SQLCLR stored procedure or function you can specify the execution context of the code using the EXECUTE AS clause.

CLR Security

Up to this point in the article I’ve been talking about the security environment of .NET code hosted and executing within SQL Server, from the perspective of SQL Server looking in at the SQLCLR code module. But the CLR imposes its own security model. This model kicks in once SQL Server is happy with all the permissions checking and allows the code to execute. Just because it can execute doesn’t mean that it can do anything it wants.

The CLR provides a number of services to the .NET code it runs as well as to the host in which it is operating. These include type safety checking to validate that the code accesses memory structures in well-defined ways, role-based security based on who is running the code, code access security under which permissions are granted based on code characteristics rather than just on who is running the code, and application domains that provide safe execution zones within the host process.

All assemblies that have the same owner in the database are loaded into the same AppDomain, no matter which database they are installed in. Each assembly in an AppDomain can discover each other through reflection. Since they have the same owner, SQL Server doesn’t have to perform its own permission checking, which helps performance. But these measures don’t circumvent code access security, which remains in effect.

The CLR also enforces host protection attributes (HPA) that allow a host-SQL Server in this case-to control what parts of the .NET Framework the SQLCLR code is allowed to use. These are more in the realm of reliability rather than security; see the sidebar, Reliability through Host Protection Attributes, for a description of these attributes.

Code Access Security

One of the most important services provided by the CLR is code access security (CAS). The fundamental principle of CAS is that privileges are granted to code, not to users. This may seem strange if you’re used to the Windows or SQL Server scheme of assigning permissions to users and logins rather than code they are executing. But even if SQLCLR code is executing in the security context of an administrative user, it may not have the full range of available permissions. In fact, SQLCLR code executing within SQL Server almost certainly will not have all permissions, what is called full trust.

Here are the basics of how CAS works. When loading an assembly in response to the invocation of a SQLCLR stored procedure, function, or other code module, the CLR gathers evidence. It uses that evidence to assign the assembly to one or more code groups. The assigned code groups each has a permission set assigned through a runtime security policy that uses membership conditions to determine where code is assigned. A permission conveys a right to do something that is protected. Code demands that callers have certain permissions to perform the privileged action.

If these are foreign concepts to you, you’ll need to bone up on this important part of developing secure applications. Understanding CAS is critical for understanding the permissions SQLCLR code has when executing.

So how does SQL Server bring together the SQL Server and CLR security environments? The first thing to understand is that there are two sets of resources being protected by these systems. The first set consists of SQL Server objects and data. SQL Server’s security environment protects access to its own objects, even for SQLCLR code it hosts.

The CLR protects access to everything else.

What is everything else? It is the resources outside of the SQL Server instance, including disk files, registry settings, other databases, network resources, and Web services. Anything and everything that exists outside of the SQL Server instance where the code is running. This means that CAS does nothing at all to protect anything at all within its host SQL Server instance.

Pause here a moment and let that sink in. Having a clear understanding of which security system protects what is critical for what is coming, so here is another way to state the same thing: The permissions granted in SQL Server protect all of its data and objects from any kind of executing code, whether the code is written in T-SQL or SQLCLR. The CLR’s CAS protects access to all resources outside of SQL Server.

A corollary to this then is that CAS does nothing to protect a SQL Server instance’s objects or data. Not a bloody thing.

Got that? Now I’ll flesh out a few more details about CAS. But keep in mind that the permissions I’m talking about now are not those within SQL Server but are out in the operating system. For example, say that SQLCLR code has to open a disk file to record some logging data, or to make a connection to read data from another database. CAS permissions restricts how the code can access that disk file and the connection to the other database.

Whenever the CLR loads an assembly in order to run some method, it gathers evidence about the assembly that it matches with policy defined on the machine to grant permissions. Evidence for .NET assemblies typically involves both location/origin data-where the assembly is being run from-and identity data. But since a SQLCLR assembly runs from within SQL Server, location evidence is not relevant. That leaves identity evidence, things such as whether the assembly has a strong name or is digitally signed by a particular company.

The CLR takes that evidence and matches it up against four levels of policy: Enterprise, Machine, User, and AppDomain. (The SQL Server documentation often calls the AppDomain level “Host Policy,” but it’s the same thing. AppDomain is the more typical term in the .NET Framework and is the one I use.) The actual set of permissions granted to an assembly by the CLR is the intersection of the permissions granted at each level.

Figure 3 shows how this works. Each of the four levels has its own permission set. To determine the set of permissions granted to an assembly, the CLR takes the intersection of permissions-that is, the common set of permissions, each of which is present in all four permission sets-and grants that intersect set to the assembly.

Figure 3: Intersection of CAS permissions from four policy levels

You can examine the first three policy levels-enterprise, machine, and user-using the .NET Framework 2.0 Configuration tool available from the Administration Tools applet in Control Panel on a machine with the framework installed. Figure 4 shows the tool when you’ve expanded the Runtime Security Policy section of the treeview to show the policy levels.

Figure 4: .NET Framework 2.0 Configuration Tool’s policy levels.

It is here that a user or administrator can modify the default policy for the levels shown so that an assembly gets more or fewer permissions when it loads. This can be a rather complex subject, but for SQLCLR code-in fact, all .NET code that is installed on the local machine-these three policy levels by default assign full trust to an assembly. Full trust simply means that the code automatically has every possible permission. More precisely, it means that the CLR doesn’t do any permission checking whatsoever.

Huh? If the assembly by default has permissions checking short circuited, why did I make you read all this stuff about CAS? Shouldn’t I have saved you the pain and gotten right to the good stuff?

The reason is that there are four policy levels used by the CLR to assign permissions, but only three are configured using the tool shown in Figure 4. The fourth is the AppDomain level that is created when you install an assembly into a database. It is the policy level that is controlled by SQL Server as the CLR host. And SQL Server will rarely grant an assembly full trust because it can be an extreme risk to both security and reliability.

Figure 5 shows the situation as it actually happens with SQLCLR code by default. (Keep in mind that a user or administrator can change the policy settings of the enterprise, machine, and user levels, in which case the situation is more like that in Figure 3.) Because the enterprise, machine, and user policy levels grant full trust, they are the same resulting permission set: all permissions. That permission set is then intersected with the AppDomain permission set to determine the final intersect set that becomes the permissions for the assembly.

Figure 5: The actual CAS permission set in SQLCLR code.

SQLCLR Permission Set Levels

SQL Server provides three permission set levels when you load an assembly into a database using the CREATE ASSEMBLY statement: SAFE, EXTERNAL_ACCESS, and UNSAFE. These are the permission sets that form the AppDomain policy level as shown in Figure 3 and Figure 5.

Here is a typical statement that installs an assembly within the FileLoader.dll file and gives it the EXTERNAL_ACCESS permission set.

CREATE ASSEMBLY FileAccess
FROM       'E:\FileLoader.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

Each of the three permission set levels confers a different set of CAS permissions on the code when it executes. I’m indebted to Nicole Calinoiu, my fellow Visual Developer - Security Microsoft MVP, for the explorations that enabled me to flesh out some of the detail in the following discussion about specific permissions granted at each level. You can read more detail about what she discovered about SQLCLR permission sets in her account in the Resources sidebar.

SAFE

SAFE is the default permission set. It grants just enough permissions to execute the code, do internal computations that don’t access external resources, and access the data and objects within the host SQL Server instance. SAFE code cannot access external resources, so it can’t read or write disk files, can’t access any other SQL Server instances, or read or write to the registry. The code must also be verifiably type safe, which helps protect against various attacks including buffer overruns.

SAFE code is the most reliable and secure SQLCLR code. It can do pretty much whatever code written in T-SQL can do within the database and server instance. It grants the CAS permissions listed in Table 1. As you can see in the table, the code is able to run and is able to read objects and data in the host SQL Server instance, using a special form of an ADO.NET connection string, either “context connection=true” or “context connection=yes.” Any other connection string will cause a security exception.

The resulting permission set granted to an assembly is the permissions listed in Table 1 intersected with those from the enterprise, machine, and user level. Because those levels by default have all permissions, the assembly receives only those listed in Table 1. Make sure you understand that.

EXTERNAL_ACCESS

The EXTERNAL_ACCESS permission set is a big step up from SAFE in that it allows restricted access to resources outside of the SQL Server instance, including disk files, the data and objects in other SQL Server instances, environment variables, and some parts of the registry. Access to these other resources is usually in the security context of the SQL Server service account, but the code can impersonate other users to get access. This level grants the permissions listed in Table 2, which is a big step up from those of SAFE assemblies.

The unrestricted FileIOPermission might look a little scary, since it means that from the CLR’s perspective the code can access any location on disk. But keep in mind that the code still is running with the operating system security of the local service account. So if that account can’t access a file, the SQLCLR code won’t be able to either.

The local service account is typically a very powerful account, so there is the possibility of abuse. The moral is to give access to these assemblies only to logins that you’d trust with the service account and don’t use the local system account as the service account for SQL Server.

It is interesting to note that one thing you can do with EXTERNAL_ACCESS is to use a more traditional ADO.NET connection string to connect to a database in the same SQL Server instance in which the SQLCLR code is running. This requires the SqlClientPermission so that you can use a connection other than the “context connection” string required to read the data in the current instance, specifying the usual server name, credentials, and so on. I haven’t figured out a reason yet why you’d want to do this, but it’s good to have options, right?

UNSAFE

The UNSAFE permission set is the SQLCLR equivalent of full trust, in which the CLR suspends all permissions checking. It receives a single, unrestricted SecurityPermission permission, which is the CLR’s way of granting full trust.

UNSAFE assemblies can potentially do all kinds of nasty things because it is inherently highly trusted code. It can call unmanaged code, such as COM components and the raw Win32 API. It is still subject to operating system permissions of the service account, but the CLR won’t restrict its ability to access any resources.

Because UNSAFE is so, well, unsafe, only a sysadmin can create this kind of assembly.

Accessing External Resources

Because accessing external resources requires interacting with the operating system, there are various rules that are observed in various situations when code attempts to access external resources.

The rule is simple for SAFE code: it if tries to access an external resource, access is denied and it throws an exception. End of story.

It’s a little more complex for EXTERNAL_ACCESS and UNSAFE. Things happen like this:

These rules initially confused me a bit, but then they began making sense as I worked with them more. Rule 1 works because a SQL login exists only in the world within SQL Server, so it would be a huge security hole if it could access operating system resources. Rule 2 also makes sense, and allows for impersonation. Rule 3 seems a bit harsh, but I suspect that the SQL Server team was trying to be conservative, since context switches could be a nightmare to manage and be sure that there were no security holes created.

The rules for external access get even a bit more complex. Assuming the login that is running code has survived the gauntlet listed above, SQL Server does not automatically impersonate the current execution context in order to access external resources, as you might expect (and perhaps hope). Instead, it uses the SQL Server instance’s service account to access the resource. Or you can explicitly impersonate the context login to access the resource. Doing so requires using the SqlContext object’s WindowsIdentity property to and then calling WindowsIdentity.Impersonate to do the actual impersonation.

Listing 1 shows how you can use impersonation in SQLCLR code. The WindowsImpersonationContext object is part of the System.Security.Principal namespace and represents the Windows user security context before you impersonate. The SqlContext object is part of the Microsoft.SqlServer.Server namespace installed with SQL Server and provides a hook between the SQL Server host and SQLCLR code. In this case, it uses the WindowsIdentity property to get a token of the current security identity. This is the security context of the Windows login under which this code is executing. The code tests whether the resulting CallerIdentity is null, which it will be if the code is executing under a SQL login. Finally, it calls the WindowsIdentity.Impersonate to do the actual impersonation, saving the original context for when it is time to revert to that context.

It is important to understand that the impersonation only needs to be in effect when performing the protected operation, such as opening a file. Once it is open, the code doesn’t need to be impersonating any longer. So revert back as soon as you’re done with the protected operation.

The finally block takes care of reverting to the original context, calling the OriginalContext’s Undo method. If you don’t revert before the function ends, SQL Server will raise an exception.

There are some restrictions on impersonation. When the impersonation is in effect, you can no longer access the data or objects with the SQL Server instance, except perhaps by making a connection to the server as though it was an external connection. You have to undo the impersonation before you can again access the local data. This also means that in-process data access is always in the context of the current security context for the session.

Interestingly, an UNSAFE assembly that executes asynchronously, meaning that it creates threads and runs code asynchronously, can never allow in-process data access. This isn’t a security issue but is definitely a reliability issue.

Trustworthy Databases

Another difference between SAFE assemblies and the other permission set levels was added late in the beta cycle for SQL Server 2005. You now have to meet one of two requirements to create either EXTERNAL_ACCESS or UNSAFE assemblies:

• The database owner (dbo) has to have EXTERNAL ACCESS ASSEMBLY permission and the database must have the TRUSTWORTHY property set, or
• The assembly must be signed with a certificate or an asymmetric key associated with a login that has EXTERNAL ACCESS ASSEMBLY permission.

The EXTERNAL ACCESS ASSEMBLY permission is another of the new granular permissions that allows a principal to create these kinds of assemblies. Administrators have it by default and can assign it to other logins. But do so with caution, of course, since this could potentially allow dangerous code to be installed in the server.

The TRUSTWORTHY property of a database requires admin privileges to set and is a prerequisite for installing non-SAFE assemblies in that database. Together with the EXTERNAL ACCESS ASSEMBLY permission, a DBA has control over both whether potentially dangerous assemblies can be installed in any database and who can put them there. Hopefully this will reassure DBAs who are worried about their server becoming infested with rogue .NET code!

It’s Secure Enough

Table 3 contains a summary of the three permission sets available for SQLCLR assemblies and what kind of protections SQL Server provides for each.

•     Code Access Security is the permissions that the CLR manages within the code.
  

•     Programming Model Restrictions are the host protection attributes as well as whether the code can use statics.
  

•     Verifiability refers to whether or not SQL Server verifies the relative safety of the code when you install it using the CREATE ASSEMBLY statement.
  

•     Call Native Code indicates whether the code can call Win32 APIs or do a platform invoke to external components.
  

As you can see, SQL Server can provide a nice sandbox for SQLCLR code that protects both the security of the data and the stability of the server, as long as you limit code to SAFE or to EXTERNAL_ACCESS with incredibly thorough code reviews.

Here’s a quiz to test your understanding of SQLCLR security. It is possible to use a regular connection string and ADO.NET to access another database, maybe an Oracle or Access database. Given what you now know about accessing external resources, what SQLCLR permission set level would an assembly that accesses an Oracle database need?

Think about it before reading further.

Hint: The only managed database provider included with the .NET Framework is System.Data.SqlClient.

Got it? The assembly must be installed as UNSAFE. Why? Because the code would have to use the System.Data.OleDb objects. Since these are based on COM objects, which is unmanaged code, the assembly needs to be installed as UNSAFE since that is the only level that can access unmanaged code.

Lest you think this is Microsoft’s way of bashing Oracle, the answer is the same for accessing a Microsoft Access database, since it too is based on OLE DB and thus unmanaged code.

I’ll make a bold statement here: UNSAFE code should never ever be used on a production server. Unless it is trivial code that can be thoroughly reviewed and firmly validated to verify that it will do no harm to the server, you just won’t be able to do enough to be confident that it is safe. While I won’t rule out that there are valid uses for UNSAFE code, I’d have to think long and hard about whatever the code does is worth the risk. I generally feel the same way about extended stored procedures, which are equally risky but have to be written in complex C++ code.

I’ll go so far as to say that any DBA that allows UNSAFE code to be installed on a production server is nuttier than a fruitcake unless proven otherwise. And I say that as a developer who often butts heads with DBAs, most of whom I know are nuttier than a fruitcake!

But SAFE code can do no more harm than a T-SQL stored procedure can do, and EXTERNAL_ACCESS is a reasonable compromise when you need to access external resources. So lay aside your fear of the unknown, and consider letting SAFE code into your database. Then consider EXTERNAL_ACCESS code when it makes sense for the database, applications, and users. Microsoft did a nice job at making that kind of code secure and reliable.

WindowsImpersonationContext OriginalContext = null;
try
{
   //Impersonate the current SQL Security context
   WindowsIdentity CallerIdentity = SqlContext.WindowsIdentity;

   if (CallerIdentity != null)
   {
      OriginalContext = CallerIdentity.Impersonate();
      //Do some protected operation
   }
}
catch
{
   //Recover from the access problem
}
finally
{
   if (OriginalContext != null)
      OriginalContext.Undo();
}