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

Show quote Hide quote 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.

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.

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

ManagementOperationObserver and Get( observer )
Sending multicast messages from specific IP address
question about class Socket
Optimization needed... Is Assembly code available?
MyApplication has encountered a problem... when not Administrator!