Browse DevX
Sign up for e-mail newsletters from DevX


Security in the CLR World Inside SQL Server : Page 5

Is running .NET Framework code within SQL Server 2005 exciting or a threat? Which is it? This article explores the security issues of SQLCLR code so that both developers and DBAs can make informed decisions about its use.

Accessing External Resources
Because accessing external resources requires interacting with the operating system, there are various rules that are observed in various situations when code attempts to access external resources.

The rule is simple for SAFE code: it if tries to access an external resource, access is denied and it throws an exception. End of story.

It's a little more complex for EXTERNAL_ACCESS and UNSAFE. Things happen like this:

  1. If the code is executing under the security context of a SQL Server login (i.e., one that is not mapped to a Windows user or group), access is denied and an exception thrown. A SQL login doesn't have any permissions outside of SQL Server, so this makes sense.
  2. If the code is executing under a login that is mapped to a Windows login, the execution context for the external access is that of that login. If the user has access to the resource in Windows, the code succeeds. If not, access is denied and an exception thrown.
  3. If the caller is not the original caller (there has been an execution context switch), then access is denied and an exception is thrown.
These rules initially confused me a bit, but then they began making sense as I worked with them more. Rule 1 works because a SQL login exists only in the world within SQL Server, so it would be a huge security hole if it could access operating system resources. Rule 2 also makes sense, and allows for impersonation. Rule 3 seems a bit harsh, but I suspect that the SQL Server team was trying to be conservative, because context switches could be a nightmare to manage and still be sure that no security holes were created.

The rules for external access get even a bit more complex. Assuming the login that is running code has survived the gauntlet listed above, SQL Server does not automatically impersonate the current execution context in order to access external resources, as you might expect (and perhaps hope). Instead, it uses the SQL Server instance's service account to access the resource. Or you can explicitly impersonate the context login to access the resource. Doing so requires using the SqlContext object's WindowsIdentity property to call WindowsIdentity.Impersonate to do the actual impersonation.

Listing 1 shows how you can use impersonation in SQLCLR code. The WindowsImpersonationContext object is part of the System.Security.Principal namespace and represents the Windows user security context before you impersonate. The SqlContext object is part of the Microsoft.SqlServer.Server namespace installed with SQL Server and provides a hook between the SQL Server host and SQLCLR code. In this case, it uses the WindowsIdentity property to get a token of the current security identity. This is the security context of the Windows login under which this code is executing. The code tests whether the resulting CallerIdentity is null, which it will be if the code is executing under a SQL login. Finally, it calls the WindowsIdentity.Impersonate to do the actual impersonation, saving the original context for when it is time to revert to that context.

It is important to understand that the impersonation only needs to be in effect when performing the protected operation, such as opening a file. Once it is open, the code doesn't need to be impersonating any longer. So revert back as soon as you're done with the protected operation.

The finally block takes care of reverting to the original context, calling the OriginalContext's Undo method. If you don't revert before the function ends, SQL Server will raise an exception.

There are some restrictions on impersonation. When the impersonation is in effect, you can no longer access the data or objects with the SQL Server instance, except perhaps by making a connection to the server as though it were an external connection. You have to undo the impersonation before you can again access the local data. This also means that in-process data access is always in the context of the current security context for the session.

Interestingly, an UNSAFE assembly that executes asynchronously, meaning that it creates threads and runs code asynchronously, can never allow in-process data access. This isn't a security issue but is definitely a reliability issue.

Trustworthy Databases
Another difference between SAFE assemblies and the other permission set levels was added late in the beta cycle for SQL Server 2005. You now have to meet one of two requirements to create either EXTERNAL_ACCESS or UNSAFE assemblies:

  • The database owner (dbo) has to have EXTERNAL ACCESS ASSEMBLY permission and the database must have the TRUSTWORTHY property set.

  • The assembly must be signed with a certificate or an asymmetric key associated with a login that has EXTERNAL ACCESS ASSEMBLY permission.
The EXTERNAL ACCESS ASSEMBLY permission is another of the new granular permissions that allows a principal to create these kinds of assemblies. Administrators have it by default and can assign it to other logins. But do so with caution, of course, since this could potentially allow dangerous code to be installed in the server.

The TRUSTWORTHY property of a database requires admin privileges to set and is a prerequisite for installing non-SAFE assemblies in that database. Together with the EXTERNAL ACCESS ASSEMBLY permission, a DBA has control over both whether potentially dangerous assemblies can be installed in any database and who can put them there. Hopefully this will reassure DBAs who are worried about their server becoming infested with rogue .NET code!

Thanks for your registration, follow us on our social networks to keep up-to-date