SQL Server 2008 : Using the CLR - Understanding Permission Sets

10/13/2010 9:39:48 AM
When registering an assembly with SQL Server, code within that assembly may adversely affect or compromise the system. To help protect against these events, SQL Server allows you to restrict the operations the assembly is able to perform. This is done at assembly registration time, by using permission sets. PERMISSION_SET is a parameter supplied to the CREATE ASSEMBLY statement, and specifies what permission restrictions apply to the assembly. Note that these restrictions apply only when the assembly is accessed from within SQL Server—for example, when using a CLR stored procedure. If the assembly is called by some other means, these permission restrictions will not apply.

Three permission sets are available in SQL Server 2008:

  • SAFE



Let’s examine the permission sets, and what effect they have on assembly execution. SAFE is the most restrictive permission set. SAFE is also the default permission set, which applies if you call CREATE ASSEMBLY, but do not specify the PERMISSION_SET parameter. When running with the SAFE permission set, assembly code is not able to access any external resources such as the network, files, or the Windows registry. The SAFE permission set is ideal for assemblies that perform computational tasks or store user-defined data types, and that don’t require any external resource access. SAFE assemblies also have permission to access data within SQL Server using the execution context. If code within an assembly granted the SAFE permission set attempts to access external resources, the operation will fail and the code will receive a security exception.

EXTERNAL_ACCESS is the permission set you will assign to your assembly if you require it to access external resources. Assemblies registered with the EXTERNAL_ACCESS permission set can access and modify the file system, write to event logs, use network services like DNS and Active Directory, create outbound network connections including Web access (port 80 and 443) and SMTP mail access (port 25), and access HKEY_CLASSES_ROOT, HKEY_LOCAL_MACHINE, HKEY_CURRENT_USER, HKEY_CURRENT_CONFIG, and HKEY_USERS registry keys. When code from an assembly registered with the EXTERNAL_ACCESS permission set attempts to access resources outside SQL Server, it will do so under the SQL Server service account. Code with EXTERNAL_ACCESS permission can perform only those operations available as part of the .NET Framework Base Class Library. As mentioned earlier, the CLR performs extensive security and stability checks on code that executes within it. This means that managed code is inherently safer than unmanaged code, as it includes an extra layer of protection provided by the CLR. The EXTERNAL_ACCESS permission set does not allow you to call unmanaged code directly.

The UNSAFE permission set allows your code to perform all operations allowed under the EXTERNAL_ACCESS permission set. The difference between the two permission sets is that the UNSAFE permission set also allows code running under it to call unmanaged code directly. Unmanaged code is called using a mechanism known as Platform Invoke (P/Invoke). Because unmanaged code is not subject to CLR checks, it could potentially damage the stability of your server. In some cases, malicious unmanaged code can be used to subvert SQL Server security and even security of the server on which SQL Server is running. Assign the UNSAFE permission set with caution and only to assemblies that absolutely require it. You should be certain that any assemblies to which you assign the UNSAFE permission are trustworthy and stable.

Figure 1 explains a method you can use to select the required permission set for your assembly. Note that you must test all assemblies for stability regardless of the permission set you intend to assign to them. Untested assemblies may cause errors and make your application behave unexpectedly.

Figure 1. Selecting an Appropriate Permission Set

What account does the assembly code run as? The assembly code runs in the SQL Server service process. It runs as the SQL Server service account. This is another reason to configure the SQL Server service to run as a least-privileged service account, not the highly privileged Local System account. If the caller of the assembly is a SQL Server login, and the assembly is trying to access external resources, the external access call will not succeed. Only if the caller is a Windows login will the call be performed using the SQL Server service account credentials. It is possible to impersonate another account from within the assembly code, but normal Windows security restrictions apply.


Remember that any code that runs within CLR database objects will run as the SQL Server service account. If the SQL Server service account is Local System, it will not be able to access network resources. An example of a network resource is a file in a network share. In order for your CLR code to be able to access network resources, it must run as a domain account that has permissions to access the resource.

When calling a CLR database object, you must be authenticated as a Windows login in order to access external resources. If you are authenticated as a Windows login, your credentials and permissions will not be used. The SQL Server service account credentials will be used.

New & Noteworthy...: Understanding the TRUSTWORTHY Database Property

SQL Server 2008 databases have a TRUSTWORTHY property. This property indicates whether objects in your database, including user-defined functions and stored procedures, can access resources outside the database while running as the SQL Server service account. When the TRUSTWORTHY property is set to ON, your CLR integrated objects will be able to access external resources, subject to the permission set they have been assigned. When the TRUSTWORTHY property is set to OFF, your CLR objects will receive an error when attempting to access resources outside the database.

To check the current value of the TRUSTWORTHY property, query the is_trustworthy_on column in the sys.databases table as shown in the following example:

SELECT name, is_trustworthy_on AS IsTrustworthy
FROM sys.databases
WHERE name = 'DatabaseName'

To set the value of the TRUSTWORTHY property to either ON or OFF, run the following command:


By default, the TRUSTWORTHY property is off in databases to protect your system against malicious code. In order to change the TRUSTWORTHY property you must be a member of the sysadmin server role.

Top 10
Review : Sigma 24mm f/1.4 DG HSM Art
Review : Canon EF11-24mm f/4L USM
Review : Creative Sound Blaster Roar 2
Review : Philips Fidelio M2L
Review : Alienware 17 - Dell's Alienware laptops
Review Smartwatch : Wellograph
Review : Xiaomi Redmi 2
Extending LINQ to Objects : Writing a Single Element Operator (part 2) - Building the RandomElement Operator
Extending LINQ to Objects : Writing a Single Element Operator (part 1) - Building Our Own Last Operator
3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular Tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8