SQL CLR
This is the first in what I plan to be a series of articles examining the Common Language Runtime (CLR) hosted by SQL Server 2005. This installment looks at the security model of the CLR. Following articles will address performance and stability of the CLR as a whole, performance of managed code vs. T-SQL and general SQL CLR best practices.
There has been both excitement about SQL CLR and wariness. The idea of being able to create SQL Server objects in the same language a developer uses to write Windows or Web Applications is awesome. But because it is new and the "guardians" of our data (DBAs) don't fully understand the implications you may find that you will not be allowed to take advantage of this powerful new feature in SQL Server 2005.
But Microsoft is not the only database vendor out there plugging the CLR into their database platform. Oracle (http://www.oracle.com/technology/oramag/oracle/05-may/o35briefs.html) and other vendors have announced that they have either added the same capabilities or plan to in the future. So it sounds to me that we need to understand the implications of this new feature in order to determine what, if any, benefit we can draw from it.
The SQL CLR is not something to be feared, but it is something to be respected and used appropriately. T-SQL is king of set-based data manipulation and it is not going away any time soon. Know your tools and apply them appropriately. My hope is to provide a good understanding of the CLR and by the end of this article series you will know when and where to use it and when you should leave it be.
I chose security as my first subject because I hope to first dispel any misconceptions about the CLR being "dangerous" to SQL Server. It's true, used incorrectly it can be but by the time I'm done you should know and understand how to use it correctly.
There are two levels of security involved with the SQL CLR; SQL Server Security and the CLR's Code Access Security (CAS).
SQL Server Security
The first security restriction placed on the CLR by SQL Server is that the CLR is disabled by default. Assemblies can be registered and objects can be created, but they cannot be executed. Any attempt to call/execute a SQL CLR object will be met with the following response:
Execution of user code in the .NET Framework is disabled. Enable
"clr enabled" configuration option.
To enable the CLR run the following script with an account which has been granted ALTER SETTINGS permission (sysadmin, serveradmin or an account explicitly granted the permissions):
EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
Or you can use the SQL Server Surface Area Configuration Tool to modify features, but you must still have permissions to ALTER SETTINGS.
Next, SQL Server manages CLR object permissions the same way it manages T-SQL object permissions. Once an account has been created it can be granted any combination of the following permission sets on CLR objects:
CREATE ASSEMBLY – permission to add new assemblies EXECUTE – permission to call/invoke specific CLR objects from code. This permission is checked at execution time. REFERENCES – permission to reference specific CLR objects when creating new T-SQL or CLR objects. This permission is checked at compile/creation time. SELECT – permission to call and return results from a table-valued function.
Additionally, the following permissions indirectly affect what can be done when creating objects:
INSERT, SELECT, UPDATE, DELETE – permission to execute the specified method against a table, view. This is like REFERENCES and the permissions are checked against the owner/creator of the object at compile time. So as long as the caller has EXECUTE permission against the specific object these permissions will not be checked at execution time.
Here are some examples of what a user can do when given the specified permissions:
CREATE ASSEMBLY myAssembly
AUTHORIZATION dbo
FROM 'C:\projects\mySolution\myProject\bin\Release\myAssembly.dll'
WITH PERMISSION_SET = SAFE
GO
DECLARE @ret INT
SET @ret = dbo.clrfunction_add_numbers(3, 7)
SELECT * FROM dbo.clrfunction_string_to_table
('key1|key2|key3|key4|key5', '|')
Hosting the CLR
Before I get into the security features of the CLR I want to take a moment to discuss what it means to host the CLR – this should help to better understand the security built into the CLR.
Microsoft implemented the CLR as a COM server; the implementation is located in a file called MSCorWks.dll. It is located in the Windows\Microsoft.Net\Framework\{version} folder. When an application wants to host the CLR it uses a COM interface called ICLRRuntimeHost which is defined in an unmanaged C++ header file named MSCorEE.h.
To load the CLR the hosting application calls CorBindToRuntimeEx which returns a pointer to ICLRRuntimeHost. At this point the host can then use the interface methods to control which classes/members can be loaded and executed. The interface also defines methods that allow the host to control memory, threads, and assembly loading among other things.
I'll discuss more about this in my next article on CLR Performance, but you can refer to the SDK documentation or the MSCorEE.h file itself. At this time I'd like to thank Jeffery Richter of Wintellect for his explanation of CLR Hosting in CLR via C# (Microsoft Press). For more information refer to his book. If you want more detail than just a chapter, Jeffery refers you to Customizing the Microsoft.NET Framework Common Language Runtime (Microsoft Press).
CLR Security
There are some aspects of SQL CLR security which blur the line between security and stability. The reason for some security features is to enforce a "do no harm" policy on the CLR. This is to prevent the CLR from being able to cause SQL Server to become unstable. The first of these features is Host Protection Attributes (HPA).
Host Protection Attributes
A Host Protection Attribute (HPA) inherits from System.Attribute and defines the type of operation(s) performed by the API which declares the HPA. When a hosting application such as SQL Server loads the CLR it can check these attributes and define functionality it will not allow. SQL Server disallows the following HPAs:
ExternalProcessMgmt ExternalThreading MayLeakOnAbort SecurityInfrastructure SelfAffectingProcessMgmnt SelfAffectingThreading SharedState Synchronization UI
As I mentioned before these restrictions are as much for stability as they are for security. For example, you do not want code to execute that would open a message box and block for a user response. For a complete list of all disallowed types and members see MSDN.
There are other HPAs which are not completely disallowed, but they are restricted based on the value of the PERMISSION_SET clause used when CREATE ASSEMBLY is called (more on this later on).
Code Access Security
The CLR defines a security model called Code Access Security (CAS) which restricts permissions based on the identity of the code itself. CAS defines four levels of security:
- Enterprise
- Machine
- User
- Host
Most of you may be familiar with the first three if you have ever used the .NET Framework Configuration tools. They allow you to define the security policy at each of the different levels. By default all code which is installed on the local machine runs under FullTrust, which means there are no restrictions on which assemblies may be loaded or which methods may be executed. But the fourth level, Host, is defined by the application hosting the CLR and cannot be configured by the user.
SQL Server uses the Host policy to, among other things; restrict access to classes and methods based on their declared HPA(s). If I wanted to create/install a new assembly to I would run the following script:
CREATE ASSEMBLY myAssembly
AUTHORIZATION dbo
FROM 'C:\projects\mySolution\myProject\bin\Release\myAssembly.dll'
WITH PERMISSION_SET = SAFE
GO
The permissions granted to this new assembly are based on the value of PERMISSION_SET. PERMISSION_SET may have one of three possible values:
SAFE: Same permissions as T-SQL. Internal Data access only. EXTERNAL_ACCESS: Allows access to external resources under the SQL Server service account by default (default service account = local system) to file system, registry, environment variables. UNSAFE: The same as CAS "FullTrust" permission set. Which means the CLR won't check permissions. Like EXTERNAL_ACCESS, access to external resources will run in the context of the SQL Server service account by default.
For details on exactly which HPAs are allowed/disallowed by the SQL Server CAS Host policy see MSDN.
If you create an object which accesses a type or method which is not permitted by the PERMISSION_SET clause when the assembly was created you will be able to create the assembly and create an object which uses the CLR method/function. But then when you try to reference the new object it will fail with a System.SecurityException like the following:
A .NET Framework error occurred during execution of user defined routine or aggregate 'clrfunction_getHostName':
System.Security.SecurityException: Request for the permission of type
'System.Net.DnsPermission, System, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand,
StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.Dns.GetHostName()
at UserDefinedFunctions.ExternalAccessMethod()
If you see this message it means you need to create your assembly with either EXTERNAL_ACCESS or UNSAFE as the PERMISSION_SET value. Before you can create an assembly with EXTERNAL_ACCESS or UNSAFE two conditions must exist:
- Database must be marked as
TRUSTWORTHY by an administrator (member of sysadmin fixed server role) or the assembly has been signed with a certificate or asymmetric key - User creating the assembly must have
EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permissions
Otherwise when you attempt to create your assembly you will be greeted with the following message:
CREATE ASSEMBLY for assembly '%' failed because assembly '%' is not
authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized
when either of the following is true: the database owner (DBO) has EXTERNAL
ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database
property on; or the assembly is signed with a certificate or an asymmetric
key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
A discussion on creating certificates or asymmetric keys is outside the scope of this discussion, but to mark your database as TRUSTWORTHY a member of the sysadmin role can run the following script:
ALTER DATABASE MaxPreps_v2 SET TRUSTWORTHY ON
GO
Just make sure that your use of a method which requires EXTERNAL_ACCESS or UNSAFE is justified. Before you resort to running this sort of code from within SQL Server you should know how it affects your system and application. Ask yourself if the same functionality could be implemented as an external process instead of from within the SQL Server process space. On the other hand, the nice part about all this is knowing that checks and balances are in place to protect your database from running code that could be potentially dangerous.
Verification
SQL Server requires that assemblies need to be verifiably type safe in order to run as SAFE or EXTERNAL_ACCESS. Managed code that is not type safe is code which is compiled with the /unsafe compiler switch. Type safe code (don't confuse this with PERMISSION_SET = SAFE) is guaranteed to perform all memory access operations within its own address space and without affecting the stability of other running applications. When an assembly is created with SAFE or EXTERNAL_ACCESS specified SQL Server verifies that the assembly is type safe when the assembly is created. You can perform this same verification process using PEVerify.exe, included with the .Net SDK.
Execution Context
By default, within SQL Server, CLR objects run in the execution context of the session of the caller. If an assembly marked as EXTERNAL_ACCESS or UNSAFE attempts to access resources external to SQL Server the execution context will be that of the SQL Server services account. Be default, SQL Server is installed under the LocalService account.
However, there are two levels of impersonation available; the SQL Server EXECUTE AS command and the WindowsIdentity.Impersonate() method.
- EXECUTE AS – allows the user to change the SQL Server execution context. This requires IMPERSONATE permission to be granted on the account to be impersonated for the user doing the impersonating.
EXECUTE AS USER = 'SOMEDOMAIN\someuser'
SELECT * FROM dbo.clrfunction_string_to_table('key1|key2|key3', '|')
REVERT
WindowsIdentity.Impersonate() – if the user account of the current session was authenticated using Windows Authentication then, for the purpose of accessing external resources the CLR object can specify it wants to use the execution context of the Windows user account instead of the SQL Server Service account. Note that this prevents you from accessing internal SQL Server resources using the current context. This means that your object will be required to access local data as if it were an external client. So as soon as you are done with the external resources you should revert back to the original context.
WindowsImpersonationContext executionContext = null;
try
{
WindowsIdentity callerId = SqlContext.WindowsIdentity;
if (callerId != null)
{
executionContext = callerId.Impersonate();
}
}
catch(Exception ex)
{
}
finally
{
if (executionContext != null)
executionContext.Undo();
}
So when using WindowsIdentiy.Impersonate() remember the following :
- Impersonation is only needed for external operations so your assembly must be created with
EXTERNAL_ACCESS or UNSAFE. - If you don't revert before exiting, an exception is thrown.
- While impersonating, local data access (using "Context Connection=yes" in your connection string) is denied. Connections must be made as if the client were an external client using a "standard" connection string.
Conclusion
In summary, there isn't much more work involved to secure SQL CLR objects than there is to secure T-SQL objects, the SQL Server security object model remains the same. However, as you can see, under the hood a lot of work went into enabling this powerful new feature in a secure manner. I hope this article has shed some light on how the integrated CLR works in SQL Server. Stay tuned for my next article on CLR Performance and Stability.
References
