Specifying Security When Creating Assemblies
When creating an assembly into a SQL Server database, you can specify one of three different levels of security in which your code can run: SAFE, EXTERNAL_ACCESS, or UNSAFE. When the CREATE ASSEMBLY statement is run, certain checks are performed on the code assembly which may cause the assembly to fail to register on the server.
SAFE is the default permission set and works for the majority of scenarios. To specify a given security level, you modify the syntax of the CREATE ASSEMBLY statement as follows:
CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
WITH PERMISSION_SET = SAFE
It is also possible to create an assembly with the SAFE permission set by simply omitting the third line of code above:
CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
When code in an assembly runs under the SAFE permission set, it can only do computation and data access within the server through the in-process managed provider.
Creating EXTERNAL_ACCESS and UNSAFE Assemblies
EXTERNAL_ACCESS addresses scenarios in which the code needs to access resources outside the server, such as files, network, registry, and environment variables. Whenever the server accesses an external resource, it impersonates the security context of the user calling the managed code.
UNSAFE code permission is for those situations in which an assembly is not verifiably safe or requires additional access to restricted resources, such as the Microsoft Win32 API.
To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, one of the following two conditions must be met:
-
The assembly is strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).
-
The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON.
The two conditions listed above are also checked at assembly load time (which includes execution). At least one of the conditions must be met in order to load the assembly.
We recommend that the TRUSTWORTHY Database Property on a database not be set to ON only to run common language runtime (CLR) code in the server process. Instead, we recommend that an asymmetric key be created from the assembly file in the master database. A login mapped to this asymmetric key must then be created, and the login must be granted EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission.
The following Transact-SQL statements perform the steps that are required to create an asymmetric key, map a login to this key, and then grant EXTERNAL_ACCESS permission to the login. You must run the following Transact-SQL statements before running the CREATE ASSEMBLY statement.
USE master
GO
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\MyDBApp\SQLCLRTest.dll'
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin
GO
.gif) Note: |
|---|
|
You must create a new login to associate with the asymmetric key. This login is only used to grant permissions; it does not have to be associated with a user, or used within the application.
|
To create an EXTERNAL ACCESS assembly, the creator needs to have EXTERNAL ACCESS permission. This is specified when creating the assembly:
CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
The following Transact-SQL statements perform the steps that are required to create an asymmetric key, map a login to this key, and then grant UNSAFE permission to the login. You must run the following Transact-SQL statements before running the CREATE ASSEMBLY statement.
USE master
GO
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\MyDBApp\SQLCLRTest.dll'
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey
GRANT UNSAFE ASSEMBLY TO SQLCLRTestLogin
GO
To specify that an assembly loads with UNSAFE permission, you specify the UNSAFE permission set when loading the assembly into the server:
CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
WITH PERMISSION_SET = UNSAFE
For more details about the permissions for each of the settings, see CLR Integration Security.