Creating .NET Framework v.2.0 assembly in SQL Server 2012

Question

I'm trying to access Active Directory through SQL Server 2012. My inspiration sources are:

http://blogs.msdn.com/b/ikovalenko/archive/2007/03/22/how-to-avoid-1000-rows-limitation-when-querying-active-directory-ad-from-sql-2005-with-using-custom-code.aspx

and

http://www.pawlowski.cz/2011/04/querying-active-directory-sql-server-clr/

Both of them are using assembly to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll.

When I try to create this assembly using this code:

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

then it fails with this message:

Assembly 'System.DirectoryServices' could not be installed because existing policy would keep it from being used.

I've tried create assembly on v4.0 using this code:

CREATE ASSEMBLY [System.DirectoryServices]
AUTHORIZATION [dbo]
FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.DirectoryServices.dll'
WITH PERMISSION_SET = UNSAFE
GO

and it was successful. After this I've tried alter this new assembly using this code:

ALTER ASSEMBLY [System.DirectoryServices]
FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'

and it fails with this message:

Alter assembly from 'system.directoryservices, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil' to 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil' is not a compatible upgrade.

Anyone have a idea how to create assembly on .NET Framework .dll in version 2.0?

Answer 1

I had a similar problem with SQL Server 2008. I solved it by changing the path to Framework64.

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