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.
