SQLCLR Security Overview

Microsoft knew that allowing .NET code to run inside the SQL Server 2005 engine would raise security-related concerns among database administrators. SQL Server 2005 addresses this concern by introducing a security architecture that combines SQL Server's user-based model with the .NET Framework's code-accessbased security model. You as an administrator have complete control over what the SQLCLR can and cannot do.

The code access security in .NET is based on permissions defined in the configurable Runtime Security Policy (which you access by selecting Start | Programs | Administrator Tools | .NET Framework 2.0 Configuration). Based on where the .NET assembly originated and the user running the assembly, the administrator can determine what that assembly can or cannot do. The .NET Framework security is provided via four configurable policy levels: Enterprise, Machine, User, and Host. The assembly receives the permissions based on the intersection of permissions defined in these policies. The highest level is Enterprise, which describes the policy for an entire application. The next level is the Machine policy, which affects all .NET assemblies running on the machine. For instance, if you define Machine policy that disallows writing or creating new entries in the registry under the HKLM hive, then all assemblies running on that machine will not be able to create entries or write into the HKLM hive in the registry. The User policy allows assigning different permissions to the same assembly based on the users executing it. In the case of SQL Server 2005, it is the account under which SQL Server 2005 service is running. The Host policy is defined by the host of the .NET CLRin this case, SQL Server 2005.

SQL Server 2005 Host policy allows you to put user assemblies into one of following three permissions buckets:

• SAFE This is the most restrictive and recommended permission set. It allows the assembly to perform internal computations and data access, and nothing else. An assembly with the SAFE permission set cannot access external system resources such as files. This is the default permission set.

• EXTERNAL_ACCESS This is the next level after SAFE, and it adds the ability to access external resources, such as files.

• UNSAFE The UNSAFE permission set allows assemblies unrestricted access to resources, both within and outside SQL Server. Assemblies can even call unmanaged code. Assemblies should be put in the UNSAFE permission bucket after thorough consideration and analysis. Only sysadmin role members can import assemblies by using this permission set.

When you import a .NET assembly by using the CREATE ASSEMBLY DDL statement, you can put that assembly in one of the three permissions buckets by using the WITH PERMISSION_SET = clause, as illustrated here:

CREATE ASSEMBLY sqlclrXMLHelper FROM 'E:\Dev\sqlclrXMLHelper.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS;

When the SQLCLR .NET code is executed, the intersection of permissions defined in Machine policy, User policy, and Host policy (the permissions bucket) determine what the code can or cannot do. The .NET integration in SQL Server 2005 and security considerations are further discussed in Chapter 11, "SQL Server 2005 and .NET Integration."