SQL CLR stored Procedure to query active directory

Question

I need to obtain some information from the users in the database that is stored on Active Directory, I've a simple function to do this:

using (DirectoryEntry de = new DirectoryEntry("LDAP://ANYGIVENDOMAIN"))
{
    using (DirectorySearcher adSearch = new DirectorySearcher(de))
    {
        adSearch.Filter = "(sAMAccountName=jdoe)";                  
        SearchResult adSearchResult = adSearch.FindOne();

        StringBuilder sb = new StringBuilder();
        sb.AppendLine(adSearchResult.Properties["displayname"][0].ToString());
        sb.AppendLine(adSearchResult.Properties["givenName"][0].ToString());
        sb.AppendLine(adSearchResult.Properties["objectSid"][0].ToString());
        sb.AppendLine(adSearchResult.Properties["description"][0].ToString());
        sb.AppendLine(adSearchResult.Properties["objectGUID"][0].ToString());
    }
}

Running from a WinForm do as I want, but in the SQL Server Project Type I can't add the namespace System.DirectoryServices to the references.

Anyone knows why ?

Regards

JE

Answer 1

See: Supported .NET Framework 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.