Co-Sponsored by:   Latest Features  • Conflict Resolution  • Cause and Effect  • Why Are You Still Single?  • Future Features of ASP.NET  • WCF Proxies: To Cache or Not to Cache? Article Rating Rate this article on a scale from 0 to 5 5 Best 4   3   2   1   0 Worst Email Tell a friend about this article!

Secure ASP.NET

Secure SQLCLR Code in SQL Server 2005

By Don Kiely

Most non-trivial Web applications these days are database applications at their heart. It’s been a long time since I’ve written a substantial application that didn’t use data in some significant way, and, in my little corner of the Web World, that data is most often in SQL Server. That makes the security of the data a critical part of my ASP.NET applications.

One of the most useful tools Microsoft introduced with SQL Server 2005 was the ability to write .NET code that executes in the database server process. Often called SQLCLR, it opens up a whole realm of new possibilities for hosting the code that makes up an application in every tier, even if it is a classic client/server application. No longer are developers shackled to Transact-SQL when we need complex code to run on the server. Best of all, you can make use of SQLCLR programming objects — including stored procedures, functions, and user-defined types — in any kind of application, both managed and unmanaged.

Many system admins were scared to death of the possibilities of SQLCLR, as it allows developers unhip to database security to write in-process code within the secure confines of the database. But SQL Server 2005 includes many features that make it a reasonably safe thing to do. I’m going to cover two of the most important: SQLCLR permissions and permission levels.

SQLCLR Permissions

The permissions scheme in SQL Server 2005 is far more granular than earlier versions of SQL Server, letting you match more principals to far more permissions than ever before. The permissions for SQLCLR code are just as granular as code accesses database objects. There are three primary permissions required just to install and run the code, before you even consider doing anything with any data or objects:

• CREATE ASSEMBLY is required to run the CREATE ASSEMBLY statement that installs a SQLCLR assembly into a database.
• Non-admin users and those who don’t own the code must have the EXECUTE permission on a code module in order run the code. Sysadmins can run any code.
• The owner of the code must have the REFERENCES permission to reference other objects, such as with foreign keys and create view with schemabinding.

If this was the end of the security story, SQLCLR would be an end-run around overall SQL Server security. But these permissions are just the opening gauntlet. The user or login that causes SQLCLR code to execute also must have the usual SELECT, INSERT, DELETE, or UPDATE permissions on any database objects used by the code.

The critical thing to understand is that there is nothing in how the SQLCLR is hosted within SQL Server 2005 that circumvents the need for these database and server permissions, depending on what the code does to the data. Furthermore, the permission checks hook into the new execution context feature in SQL Server 2005, so that when defining a SQLCLR stored procedure or function you can specify the execution context of the code using the EXECUTE AS clause.

SQLCLR Permission Levels

SQL Server provides three permission set levels when you load an assembly into a database using the CREATE ASSEMBLY statement: SAFE, EXTERNAL_ACCESS, and UNSAFE. These levels are bundles of code-access security (CAS) permissions that are enforced by the common language runtime (CLR).

Here is a typical statement that installs an assembly within the MyCodeDLL.dll file and gives it the SAFE permission set:

CREATE ASSEMBLY MyCode

    FROM ‘C:\MyCodeDLL.dll’

    WITH PERMISSION_SET = SAFE

GO

SAFE

SAFE is the default permission set. It grants just enough permissions to execute the code, do internal computations that don’t access external resources, and access the data and objects within the host SQL Server instance. SAFE code cannot access external resources, so it can’t read or write disk files, can’t access any other SQL Server instances, or read or write to the registry. The code must also be verifiably type safe, which helps protect against various attacks — including buffer overruns.

SAFE code is the most reliable and secure SQLCLR code. It can do pretty much whatever code written in T-SQL can do within the database and server instance, and is the level at which the vast bulk of SQLCLR code should execute. It grants this very short list of CAS permissions:

EXTERNAL_ACCESS

The EXTERNAL_ACCESS permission set is a big step up from SAFE in that it allows restricted access to resources outside the SQL Server instance, including disk files, the data and objects in other SQL Server instances, environment variables, and some parts of the registry. Anything outside the safe confines of the SQL Server instance in which the SQLCLR code is running requires at least this permission level. EXTERNAL_ACCESS grants these permissions, quite a few more than the SAFE level:

Keep in mind that the CLR can’t grant any permissions that go beyond what the security context has in Windows. So the unrestricted FileIOPermission might look a little scary, but if the code is running with an account that can’t access a file, the SQLCLR code won’t be able to access the file either, even if it seems to have the permission to do so.

UNSAFE

The UNSAFE permission set is the SQLCLR equivalent of full trust, in which the CLR suspends all permissions checking. It receives a single, unrestricted SecurityPermission permission, which is the CLR’s way of granting full trust. In essence, assigning this security level tells the CLR to not pay any attention to security. This means that UNSAFE code can call unmanaged code, such as COM components and the raw Win32 API. It is still subject to operating system permissions of the service account, but the CLR won’t restrict its ability to access any resources.

SQLCLR is a great tool to consider as part of ASP.NET applications, but like every component in a secure application, you have to sweat the details. My thanks to my fellow developer security MVP and new mother Nicole Calinoiu for doing the hard work to determine the CLR permissions in each SQLCLR level.

Don Kiely, MVP, MCSD, is a senior technology consultant, building custom applications as well as providing business and technology consulting services. His development work involves tools such as SQL Server, Visual Basic, C#, ASP.NET, and Microsoft Office. He writes regularly for several trade journals, and trains developers in database and .NET technologies. You can reach Don at mailto:donkiely@computer.org and read his blog at http://www.sqljunkies.com/weblog/donkiely/.