SQL Server 2005 includes the Windows .NET Framework 2.0. Including
the .NET Common Language Runtime (CLR) environment under the SQL Server
2005 runtime process isn't a trivial change. Had the SQL Server 2005
developers handled this change incorrectly, SQL Server 2005 could have
been totally destabilized. Let's look at the risks that the SQL Server
2005 developers mitigated, then discuss why integrating the CLR in SQL
Server 2005 was worth the trouble.
The SQL Server 2005 developers had to mitigate stability and security
risks when they were integrating the CLR. Before I discuss these risks,
though, let me clarify one point upfront: Having the CLR running within
SQL Server 2005 doesn't open any new potential security holes in SQL
Server 2005. There's nothing inherent in the CLR that creates or exposes
new security problems.
With that said, let's use a simplified scenario to examine the
stability and security risks that the SQL Server 2005 developers
alleviated. In Windows, the CLR runs under Microsoft IIS. Suppose
that, for some reason, the CLR locked up. Under IIS, the most
likely solution is to simply restart IIS. As with any problem, this
solution isn't ideal, but all things considered, even this worst-case
scenario is relatively painless. At most, the reset event causes the
loss of a few user sessions. On a load-balanced site, the reset event
wouldn't even be noticed by anyone but the person whose request had
caused the lockup.
Now let's apply that same scenario in SQL Server 2005. If the CLR
were to lock up under SQL Server 2005, restarting might be the only
solution. However, for a database server, unplanned downtime of this
sort isn't acceptable. Even the best solutions for failover aren't
designed to support a restart. The type of load balancing supported by
SQL Server enterprise installations would never accept this type of
risk.
So, the SQL Server 2005 development team worked with the .NET
Framework CLR team to prevent this scenario from occurring. In short,
these teams ensured that SQL Server 2005's existing mechanisms for
controlling a transaction and tracking deadlocked processes could take
control when something bad happened. This required an evaluation of .NET
method calls to ensure that SQL Server 2005's built-in reliability
wouldn't be compromised. The goal wasn't just to review the calls' code
but to determine which of those calls could be safely controlled within
the framework of a CLR that might not be able to be reset for years.
Using the .NET development languages, the CLR can make calls to
either the file system or an XML Web service. Thus, a process internal
to SQL Server 2005 could suddenly access code that wasn't running within
SQL Server. The problems that might result are beyond any runtime
process's ability to control.
Fortunately, calls to external resources such as a file system or XML
Web service are associated with specific .NET capabilities, so the SQL
Server 2005 developers used a common model to expose .NET capabilities
in SQL Server 2005. The basic model has three Code Access Security (CAS)
tiers. The lowest tier is the SAFE permission set, which allows access
to those .NET capabilities that don't expose SQL Server 2005 to any
risks greater than the risks associated with using native T-SQL. Thus,
this permission set should trigger a green flag in your mind. In fact,
I recommend that you use only the SAFE permission set when taking
advantage of the .NET technology in SQL Server 2005.
The next CAS tier is the EXTERNAL_ACCESS permission set, which allows
access to those .NET capabilities that call the registry and file
system. By definition, these capabilities trigger security holes, which
under some unknown condition might cause an unresolvable problem
external to the database or provide a path that might lead to system
damage. The reality is these capabilities should be managed in business
objects, so this permission set should trigger a yellow warning flag in
your mind. There will be some developers who feel a need to use these
capabilities. Although these capabilities can be used safely, doing so
implies some error of logic in the project's overall architecture and
design.
The third and final CAS tier should trigger a red flag in your mind.
This tier is known as the UNSAFE permission set, but you could easily
justify calling it UNSECURED or just plain INSANE. This tier allows for
full trust of managed code and permits calls to unmanaged code. Only the
database system administrator can configure this permission set, but any
one doing so should probably be asked to find a new job. The best
analogy I have is how you respond to stop signs when driving. There's
nothing preventing you from running a stop sign, and you could run every
stop sign for weeks without having an accident. But rest assured, if you
keep running stop signs, at some point you're going to have an accident.
And that accident will likely cause you a lot of pain.
So why did the SQL Server 2005 developers go to so much trouble to
integrate the .NET technology? According to Microsoft, they did it to
gain the ability to create user-defined types (UDTs), aggregates, and
better performing user-defined functions (UDFs). Underlying those
features is the ability to define and better manipulate custom data
structures. More specifically, at the core is the ability to manipulate
XML within the database. The idea behind having the .NET CLR running
under SQL Server 2005 is to support the complex data structures you'll
be able to create by storing XML data within SQL Server 2005.
End of Article
Register
