What is CLR ?

The common language run time(CLR) is the virtual machine component of Microsoft’s .NET framework and is responsible for managing the execution of .NET programs. In a process known as just-in-time (JIT) compilation, the CLR compiles the intermediate language code known as Common Intermediate Language (CIL)into the machine instructions that in turn are executed by the computer’s CPU. The CLR provides additional services including memory management, type safety and exception handling. All programs written for the .NET framework, regardless of programming language, are executed by the CLR. It provides exception handling, Garbage collection and thread management.

The CLR is Microsoft’s implementation of the Common Language Infrastructure (CLI) standard.

What does it means when CLR is integrated into SQL server ?

• CLR was introduced in SQL server 2005
• CLR integration provides you with the flexibility to define stored procedures, functions, triggers, data types, andother database objects using either Transact-SQL or any .NET Framework language , So you can extend the functionality SQL server programmable objects by using any .NET framework language

CLR and Manage code

The .NET Framework includes a compiler component for each .NET-compliant language. This component compiles the source code written in the specific high-level
language to the lower-level Microsoft Intermediate Language (MSIL). When the application is running, the CLR translates MSIL to native machine instructions. This
is done by a component known as just-in-time ( JIT) compilation. It is important to understand that no matter which .NET language you develop your application in,
it will be compiled to the one common language—MSIL.
The CLR is responsible for executing MSIL code. The CLR also manages the code that runs within it by providing the code with runtime services.
The CLR manages code execution by providing mandatory stability and security-checking engines, as well as resource management. For example, the
Garbage Collection service provided by the CLR ensures optimal memory management. Thread management provides parallel processing capabilities. The Base
Class Library support, error management, and debugging services create a consistent development environment for all .NET languages.
Code that runs within the CLR is known as managed code , as the CLR provides it with management services like those mentioned above. The term is often used to
distinguish between .NET code and native code (for example, code written using Visual Basic 6 or C++). Native code runs outside the CLR and is referred to as unmanaged code . Unmanaged code is not subject to CLR services, like code access security. Within SQL Server, all objects that use CLR integration are written as managed code. Extended stored procedures (XPs) are unmanaged code and are written using C++.

Base class library

The Base Class Library (BCL) is a standard library available to all languages using the .NET Framework. .NET includes the BCL in order to encapsulate a large number of common functions, such as file reading and writing, graphic rendering,database interaction, and XML document manipulation, which makes the programmer’s job easier. It is much larger in scope than standard libraries for most other languages, including C++, and is comparable in scope and coverage to thestandard libraries of Java. The BCL is sometimes incorrectly referred to as the Framework Class Library (FCL), which is a superset including the Microsoft.* namespaces.

Standardized namespaces

These are the namespaces that are standardized as of the ECMA 335 and ISO/IEC 23271:2006 standards.

System 

This namespace includes the core needs for programming. It includes base types like String, DateTime, Boolean, and so forth, support for environments such as the console, math functions, and base classes for attributes, exceptions, andarrays.

System.Collections 

Defines many common containers or collections used in programming, such as lists, queues, stacks, hashtables, and dictionaries. It includes support for generics.

System.Diagnostics 

Provides the ability to diagnose applications. It includes event logging, performance counters, tracing, and interaction with system processes.

System.Globalization 

Provides help for writing internationalized applications. “Culture-related information, including the language, the country/region, the calendars in use, [and] the format patterns for dates, currency, and numbers” can be defined.

System.IO 

Enables reading from and writing to different streams, such as files or other data streams. Also provides a connection to the file system.^[6]

System.Net 

Provides an interface “for many of the protocols used on networks today”, such as HTTP, FTP, and SMTP. Secure communication is supported by protocols such as SSL.

System.Reflection 

Provides an object view of types, methods, and fields; and “the ability to dynamically create and invoke types”. It exposes the API to access the Reflective programming capabilities of CLR.

System.Runtime 

Allows management of the runtime behavior of an application or the CLR. Some of the included abilities are interoperable with COM or other native code, writing distributed applications, and serializing objects into binary or SOAP.

System.Security 

“Provides the underlying structure of the common language runtime security system.” This namespace allows security to be built into applications based on policy and permissions. It provides services such as cryptography.

System.Text 

Supports various encodings, regular expressions, and a more efficient mechanism for manipulating strings (StringBuilder).

System.Threading 

Helps facilitate multithreaded programming. It allows the synchronizing of “thread activities and access to data” and provides “a pool of system-supplied threads.”

System.Xml 

“Provides standards-based support for processing XML,” including reading, writing, schemas, serialization, searching, and transforming.

But not all base classes are supported by clr in sql server , check this post for the list of supported name spaces in SQL server

https://sqljunkieshare.com/2012/02/22/list-of-supported-librariesnamespaces-by-clr-integration-in-sql-server-2008r2-and-2012/

Even if the class you wish to call is in an unsupported assembly, it doesn’t mean you cannot use it. Any custom assembly that you write yourself is unsupported,
unless you can persuade the makers of SQL Server to test it for you. This unlikely event notwithstanding, you will have to use the unsupported library at your ownrisk. Unsupported libraries are not loaded by SQL Server by default, and therefore you have to register them explicitly.  When using functionality from an unsupported assembly, you are responsible for testing it and ensuring it performs well, without affecting the stability of SQL Server.

What is an assembly? and  what is this to do with CLR Stored procedures, User Defined Functions, Triggers, User Defined Data Types ?

Assemblies are the building blocks of .NET Framework applications; they form the fundamental unit of deployment, version control, reuse, activation scoping, and security permissions. An assembly is a collection of types and resources that are built to work together and form a logical unit of functionality. An assembly provides the common language runtime with the information it needs to be aware of type implementations. To the runtime, a type does not exist outside the context of an assembly. Physically it can be a Executable file or *.dll (Dynamic linked library) , basically these Assemblies consist MSIL code and these assemblies may refer other assemblies

These assemblies are stored external to SQL server and before  you can use an assembly from SQL Server you must register it. When you register the assembly, it is imported into SQL Server and configured for use. Often assemblies depend on other assemblies, which in turn depend on others, and so on, forming a dependency tree. During registration, the entire chain of dependencies for your assembly must be imported into SQL Server.

During the execution the SQL Server the brings the assembly code(CIL) in to the sql server execution environment which is a CLR and converts the MSIL code to the machine instructions

Assemblies are registered using the CREATE ASSEMBLY

uploads an assembly that was previously compiled as a .dll file from managed code for use inside an instance of SQL Server

statement as shown below

CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ]

FROM { <client_assembly_specifier> | <assembly_bits> [ ,…n ] }

[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]

[ ; ] <client_assembly_specifier> :: = ‘[\\computer_name\]share_name\[path\]manifest_file_name’ | ‘[local_path\]manifest_file_name’ <assembly_bits> :: = { varbinary_literal | varbinary_expression }

assembly_name                                                  :Is the name of the assembly that will be registered in the SQL server , Which should be unique with in the database

AUTHORIZATION [owner_name]                 :owner_name must either be the name of a role of which the current user is a member, or the current user must have  IMPERSONATE permission on owner_name(if username is used). If not specified, ownership is given to the current user. Recommended role to use [DBO]

<client_assembly_specifier>                            :Specifies the local path or network location where the assembly that is being uploaded is located, and also the manifest  file name that corresponds to the assembly. <client_assembly_specifier> can be expressed as a fixed string or an expression evaluating to a fixed string, with variables. CREATE ASSEMBLY does not support loading multimodule assemblies. SQL Server also looks for any dependent assemblies of this assembly in the same location and also uploads them with the same owner as the root level assembly

PERMISSION_SET { SAFE | EXTERNAL_ACCESS | UNSAFE } :

Before talking about these three options we need to know little about CLR CAS(Code Access Security)

What is CAS security?

The CLR supports a security model called code access security (CAS) for managed code. In this model, permissions are granted to assemblies based on the identity of the code, as opposed to the identity of the user or process running the code. CAS permissions are granted based on evidence and policies. For example, the origin of an assembly is a form of evidence, and a policy could specify that code downloaded from the Internet should be granted a very limited set of permissions. System administrators set policies, removing security decisions from users and developers. The security policy that determines the permissions granted to assemblies is defined in three different places:

• Machine policy: This is the policy that is in effect for all managed code running in the machine on which SQL Server is installed.
• User policy: This is the policy that is in effect for managed code hosted by a process. For SQL Server, the user policy is specific to the Windows account on which the SQL Server service is running.
• Host policy: This is the policy that is set up by the host of the CLR (in this case, SQL Server) that is in effect for managed code running in that host.

What is CAS security?

 

 

 

 

 

 

 

 

 

 

 

 

 

How does CAS security works?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The set of CAS permissions that are granted to managed code when running inside SQL Server is the intersection of the set of permissions granted by the above three

policy levels. Even if SQL Server grants a set of permissions to an assembly loaded in SQL Server, the eventual set of permissions given to user code may be restricted

further by the user and machine-level policies. The security model of the Microsoft SQL Server integration with the Microsoft .NET Framework common language

runtime (CLR) manages and secures access between different types of CLR and non-CLR objects running within SQL Server. These objects may be called by a Transact-

SQL statement or another CLR object running in the server.

 

 

 

 

 

Permission Sets:

The SQL Server host policy level is determined by the permission set specified when creating the assembly.

There are three Permissions

1. SAFE
2. EXTERNAL_ACCESS
3. UNSAFE

SAFE:  Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry, and this is the default permission set.

If assembly is set to safe and during the execution if it tries to access external resources like file system or network , it will fail.

EXTERNAL_ACCESS:  is the permission set you will assign to your assembly if you require it to access external resources. Assemblies registered with the
EXTERNAL_ACCESS permission set can access and modify the file system, write to event logs, use network services like DNS and Active Directory, create outbound
network connections including Web access (port 80 and 443) and SMTP mail access  (port 25), and access HKEY_CLASSES_ROOT, HKEY_LOCAL_MACHINE,
HKEY_CURRENT_USER, HKEY_CURRENT_CONFIG, and HKEY_USERS registry keys.

UNSAFE: UNSAFE allows assemblies unrestricted access to resources, both within and outside SQL Server. Code executing from within an UNSAFE assembly can call unmanaged code.

 

Accessing External Resources