SQL Server is a host of the .NET runtime. In versions prior to CLR 2.0, multiple hosts already existed to run .NET applications; 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 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, Ever) database that makes use of CLR code creates its own applialtion donutin. This guarantees that CLR code from one database doesn’t have any influence on any other database. “
.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 as a .NET runtime host defines additional permission levels safe external, and unsafe.
- Safe – With the safety level safe, only computational CLR classes can be used, The assembly is able to perform only 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 CLRcode.
- External – With the safety level extemat 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 be installed only by a database administrator.
To enable custom .NET code to be run within SQL Server, the CLR must be enabled with the sp_configure stored procedure:
sp_configure [clr enabled], 1
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 us\;tlly 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, the assembly can be installed with the CREATE ASSBMBLY command. With this command, the name of the assembly used in SQL Server, the path to the asaembly, and the safety level can be applied:
CREATEASSEMBLYmylibrary FROMc:/PrOCSharp/SqlServer/Demo.dll WITH.PERMISSIONSET = SAFE \
With Visual Studio 2008, the permission level of the generated assembly can be defined with the Database properties of the project, as shown in Figure 30-1.