How to query individual Active Directory fields from SQL Server 2008

0

I have created a linked server to Active Directory, and if I run the following query, it returns the ADsPath values as expected (I have replaced the actual details of my LDAP server with LDAP Details).

Unfortunately the problem applies to all individual fields. I have intentionally avoided trying to query I8 fields or fields such as description that can have multiple values. If it makes a difference, I am using SQL Server 2008 (all updates applied) on a Windows Vista machine.

If the list of properties you need to interrogate is static then it might make sense to write a CLR table valued function to query AD. Please let me know if you want me to post a complete sample of such function.

From what I understand, the LDAP details are best deduced rather than hardcoded because if you have multiple domain controllers, it might be cheaper to query the one AD wants you to query rather than forcing it to query the PDC every time. There is a root named LDAP:\/\/RootDSE which cannot be used for querying the data but is very handy to deduce the details. For example,

string root = "LDAP://";
using (DirectoryEntry de = new DirectoryEntry("LDAP://RootDSE"))
{
root += de.Properties["defaultNamingContext"][0].ToString();
}
// now the details are available, so
using (DirectoryEntry de = new DirectoryEntry(root))
{
// etc
}

Any property which is itself an array can be queried for its zero's member to avoid looping and property accountexpires or any other which is stored in FileTime format can be retrieved by using the static FromFileTime method of DateTime struct (zero stored translates to January 2nd 1600 for those).

I tried running the query on a Windows 2008 server, but received the same error. I used Oleg's suggestion to determine the "root", but when I used that in the query I received the following error. An error occurred while preparing the query "SELECT FROM 'LDAP://DC=region,DC=domain,DC=net' WHERE sn='bloggs'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI". I used Select as a first step, but even that failed. It appears that I will have to resort to using a CLR function (thanks for the kind offer to post a sample Oleg, but hopefully my knowledge of CLR will provide a result eventually), but I always like to find why something that works in code, and works in SQL if I do not query specific fields, will not work when I use the query that I am trying to use.

Previously I had used LDAP://Domainname.company.com/OU=UnitName,O=CompanyName.com, which had worked for a URL query of the directory.

The only problem that I still have is that we have three domain names that represent three geographical groups, but I can only query my domain (DomainName1). If I run my original URL query, I obtain results for users in all domains, which suggests that it isn't a permissions problem. I would be grateful if somebody could tell me how I can query all three domains within the same SQL query.,I apologize for taking so long to respond. I have finally been able to return specific fields by using a query with the following structure.

I had previously used ''LDAP://Domainname.company.com/OU=UnitName,o=OrganizationName'', which worked successfully within a browser URL.

DomainName1 represents one of three domain names that we have, which represent three geographical regions. I would be grateful if somebody could tell me how I can query all three within the same query. I cannot even query either of the other two individually by replacing DomainName1 with DomainName2, but I am able to obtain results for users within the other domains via a URL query.