SqlServer 2005 CLR integration using DirectoryServices

Using a VisualStudio 2005 Sql Server Database project template, I want
to
access DirectoryServices (AD) from a UDF, but cannot add a reference to

directoryservices.dll (legally) into the c# project, and if I add it
illegally (in the XML), i get a sql error saying that DirectoryServices

is not in the SQL catalog.

Question: can I access DirectoryServices from Sql Server in either a
UDF or SP. If the answer is yes - then how?

Thanks
Howard

Willy
Wed Feb 22 09:22:16 CST 2006


"HowardB" <howard.birkett@collercapital.com> wrote in message
news:1140604268.558973.45320@g44g2000cwa.googlegroups.com...
| Using a VisualStudio 2005 Sql Server Database project template, I want
| to
| access DirectoryServices (AD) from a UDF, but cannot add a reference to
|
| directoryservices.dll (legally) into the c# project, and if I add it
| illegally (in the XML), i get a sql error saying that DirectoryServices
|
| is not in the SQL catalog.
|
| Question: can I access DirectoryServices from Sql Server in either a
| UDF or SP. If the answer is yes - then how?
|
| Thanks
| Howard
|

Sure it's possible, but you should know that this is quite unsafe and
currently unsupported by MS.

What you should do first is to marks your database as THRUSTWORTHY, by means
of:

ALTER DATABASE xxxx Set THRUSTWORTHY= ON

then you need to load the DirectoryServices assembly in the DB, like:
CREATE ASSEMBLY DirectoryServices from
'c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.directoryservices.dll'
WITH PERMISSION_SET = UNSAFE

this will show you this warning message:

Warning: The Microsoft .Net frameworks assembly 'system.directoryservices,
version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a,
processorarchitecture=msil.' you are registering is not fully tested in SQL
Server hosted environment.

Once this is done you can load your assembly.

CREATE ASSEMBLY aaaa from 'your_assembly_Path' WITH PERMISSION_SET = UNSAFE

Note the UNSAFE permission set, check SQL online to see what this means ;-)

Willy.

HowardB
Wed Feb 22 10:06:00 CST 2006

This is a behind the firewall, non public facing server, supporting an
internal app. So UNSAFE should be SAFE ;-)

Thanks for your help Willy
H

Willy
Thu Feb 23 07:30:23 CST 2006


"HowardB" <howard.birkett@collercapital.com> wrote in message
news:1140624360.604162.58870@g47g2000cwa.googlegroups.com...
| This is a behind the firewall, non public facing server, supporting an
| internal app. So UNSAFE should be SAFE ;-)
|
| Thanks for your help Willy
| H
|

Unsafe here means UNSAFE for SQL Server, this has nothing to do with
security, the scenario described here is un-tested and is not supported by
MSFT. SQL Server folks don't like to deal with stuff that is calling into
unsafe code, all assemblies like System.Management, System.Winforms,
System.DirectoryServices etc.. are considered dangerous (and have never been
tested), they could compromise the health of the SQL server itself, don't
forget that this stuff runs inside the SQL server process.
Note also that you can't create types or call some methods on other
namespaces too. One of the things that scares the SQL team is that you start
creating threads outside the control of SQL, now, this is exactly what's
been done by DirectoryServices (under the hood).


Willy.

HowardB
Fri Feb 24 09:43:49 CST 2006

Thanks again Willy - i'm going to have to rethink my solution/design
H