How to register System.DirectoryServices for use in SQL CLR User Functions?

Question

I am porting an old 32-bit COM component that was written in VB6 for the purpose of reading and writing to an Active Directory server. The new solution will be in C# and will use SQL CLR user functions.

The assembly that I am trying to deploy to SQL Server contains a reference to System.DirectoryServices. The project does compile without any errors but I am unable to deploy the assembly to the SQL Server because of the following error:

Error: Assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.

What are the correct steps for registering System.DirectoryServices on SQL Server?

Answer 1

The information provided from other answers led me to the solution. Here are the steps I came up with for future reference:

CREATE ASSEMBLY [System.DirectoryServices]
FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'
WITH PERMISSION_SET = UNSAFE
GO

The first time I ran the statement above I got the following error:

CREATE ASSEMBLY for assembly 'System.DirectoryServices' failed because assembly 'System.DirectoryServices' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

In order to get the CREATE ASSEMBLY statement to execute without error I had to first turn TRUSTWORTHY ON as follows:

ALTER DATABASE DatabaseName SET TRUSTWORTHY ON
GO

Once TRUSTWORTHY is turned ON, the command executed without error but it did present this scary sounding warning:

Warning: The Microsoft .NET Framework assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

With System.DirectoryServices properly registered in SQL Server I am now able to deploy/register the dependent custom SQL CLR assembly without any problems.

Answer 2

Does that article here help??

New "SQLCLR-approved" assembly in SP1

People often ask about the set of base class library assemblies that can be safely used in SQLCLR. It usually takes the form "can I use assembly System.XYZ.dll in SQLCLR procedural code" or "why do I get "assembly System.XYZ.dll is not found" when I try and catalog my own assembly that calls this one? The ones is hear mentioned most frequently is System.DirectoryServices.dll (Active Directory support) or System.Management.dll (WMI support) or System.Remoting.dll et al. The only way you use these is to run CREATE ASSEMBLY on them yourself, which involves using PERMISSION_SET = UNSAFE. And cataloging all the dependencies. Not for the faint of heart.

Also - SQL Server CLR doesn't support every imagineable assembly - find lists here:

• for SQL Server 2005 SQL-CLR
• for SQL Server 2008 SQL-CLR

One note from that second MSDN article:

Unsupported Libraries

Unsupported libraries can still be called from your managed stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. The unsupported library must first be registered in the SQL Server database, using the CREATE ASSEMBLY statement, before it can be used in your code. Any unsupported library that is registered and run on the server should be reviewed and tested for security and reliability.

For example, the System.DirectoryServices namespace is not supported. You must register the System.DirectoryServices.dll assembly with UNSAFE permissions before you can call it from your code. The UNSAFE permission is necessary because classes in the System.DirectoryServices namespace do not meet the requirements for SAFE or EXTERNAL_ACCESS. For more information, see CLR Integration Programming Model Restrictions and CLR Integration Code Access Security.