DAT318: SQL Server 2005 CLR Integration

  • Article

I've already blogged about this topic in some depth (1 2 3 4), so I've highlighted a few notes of interest from Ramachandran and Makesh's session:

Restricting the CLR
Not all of the Base Class Libraries in the .NET Framework are available in the database. Functionality "not applicable" to the database is disallowed. Most of System.* is available, but many non-applicable services are not supported, such as System.Windows.Forms, System.Drawing, System.Web. This is achieved through a "fusion loader hook" in CLR hosting. How does that work? When you look up an assembly today, the CLR finds it using Fusion. SQL Server 2005 intercepts that search for the hosted environment and loads it from the database itself instead.

Even in supported assemblies, some APIs are not available in SQL. This is achieved through a new HostProtection attribute in the CLR (this is extensible by third-party libraries). You can reflect on an assembly and list these methods, and a full list will be available in the SQL Server 2005 documentation. Potentially unreliable constructs are disabled, such as thread creation, socket listening, and finalizers.

Tips for using the In-Proc Provider

  • Use SqlPipe.Execute() instead of cmd.ExecuteReader(). This allows you to simply stream back data without marshalling the results into the managed environment.
  • Use SqlDataReader instead of T-SQL cursors: it's the fastest way to enumerate rows.
  • Use SqlExecutionContext for static SQL patterns to improve performance.

Security Tips for DBAs

  • Use sp_configure 'clr enabled' to enable the CLR hosting in the engine - it's off by default
  • Use the CREATE ASSEMBLY permission to control the creation of assemblies
  • Use the REFERENCES permission to protect schema binding on assemblies
  • Use the EXECUTE permission to regulate who can execute routines
  • Catalog view security works as expected
  • Use the EXTERNAL ACCESS permission to regulate who can create external access code
  • Recommendations: use safe assemblies - this is the default and is closest to the T-SQL model. If you are going off the box, you need to question the impersonation strategy. By default, SQL does not impersonate. But you can use EXECUTE AS to specify a specific SQL context for impersonation.
  • Avoid unsafe assemblies - they can compromise system reliability and correctness.