.NET Runtime Host

SQL Server 2005 is a new host of the .NET runtime. In versions prior to .NET 2.0, already multiple hosts existed to run .NET application; for example,. a host for Windows Forms and a host for ASP.NET. Internet Explorer is another runtime host that allows running Windows Forms controls.

SQL Server 2005 allows running a .NET assembly inside the SQL Server process, where it is possible to create stored procedures, functions, data types, and triggers with CLR code.

Every database that makes use of CLR code creates its own application domain. This guarantees that CLR code from one database doesn’t have any influence on any other database.

Tip 

You can read more about application domains in Chapter 16, “Assemblies.”

.NET 1.0 already had a well-thought-out security environment with evidence-based security. However, this security environment was not enough for mission-critical databases - .NET needed some extensions. SQL Server 2005 as a .NET runtime host defines additional permission levels: safe, external, and unsafe.

Tip 

You can read more about evidence-based security in Chapter 19, “.NET Security.”

• Safe - With the safety level safe, only computational CLR classes can be used. The assembly is only able to perform local data access. The functionality of these classes is similar to a T-SQL stored procedure. The code access security defines that the only .NET permission is execution of CLR code.

• External - With the safety level external it is possible to access the network, file system, registry, or other databases with client-side ADO.NET.

• Unsafe - The safety level unsafe means that everything can happen, because this safety level allows you to invoke native code. Assemblies with the unsafe permission level can only be installed by a database administrator.

To enable custom .NET code to be run within SQL Server 2005, the CLR must be enabled with the sp_configure stored procedure:

  sp_configure [clr enabled], 1 reconfigure 

With .NET 2.0, the attribute class HostProtectionAttribute in the namespace System.Security .Permissions was invented for better protection of the hosting environment. With this attribute, it is possible to define if a method uses shared state, exposes synchronization, or controls the hosting environment. Because such behavior is usually not needed within SQL Server code (and could influence the performance of the SQL Server), assemblies that have these settings applied are not allowed to be loaded in SQL Server with safe and external safety levels.

For using assemblies with SQL Server 2005, the assembly can be installed with the CREATE ASSEMBLY command. With this command, the name of the assembly used in SQL Server, the path to the assembly, and the safety level can be applied:

  CREATE ASSEMBLY mylibrary FROM c:/ProCSharp/SqlServer2005/Demo.dll    WITH PERMISSION SET = SAFE 

With Visual Studio 2005, the permission level of the generated assembly can be defined with the Database properties of the project, as shown in Figure 27-1.

Figure 27-1