4.1. CLR Integration Design Objectives

Microsoft identifies the design objectives of SQL Server 2005 CLR integration as follows:

Reliability

CLR routines cannot perform operations that compromise the integrity of the Database Engine process, nor are they allowed to overwrite Database Engine memory buffers and internal data structures.

Scalability

SQL Server and the CLR have different models for threading, scheduling, and memory management. The design goal is to ensure scalability when user code calls APIs for threading, synchronization primitives, and memory.

Security

User code running in the database must follow SQL Server authentication and authorization rules for accessing database objects. Additionally, administrators must be able to control access to operating system resources from code running within the database.

Performance

User code running in the database must perform at least as well as equivalent implementations through native Database Engine functionality or T-SQL.

The CLR provides the following services to achieve these design objectives:

Type-safe verification

After assemblies are loaded into the CLR but before they are compiled, the code is verified to ensure access to memory structures only in well-defined wayscode that passes this verification is type-safe.

Application domains

Application domains are execution spaces within a host process where assemblies are loaded, unloaded, and executed. They provide isolation between executing assemblies.

Code access security

CAS applies permissions to code to control the operations that the code can perform and the system resources it can access based on the identity of the code.

Host Protection Attributes (HPA)

HPA is a mechanism to annotate .NET-managed APIs with attributes of interest to host CLRs such as SQL Server. The host CLR can deny user code calls to APIs that are on a prohibited list.

SQL Server 2005 hosts the CLR in the Database Engine, effectively acting as the operating system for the CLR. The design goals for SQL Server 2005 CLR integration for reliability, scalability, and security are accomplished as follows:

Reliability

You cannot always recover from critical exceptions in .NET-managed code when a thread abort exception is raised. If there is any shared state in the application domain in which the thread abort exception occurs, the SQL Server-hosted CLR unloads that application domain, thereby stopping database transactions running in it.

Scalability

The CLR calls SQL Server APIs to create threads and calls SQL Server synchronization objects to synchronize threads. All threads and synchronization objects are known to SQL Server, so it can effectively schedule non-CLR threads, detect and remove deadlocks involving CLR synchronization objects, and detect and handle CLR threads that have not yielded in a reasonable amount of time.

The CLR calls SQL Server primitives to allocate and deallocate memory. This lets SQL Server stay within its configured memory limitsSQL Server can reject CLR memory requests when memory is constrained or ask the CLR to reduce its memory use as necessary.

Security

When a SQL Server registered assembly is created or altered, you can specify one of three permissions sets for the assembly: SAFE, EXTERNAL-ACCESS, or UNSAFE. SQL Server uses permission sets to set CAS permissions when the assembly executes. The three permission sets are described in Table 4-1.

Table 4-1. SQL Server CLR routine permission sets

Permission set	Description
SAFE	SAFE assemblies can access data from local SQL Server databases and can execute computations and business logic not involving resources outside the local databases. SAFE assemblies cannot access external system resources such as files, networks, environment variables, or the registry. The SAFE permission set can only be applied to code that is verifiably type-safe. It is the default permission set and the most restrictive.
EXTERNAL-ACCESS	EXTERNAL-ACCESS allows assemblies to access certain external system resources such as files, networks, environment variables, and the registry in addition to the access provided by the SAFE permission set. The EXTERNAL-ACCESS permission set can only be applied to code that is verifiably type-safe.
UNSAFE	UNSAFE assemblies have unrestricted access to resources both inside of and outside of SQL Server. An UNSAFE assembly can call unmanaged code. Only a database administrator can register an UNSAFE assembly.

The SQL Server-hosted CLR imposes the following security-related programming restrictions:

• Code marked SAFE or EXTERNAL-ACCESS cannot use static data members and variables.

• Calls cannot be made to .NET Framework API types or members annotated with the ExternalProcessMgmt, MayLeakOnAbort, SharedState, or Synchronization host protection attributes (HostProtectionAttribute)this prevents code in assemblies marked as SAFE or EXTERNAL-ACCESS from calling APIs that might cause resource leaks on termination, that enable sharing state, or that perform synchronization.