Monday, January 12, 2009

SQL CLR - Assembly.Load()

The jump from SQL Server 2000 to SQL Server 2005 was huge at all levels (fortunately, I didn’t got the chance to work with previous versions of the SQL Server). One of the many features introduced with SQL Server 2005 was the SQL CLR. The idea is to allow managed code to be hosted by and executed in the Microsoft SQL Server environment.Translating, you can write your stored procedures, user defined functions, trigger and so on in any CLS compliant language and use them in any standard SQL statement as if they were created using T-SQL. You can even define new types and they will be recognized in SQL.

Now, accordingly to Microsoft, the SQL Server itself hosts the .NET Framework runtime (resources management and so on are no longer satisfied by the undererlying OS). So, one other question arises: under what security permissions does it run?

Quoting Microsoft, "The CLR supports a security model called Code Access Security (CAS) for managed code. In this model, permissions are granted to assemblies based on the identity of the code (e.g.: signed assemblies), as opposed to the identity of the user or process running the code." When moving to a CLR hosted environment however, this implies that "the ability to generate managed code dynamically is not supported inside the CLR hosted environment in SQL Server. Such code would not have the CAS permissions to run and would therefore fail at run time."

So, translating Microsofts’ fancy words: what happens when dynamically loading an assembly? Bum...
Loading an assembly — either explicitly by calling the System.Reflection.Assembly.Load() method or implicitly through the use of Reflection.Emit namespace — is not permitted. Dynamic assembly loading is always disallowed under SQL CLR, even using UNSAFE permissions. If your assembly uses any of these features, the DMBS will yield an error message when running the code:

(click to enlarge)

This isn't a big issue, though. Mainly because the Database is one of the most important components of any system and you'll want it to be as "well defined" and secure as possible.