CLR Integration Security

The security model of the Microsoft SQL Server integration with the Microsoft .NET Framework common language runtime (CLR) manages and secures access between different types of CLR and non-CLR objects running within SQL Server. These objects may be called by a Transact-SQL statement or another CLR object running in the server.

CLR Integration Code Access Security

The CLR supports a security model called code access security (CAS) for managed code. In this model, permissions are granted to assemblies based on the identity of the code, as opposed to the identity of the user or process running the code. CAS permissions are granted based on evidence and policies. For example, the origin of an assembly is a form of evidence, and a policy could specify that code downloaded from the Internet should be granted a very limited set of permissions. System administrators set policies, removing security decisions from users and developers. The security policy that determines the permissions granted to assemblies is defined in three different places:

• Machine policy: This is the policy that is in effect for all managed code running in the machine on which SQL Server is installed.

• User policy: This is the policy that is in effect for managed code hosted by a process. For SQL Server, the user policy is specific to the Windows account on which the SQL Server service is running.

• Host policy: This is the policy that is set up by the host of the CLR (in this case, SQL Server) that is in effect for managed code running in that host.

The set of CAS permissions that are granted to managed code when running inside SQL Server is the intersection of the set of permissions granted by the above three policy levels. Even if SQL Server grants a set of permissions to an assembly loaded in SQL Server, the eventual set of permissions given to user code may be restricted further by the user and machine-level policies.

CAS for CLR assumes that the runtime can host both fully trusted and partially trusted code. The resources protected by CLR CAS are typically wrapped by managed application programming interfaces (APIs) that demand the corresponding permission before allowing access to the resource. The demand for the permission is satisfied only if all the callers (at the assembly level) in the call stack have the corresponding resource permission.

SQL Server Host Policy Level Permission Sets

The set of CAS permissions granted to user-defined assemblies by the SQL Server host policy level is determined by the permission set specified when creating the assembly. There are three permission sets: SAFE, EXTERNAL_ACCESS and UNSAFE.

SQL Server supplies a host-level security policy level to the CLR while hosting it; this policy is an additional policy level below the two policy levels that are always in effect. This policy is set for every application domain that SQL Server creates. This policy is not meant for the default application domain that would be in effect when SQL Server creates an instance of the CLR.

The SQL Server host-level policy is a combination of SQL Server fixedpolicy for system assemblies and user-specified policy for user assemblies. The fixed policy for CLR assemblies and SQL Server system assemblies grants them full trust. The user-specified portion of the SQL Server host policy is based on the assembly owner specifying one of three permission buckets for each assembly:

• SAFE: Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry.

• EXTERNAL_ACCESS: Like the SAFE permission set, but with the additional ability to access external system resources such as files, networks, environmental variables, and the registry.

• UNSAFE: UNSAFE allows assemblies unrestricted access to resources, both within and outside SQL Server. Code executing from within an UNSAFE assembly can call unmanaged code.

  Security Note:

  SAFE is the recommended permission setting for assemblies that perform computation and data management tasks without accessing resources outside SQL Server. EXTERNAL_ACCESS is recommended for assemblies that access resources outside SQL Server. EXTERNAL_ACCESS assemblies by default execute as the SQL Server service account. It is possible for EXTERNAL_ACCESS code to explicitly impersonate the caller’s Windows Authenticated security context. Since the default is to execute as the SQL Server service account, permission to execute EXTERNAL_ACCESS should only be given to logins trusted to run as the service account. From a security perspective, EXTERNAL_ACCESS and UNSAFE assemblies are identical. However, EXTERNAL_ACCESS assemblies provide various reliability and robustness protections that are not in UNSAFE assemblies. Specifying UNSAFE allows the code in the assembly to perform illegal operations against the SQL Server process space and hence can potentially compromise the robustness and scalability of SQL Server.

Accessing External Resources

If a user-defined type (UDT), stored procedure, or other type of construct assembly is registered with the SAFE permission set, then managed code executing in the construct is unable to access external resources. However, if either the EXTERNAL_ACCESS or UNSAFE permission sets are specified, and managed code attempts to access external resources, SQL Server applies the following rules:

Calling Code Through Links

The SQL Server CLR integration security model is intended to govern code executing inside of SQL Server, and is intended to prevent elevation of privilege attacks against protected system resources from managed code. User-defined code can call other code inside of SQL Server, either in Transact-SQL or in one of the managed languages. The relationships between these objects are referred to as links. The three types of links are described in the following table.

SQL Server Security Checks for CLR Code

Code in SQL Server always executes under the context of a process, or user account or role, and the permissions granted to that account or role. Once CLR code is invoked, SQL Server performs the following security checks:

• All invocations require EXECUTE permission unless the invocations occur within the same object. Intra-assembly calls do not require any permission checks. The permission is checked at execution time.

• Gated links require REFERENCE permission against the callee when the calling object is created. The permission is checked for the owner of the calling object when the object is created.

• Table-access links require the corresponding SELECT, INSERT, UPDATE or DELETE permission against the table or view being accessed.

• The permission is checked against the current execution context. Procedures and functions can be created with an execution context that is different from the caller. Assemblies are always created with caller as the execution context.

CLR Integration Security and Impersonation

When managed code accesses external resources, SQL Server will not automatically impersonate the current execution context under which the routine is executing. Code in EXTERNAL_ACCESS and UNSAFE assemblies can explicitly impersonate the current execution context as follows:

The in-process data access provider provides an API (WindowsIdentity) that can be used to retrieve the identity associated with the current security context. Managed code in EXTERNAL_ACCESS and UNSAFE assemblies can use this method to retrieve the context and use the .NET Framework Impersonate method to impersonate that context. The following restrictions apply when user code explicitly impersonates:

• In-process data access is not allowed when managed code is in an impersonated state. Code can undo the impersonated state, and then call in-process data access. Note that undoing impersonation requires storing the return value (a WindowsImpersonationContext object) of the original Impersonate method, and calling the Undo method on this WindowsImpersonationContext.

  This restriction means that when in-process data access occurs, it is always performed in the current security context in effect for the session and cannot be modified by explicit impersonation within managed code.

• For managed code executing asynchronously (for example, through UNSAFE assemblies creating threads and running code asynchronously), in-process data access is never allowed, regardless of whether or not there is impersonation.

When code is running in an impersonated context that is different from SQL Server, it cannot perform in-process data access calls; it should undo the impersonation context before making in-process data access calls. When in-process data access is made from managed code, the original execution context of the Transact-SQL entry point into the managed code is always used for authorization.

Both EXTERNAL_ACCESS assemblies and UNSAFE assemblies access operating system resources with the SQL Server service account unless they voluntarily impersonate the current security context as previously described. Because of this, the authors of EXTERNAL_ACCESS assemblies require a higher level of trust than those of SAFE assemblies, which is specified by the EXTERNAL_ACCESS login-level permission. Only logins who are trusted to run code under the SQL Server service account should be granted the EXTERNAL_ACCESS permission.

CLR Integration Security and Application Domains

SQL Server loads assemblies belonging to the same owner in the same application domain. By virtue of a set of assemblies running in the same application domain, assemblies are able to discover each other at execution time using the .NET Framework Reflection APIs or other means, and are able to call into them in late-bound fashion. Since such calls occur against assemblies belonging to the same owner, there are no SQL Server permissions checked for these calls. The placement scheme of assemblies in application domains is designed primarily to achieve scalability, security, and isolation goals and can potentially change in future releases. Hence, you should not rely on finding assemblies in the same application domain through late-bound mechanisms.