Securely Hosting SQLCLR Code
With the introduction of SQLCLR code, SQL Server 2005 now supports two entirely different runtime environments: good old reliable T-SQL and the new upstart SQLCLR. T-SQL has grown up with SQL Server over the years and is closely integrated with the data and objects stored in a database, tied neatly with the security systems in SQL Server. SQLCLR code, in stark contrast, internally uses a completely different security system provided by the CLR, a warm, safe, fuzzy environment in which code runs not with the security credentials of the user who runs it but credentials based on the characteristics of the code itself. At the same time, SQLCLR code has to execute within the security confines of the database and server. These two security systems are fundamentally different, and the SQL Sever team at Microsoft had to figure out a way to make them both coexist and work together.
|Sooner or later most every DBA will be faced with a compelling use for SQLCLR and will have to make a final decision about whether to let it into the database.|
The ability to host the CLR within another application reliably and securely is a new feature of version 2.0 of the .NET Framework. This hosting environment, and SQL Server's implementation of it, is the magic that makes the two security systems peacefully coexist, since the hosthere SQL Servercan exert a great deal of control over the code that runs. This means that from a security perspective managed SQLCLR code isn't allowed to access database objects it isn't authorized for. The code must run under the SQL Server security context of the user session that invoked it with the associated permissions, same as T-SQL code.
The bottom line: SQLCLR code can't do anything more in a database than an equivalent T-SQL code module running under the same security context. This fact alone should take the edge off of any DBAs nervousness!
Microsoft had three primary design goals when designing how to host the CLR:
- The CLR and the code running within it cannot compromise the security and stability of SQL Server.
- SQLCLR code must follow SQL Server authentication and authorization rules, which in part means that it runs under security context of the user session.
- Admins must be able to control access to operating system resources. This means that there must be a secure way to access machine resources from within the SQL Server process.
One of the clearest manifestations of these goals is that CLR integration is turned off by default. If you want to run .NET code in a database an administrator has to turn it on. The T-SQL code to turn it on requires using sp_configure
|Figure 1. Surface Area Configuration Tool: The figure shows how to enable SQLCLR using the Surface Area Configuration tool.|
sp_configure 'clr enabled', 1
You can also use the new Surface Area Configuration tool installed with SQL Server 2005, as shown in Figure 1
. Start the tool from the Windows Start menu, selecting Microsoft SQL Server 2005, Configuration Tools, and SQL Server Surface Area Configuration. Select Surface Area Configuration for Features, then select CLR Integration from the list of features.
It is important to understand what turning the CLR Integration feature on and off does. The only thing it affects is whether SQLCLR code will run. If it's off, no SQLCLR code will execute in that server instance; if it's on, any code can execute (assuming, of course, the user has the proper execution permission). If it is off, it does not
prevent you from installing
SQLCLR assemblies into the database. You can install all the assemblies you want (assuming, of course, you have the property permissions to do so) but they won't run under any circumstances until you enable CLR integration.
When SQLCLR code executes, it is within a rigid security environment, one layer among many that protects both operating system resources as well as data and objects within SQL Server.
|Figure 2. Security Layers: SQLCLR code doesn't run in a security vacuum. It is the low man on the security totem pole.|
shows these layers of security. The operating system imposes its own control, using the familiar user and group paradigm for granting access to resources that are decorated with Access Control Lists (ACLs). Every application running in Windows needs to run within a login's security context that has proper permissions to access the resource. Even SQL Server has to operate within this framework.
SQL Server controls the security of its own environment, using logins either of its own making or mapped to operating system logins. Within its environment, it grants or denies access to data and objects based on permissions assigned by the object's owner or an administrator. T-SQL operates within this permission scheme as a kind of final arbiter of who can access what. SQLCLR code executes within the same security environment as T-SQL code, but also executes within its own security environment provided by the CLR. The CLR implements Code Access Security (CAS) to grant its own permissions to running code. I'll get into more details about CAS in a few paragraphs, after I cover a couple more security details about the SQL Server host environment.