.Net Runtime Host

SQL Server 2005 is a new host of the .NET runtime. In versions prior to .NET 2.0, the .NET runtime can be hosted with Web applications from ASP.NET and Windows Forms controls can run in the Internet Explorer runtime host.

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.

Note

You can read more about application domains in Chapter 15, "Assemblies."

.NET 1.0 already had a well-thought-out security environment with evidence-based security. 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.

Note

You can read more about evidence-based security in Chapter 16, ".NET Security."

With the safety level safe, only computational CLR classes can be used. 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. With the safety level external it is possible to access the file system or other databases with client-side ADO.NET. 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 20-1.

Figure 20-1