Security in the CLR World Inside SQL Server : Page 4

Is running .NET Framework code within SQL Server 2005 exciting or a threat? Which is it? This article explores the security issues of SQLCLR code so that both developers and DBAs can make informed decisions about its use.

by Don Kiely

May 22, 2006

Page 4 of 6

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.

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 Resourcessidebar.

SAFESAFE 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.

Table 1: Permission set granted to SAFE assemblies.

Permission

Type

Restriction, if any

SecurityPermission

Restricted

Execution

SqlClientPermission

Restricted

No blank password, context connection string only

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.

Table 2: Permission set granted to EXTERNAL_ACCESS assemblies.

Permission

Type

Restriction, if any

EnvironmentPermission

Unrestricted



FileIOPermission

Unrestricted



RegistryPermission

Restricted

Read only access to HKEY_CLASSES_ROOT, HKEY_LOCAL_MACHINE, HKEY_CURRENT_USER, HKEY_CURRENT_CONFIG, and HKEY_USERS

SecurityPermission

Restricted

Assertion, Execution, SerializationFormatter, ControlPrincipal

KeyContainerPermission

Unrestricted



SqlClientPermission

Unrestricted



EventLogPermission

Restricted

Only on local machine, by Administrators only

DnsPermission

Unrestricted



SocketPermission

Restricted

IP address only

WebPermission

Restricted

Access local host only via HTTP

SmtpPermission

Restricted

Connect access only

NetworkInformationPermission

Restricted

Ping access only

DistributedTransactionPermission

Unrestricted



StorePermission

Unrestricted



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.

An UNSAFE assemblies can potentially do all kinds of nasty things because it's inherently highly trusted code. For example, 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.