HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME HOST When Payroll routines and HR routines are declared (in assemblies of analogous names), they each reference EmployeeRoutinesas follows. SQL Server reflection determines that PayrollRoutines references EmployeeRoutines EmployeeRoutines is cataloged too CREATE ASSEMBLY PayrollRoutines FROM \zmv43typesPayrollRoutines.DLL GO SQL Server reflection determines that HRRoutines references EmployeeRoutines this sets up another reference to EmployeeRoutines CREATE ASSEMBLY HRRoutines FROM \zmv43typesHRRoutines.DLL GO With the previous declarations, neither the Payroll programmers nor the HR programmers can change or drop the EmployeeRoutines without the consent of the other. We ll look at how you d set up the permissions for this in Chapter 6, the security chapter. Assemblies and SQL Schemas Who Owns Assemblies (Information Schema) Assemblies, like other SQL Server database objects, are the property of the user that catalogs them using CREATE ASSEMBLY, the schema owner. This has security repercussions for the users that wish to use the procedures, triggers, and types within an assembly. Though we ll go over all the security details in Chapter 6, we d like to discuss execution context here. In addition, we ll see where exactly in the system tables (and the SQL:1999 INFORMATION_SCHEMA) information about assemblies is stored. System Metadata Tables and INFORMATION_SCHEMA Information about assemblies as well as the assembly code itself and the dependencies is stored in the system metadata tables, which, in general, store information about SQL Server database objects, such as tables and indexes. Some metadata tables store information for the entire database instance and exist only in the MASTER database; some are replicated in every database, user databases as well as MASTER. The names of the tables and the information they contain are proprietary. System metadata tables are performant, however, because they reflect the internal data structures of SQL Server. In the big rewrite that took place in SQL Server 7, the system metadata tables remained intact. In SQL Server 2005, the metadata tables

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

ASSEMBLY DEPENDENCIES Table 2-1: Code Requirements and Constructs

ASSEMBLY DEPENDENCIES Table 2-1: Code Requirements and Constructs and SQL Server Safety Levels Feature or Construct SAFE EXTERNAL_ACCESS UNSAFE Shared state NN Y Synchronization NN Y Thread.Create N N Y Class constructors Y Y Y Register for static events NN Y Finalizers NN Y Debug.Break N N N ThreadException.EventHandler N N N AppDomain.DomainUnloadEvent N N N PInvoke N N Y IJW (note: /clr:pureand /clr are supported) N N N PE Verification Y Y Y Metadata Verification Y Y Y IL Verification Y Y Y Non-read-only static fields/properties N N Y Code must be type-safe Y Y N HPA ExternalProcessMgmt N N Y HPA ExternalThreading N N Y HPA Synchronization N N Y HPA SharedState N N Y HPA SelfAffectedProcessMgmt N N Y HPA SelfAffectedThreading N N Y

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME HOST EXTERNAL_ACCESS Specifying EXTERNAL_ACCESS gives code the ability to access external system resources. Like SAFE, an assembly cataloged with the EXTERNAL_ACCESS permission set cannot compromise the security, reliability, or performance of SQL Server. The registry, network file system, external databases, and environment variables are available through the managed code APIs, but EXTERNAL_ACCESS code cannot use COM-callable wrappers or PInvoke, or create threads. UNSAFE UNSAFE code is not restricted in any way, including using reflection and unmanaged code. Since using UNSAFE could compromise SQL Server, only users that are members of the sql_admins role can even permit UNSAFE code. Usage permissions are described in Chapter 6, Security. Although it seems unwise to even permit UNSAFE code to execute, UNSAFEcode is really no more unsafe than an extended stored procedure. In addition to CAS permission sets, which will be discussed in Chapter 6, there is a series of .NET code requirements and coding constructs that can be used or not based on safety level. Table 2-1 shows a list of .NET constructs and their permitted usage in the three safety levels. Assembly Dependencies When Your Assemblies Use Other Assemblies One very important point that we ve only touched on so far is that assemblies are scoped at a database level. Since each database is its own App Domain, as mentioned previously, and assemblies may not be shared among AppDomain, they must be loadable in each AppDomain. However, you often might want to share an assembly within a single database. Examples would be statistical packages or spatial data types that are referenced by many user-defined assemblies in multiple databases. System utilities, such as collection classes, and user-defined class libraries can be used. To ensure that a library that s being referenced by multiple assemblies is not dropped when a single library that references it is dropped, SQL Server will reflect on the assembly when CREATE ASSEMBLYis executed, to determine the dependencies. It automatically catalogs these dependencies in the SQL metadata tables. As an example, let s assume that both the Payroll department and the HR department reference a common set of formulas to calculate an employee s years of service. This library is called EmployeeRoutines.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

WHERE THE CODE LIVES completes, SQL Server never

WHERE THE CODE LIVES completes, SQL Server never accesses the location it loaded the assembly from again. If the file or bits that CREATE ASSEMBLY points to does not contain an assembly manifest, or if the manifest indicates that this is a multifile assembly, CREATEASSEMBLYwill fail. SQL Server will both verify that the assembly code is type-safe (except if PERMISSION_SET=UNSAFE) and validate the code when it is cataloged. This not only saves time, since this is usually done by the runtime during the JIT process (at first load), but also ensures that only verifiable code is cataloged into SQL Server. Unverifiable code will cause CREATEASSEMBLYto fail. What happens during validation depends on the value of the PERMISSION_SET specified. The default PERMISSION_SET is SAFE. The permission sets control code access security permissions when the code executes, but also are meant to enforce semantics with respect to what kind of calls can be made. CREATE ASSEMBLYuses reflection to ensure that you are following the rules. There are three distinct PERMISSION_SETs. SAFE This is the default permission set. An assembly cataloged with the SAFE permission set cannot compromise the security, reliability, or performance of SQL Server. SAFE code cannot access external system resources such as the registry, network, file system, or environment variables; the only CLR permission that SAFEcode has is execution permission. SAFE code also cannot access unmanaged code through runtime-callable wrappers or use PInvoke to invoke a native Windows DLL. SAFEcode can make data access calls using the current context but cannot access data through the SqlClient or other data providers. SAFE code cannot create threads or otherwise do any thread or process management. Attempting to use forbidden methods within a SAFE assembly will result in a security exception. The following example shows the security exception produced when a method in a SAFE assembly tries to connect to the Web using System.Net.WebRequest. Msg 6522, Level 16, State 1, Line 2 A CLR error occurred during execution of GetFromWeb : System.Security.SecurityException: Request for the permission of type System.Net.WebPermission, System, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed. at System.Security.CodeAccessSecurityEngine.CheckHelper (PermissionSet grantedSet, PermissionSet deniedSet, CodeAccessPermission demand, PermissionToken permT…

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME HOST where: assembly_name Is the name of the assembly; the name should be a valid SQL Server identifier. client_assembly_specifier Specifies the local path or the network location (as UNC Path) of the assembly being loaded including the file-name of the assembly. manifest_file_name Specifies the name of the file that contains the manifest of the assembly. SQL Server will also look for the dependent assemblies of this assembly, if any, in the same location that is, the directory specified by client_assembly_specifier. PERMISSION_SET={SAFE|EXTERNAL_ACCESS|UNSAFE } Changes the .NET Code Access Permission Set property granted to the assembly. We ll have more to say about this later in the chapter and in Chapter 6. assembly_bits Supplies the list of binary values that constitute the assembly and its dependent assemblies. If assembly_bitsis specified, the first value in the list should correspond to the root- level assembly; that is, the name of the assembly as recorded in its manifest should match the assembly_name. The values corresponding to the dependent assemblies can be supplied in any order. varbinary_literal Is a varbinaryliteral of the form 0x. varbinary_expression Is an expression of type varbinary. When you catalog an assembly using CREATE ASSEMBLY, the symbolic name you assign to the assembly need not agree with the name in the assembly manifest. This allows you to catalog multiple versions of the assembly or the same assembly that differ in version number or culture specifier. The current NT user s identity is used to read the assembly file from the appropriate directory. Therefore, the user must have permission to access the directory where the assembly is located. You must be logged in to SQL Server using a SQL Server account defined with integrated security to create an assembly; attempting to use CREATE ASSEMBLY while you are logged in as a SQL Server security account will fail. Note that using CREATE ASSEMBLY copies the assembly s bits into the database and stores them physically in a system table (sys.assembly_files). There is no need for SQL Server to have access to the file system directory to load the bits the next time SQL Server is started; once the CREATE ASSEMBLY statement

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

WHERE THE CODE LIVES Setting permission sets on

WHERE THE CODE LIVES Setting permission sets on assemblies is shown in the section on CREATE ASSEMBLYlater in the chapter. A HostProtectionAttributediffers from a normal security LinkDemand in that it is applied at the discretion of the host in this case, SQL Server. Some hosts, like Internet Explorer, can choose to ignore the attribute, while others, like SQL Server, can choose to enforce it. If the host chooses to ignore the HostProtectionAttribute, the LinkDemandevaporates that is, it s not executed at all. All the Framework class libraries permitted to load in SQL Server have been decorated with HostProtectionAttributes. In conjunction with code access security (discussed in Chapter 6), HostProtectionAttributes produce a SQL Server-specific sandbox based on permission set that ensures the code running with any permission set other than UNSAFE cannot cause instability or lack of scalability in SQL Server. Where the Code Lives Storing .NET Assemblies (CREATE ASSEMBLY) A .NET assembly is cataloged in a SQL Server database by using the CREATE ASSEMBLYstatement. The following lines of code define an assembly to SQL Server and assign it the symbolic name SomeTypes. CREATE ASSEMBLY SomeTypes FROM \zmv43typesSomeTypes.dll This not only loads the code from the file, but assigns a symbolic name to it in this case, SomeTypes. The code can be loaded from a network share or from a local file system directory, and it must be a library (DLL) rather than directly executable from the command line (EXE). No special processing of the code is needed beyond normal compilation; SomeTypes. dllis a normal .NET assembly. SomeTypes.dllmust contain an assembly manifest, and although a .NET assembly can contain multiple physical files, SQL Server does not currently support multifile assemblies. The complete syntax for CREATE ASSEMBLYfollows. CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM { < client_assembly_specifier > | < assembly_bits > [,...n] } [ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ] < client_assembly_specifier > :: = \machine_nameshare_name[path]manifest_file_name < assembly_bits > :: = { varbinary_literal | varbinary_expression }

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME HOST own. This has the capability to cause memory corruption and violate security, which is potentially more insidious. Since code runs in its own AppDomain and is prevented from accessing memory except through the runtime, managed code is an order of magnitude safer than the extended stored procedures of the past. Note that it is possible to run unsafe .NET code inside SQL Server, but this code must be defined using the UNSAFE option in the CREATE ASSEMBLY DDL statement, with no constraints on it. It is worth noting that UNSAFE assemblies may only be cataloged by SQL Server administrators. .NET code in the Managed C++ compiler without the /safe compile switch and C# code that uses the unsafe keyword must use the UNSAFE declaration in the DDL. In addition to analyzing your code when it is cataloged to SQL Server, there are also runtime safety checks. Host Protection Attributes In an attempt to make .NET code more reliable to run inside hosts like SQL Server, the Host Protection Attribute was invented. The System. Security.Permissions.HostProtectionAttributeclass can be applied to classes or methods to indicate that the class or method contains functionality that could cause instability in the host when invoked by user code. HostProtectionAttributehas a series of properties that can be set to indicate different potentially dangerous functionality types. The current set of properties is as follows: ExternalProcessMgmt ExternalThreading SelfAffectingProcessMgmt SelfAffectingThreading MayLeakOnAbort Resources SecurityInfrastructure SharedState Synchronization UI Applying the HostProtectionAttribute to a class or method creates a LinkDemand that is, a demand that the immediate caller have the permission required to execute the method. The LinkDemand is checked against the permission set of the assembly and/or the procedural code.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

SAFE CODE variables, because each AppDomain must have

SAFE CODE variables, because each AppDomain must have its own copy of static variables and this requires the runtime to add a level of indirection. There are four domain-neutral code settings. 1. No assemblies are domain neutral. 2. All assemblies are domain neutral. 3. Only strongly named assemblies are domain neutral. 4. The host can specify a list of assemblies that are domain neutral. SQL Server 2005 uses the fourth option it will only share a set of Framework assemblies. It doesn t share strongly named user assemblies, because it means user assemblies that happen to be strongly named can never be unloaded. AppDomains do not have a concept of thread affinity; that is, all App Domains share the common CLR thread pool. This means that although object instances must be marshaled across AppDomains, the marshaling is more lightweight than COM marshaling, for example, because not every marshal requires a thread switch. This also means it is possible to delegate the management of all threads to SQL Server while retaining the existing marshaling behavior with respect to threads. Safe Code How the Runtime Makes It Safer to Run Foreign Code If you ve used SQL Server for a while, you might be thinking at this point, We ve always been able to run code other than Transact-SQL inside the SQL Server process. OLE DB providers can be defined to load into memory. Extended stored procedures are written in C++ and other languages. What makes this scenario different? The difference is that managed code is safe code. Except in the special UNSAFEmode, code is verified by the runtime to ensure that it is type-safe and validated to ensure that it contains no code that accesses memory locations directly. This all but eliminates buffer overruns, pointers that point to the wrong storage location, and so on. The unmanaged extended stored procedure code does run under structured exception handling. You cannot bring down a SQL Server process by branching to location zero, for example. However, since an extended stored procedure runs directly in memory shared with SQL Server, it is possible for the procedure to access or change memory that it does not

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME HOST Although there may be many AppDomains in a process, AppDomains cannot share class instances without marshaling. The relationship between a process and its AppDomains is shown in Figure 2-2. SQL Server does not use the default AppDomain for database processing, although it is used to load the runtime. Exactly how AppDomains are allocated in SQL Server 2005 is opaque to and not controllable by the user or DBA; however, by observation, in the beta version of SQL Server 2005, it can be determined that a separate AppDomain will be created for each database for running that database s code. Executing the system function master.sys.fn_appdomains() shows the AppDomains in the SQL Server process when more than one combination is in use. In the beta 1 version, the AppDomains were named databasename.number for example, AdventureWorks.2. This effectively isolates each database s user code from the others, albeit at the cost of more virtual memory. In beta 2, App Domains may be allocated based on the identity of the user owning the assembly, possibly resulting in more AppDomains, but isolating each assembly owner s code. This effectively prevents using reflection to circumvent SQL Server permissions without the overhead of intercepting each call. The runtime-hosting APIs also support the concept of domain-neutral code. Domain-neutral code means that one copy of the Just-In-Time compiled code is shared across multiple AppDomains. Although this reduces the working set of the process because only one copy of the code and supported structures exists in memory, it is a bit slower to access static A Windows NT Process Default Domain AppDomain 1 AppDomain 2 CLR Each AppDomain is separate. Unmanaged code runs isolated from any AppDomain. Figure 2-2: AppDomains in a .NET Process

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

LOADING THE RUNTIME PROCESSES AND APPDOMAINS model and enforces

LOADING THE RUNTIME PROCESSES AND APPDOMAINS model and enforces user permissions.1 For ensuring the integrity of user- permissions defined in the database, we depend on the principal execution context of the stored procedure or user-defined function in combination with database roles. See Chapter 6 for the specifics of security enhancements. Loading the Runtime Processes and AppDomains We ve spoken of AppDomains quite a bit in previous paragraphs. It s time to describe exactly what they are and how SQL Server uses them. In .NET, processes can be subdivided into pieces known as application domains, or AppDomains. Loading the runtime loads a default AppDomain; user or system code can create other AppDomains. AppDomains are like lightweight processes themselves with respect to code isolation and marshaling. This means that object instances in one AppDomain are not directly available to other AppDomains by means of memory references; the parameters must be marshaled up and shipped across. In .NET, the default is marshal-by-value; a copy of the instance data is made and shipped to the caller. Another choice is marshal-by-reference, in which the caller gets a locator or logical pointer to the data in the callee s AppDomain, and subsequent use of that instance involves a cross AppDomain trip. This isolates one AppDomain s state from others. Each process that loads the .NET Framework creates a default App Domain. From this AppDomain, you can create additional AppDomains programmatically, like this. public static int Main(string[] argv) { // create domain AppDomain child = AppDomain.CreateDomain( dom2 ); // execute yourapp.exe int r = child.ExecuteAssembly( yourapp.exe ,null,argv); // unload domain AppDomain.Unload(child); return r; } 1 For more information on the stack walk process, see Keith Brown s article Security in .NET: Enforce Code Access Rights with the Common Language Runtime in the February 2001 issue of MSDN Magazine.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services