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
EXTERNAL_ACCESS
UNSAFE
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.
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.
Tip
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.
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:
ALTER DATABASE DatabaseName SET TRUSTWORTHY ON
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.
