I would like to create a CLR user defined function in Visual Studio
2005 to retrieve information from Active Directory for the logged in
user. Unfortunately I am unable to add a reference to
System.DirectoryServices because Visual Studio only allows me to add
"SQL references" to SQL Server Projects. I have tried putting my UDF in
a class library instead of a SQL Server Project but then I get the
following error when I try to CREATE ASSEMBLY in SQL Server:

Assembly 'AIMSqlExtend' references assembly 'system.directoryservices,
version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.',
which is not present in the current database. SQL Server attempted to
locate and automatically load the referenced assembly from the same
location where referring assembly came from, but that operation has
failed (reason: 2(The system cannot find the file specified.)). Please
load the referenced assembly into the current database and retry your
request.

Is what I am trying to do supported by Microsoft? If anybody could
offer any suggestions I would be most grateful.

Thanks

Dave

    // Get the ECSUsername for this user from Active Directory
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess =
DataAccessKind.Read)]
    public static SqlString xudf_ECSUsername()
    {
        string username = SqlContext.WindowsIdentity.Name;
        DirectoryEntry dr = new DirectoryEntry("LDAP://" +
EscapeSpecialChars(username));
        return (string)dr.Properties["ECSUsername"][0];
    }

Hello roadz,

Before cataloging your assembly , try executing this statement:

create assembly [System.DirectoryServices] from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'
with permission_set = unsafe

Which will catalog the DirectoryServices assembly for use. Note you may also
need to alter the hosting database to be trustworthy or follow the procedure
at http://www.sqljunkies.com/WebLog/ktegels/archive/2006/08/14/safetydancelite.aspx

Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/

Keith,

Thank you for your extremely quick reply. I tried doing this and SQL
Server gave me a warning stating that System.DirectoryServices had not
been tested with SQL Server. I guess this means that MS don't support
this. When trying to run my UDF I get a COM exception stating that "The
server is not operational". I guess that this is because
System.DirectoryServices is just a wrapper for a COM DLL and COM
interop is not supported in SQL Server 2005. I think that my options
are to either use C++ extended stored procedures (yuk) or abandon this
route altogether.

Thanks again,

Dave

Show quoteHide quote

Sorry Kent, misread your name... having a very stressful day!

It turns out I had gone down completely the wrong route in trying to
use CLR UDFs because Active Directory can be accessed directly from
TSQL. See Books Online: linked servers [SQL Server], Directory Service.

Dave

SET/SELECT truncate string
parse out set of numbers from a column
stored procedure stopped working
How is the design for this complex requirement ?
trying to use a cursor to update table..