Registering CLR Assemblies for Use with SQL Server

Assemblies are physical units of deployment that contain managed code. This managed code can be called by CLR stored procedures, CLR functions, CLR triggers, CLR user-defined aggregates, and CLR user-defined data types. Assemblies are stored externally from SQL Server and thus can be used by other applications. When called by SQL Server, the code within the assembly is loaded into the SQL Server service process and is executed within its context.

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. Assemblies are registered using the CREATE ASSEMBLY statement as shown in Example 4.1.

Example 1. Syntax—Create Assembly

CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM { <client_assembly_specifier> | <assembly_bits> [,...n ] } [ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ] [ ; ]

The assembly_name is the unique name of the assembly within SQL Server. To avoid confusion, name your assemblies after their file names, unless there is a naming conflict with another registered assembly. For example, if you want to use the assembly MyAwesomeFunctions.dll, designate MyAwesomeFunctions as its name when registering it.

The owner_name is the role or user who will become the owner of the assembly. Registered assemblies have owners within SQL Server, much like tables, stored procedures, and other database objects. Note that when registering the assembly, you can only specify a role of which you are a member, or a user from whom you have permission to impersonate. If you don’t specify the owner_name explicitly, you will be registered as the owner by default (again, the same principle as tables or stored procedures). Just like other objects, it is recommended to register all assemblies as owned by dbo. This will help avoid major interoperability problems that arise when assemblies that need to call each other are owned by different users.

Warning

Be careful when choosing anything but dbo as the AUTHORIZATION parameter for CREATE ASSEMBLY. You must have a very specific reason not to use dbo, for example, if the calling user is not a member of db_owner. The best practice is to always use dbo for all assembly ownership.

The client assembly specifier is the local or network path of the physical assembly DLL file you wish to register. Alternatively, you can specify the actual bits of the assembly as the assembly bits instead of its network path. The bits are specified as either a literal varbinary value or an expression of a varbinary data type. You must first specify the bits for the root assembly and then all its dependencies.

Warning

Remember that when SQL Server registers the assembly, it imports the assembly file as well as all its dependencies. If any dependency is not available, and it is not already registered with SQL Server, the registration statement will fail and report an error.

Also note that if a dependency is already registered, but under a different owner, your statement will also fail.

The PERMISSION_SET parameter specifies what security permissions are granted to the assembly code when it executes within SQL Server. We will discuss permission sets next in this chapter.

The following example registers the MyAwesomeFunctions assembly, owned by dbo;

CREATE ASSEMBLY MyAwesomeFunctions
AUTHORIZATION [dbo]
FROM 'C:\Valentine\MyAwesomeFunctions\bin\Debug\MyAwesomeFunctions.dll'
WITH PERMISSION_SET = SAFE;

For this statement to succeed, the calling user must be a member of db_owner role, and also have permission to access the MyAwesomeFunctions.dll file. All dependencies for the assembly must be available for SQL Server to import or must have been already registered with SQL Server. Remember, you don’t need to register supported assemblies as they are registered by default.

Warning

You cannot register an assembly if there is already another version of this assembly registered with the same name, public key, and culture (locale). The public key is used to generate a unique name for an assembly. For example, you cannot register MyAwesomeFunctions version 1.5.0 and MyAwesomeFunctions version 1.5.2.

Finally, you can unregister an assembly using the DROP ASSEMBLY statement and change the properties of an assembly using the ALTER ASSEMBLY statement. Supported assemblies that are loaded by default cannot be altered or dropped.