asked
How to query individual Active Directory fields from SQL Server 2008
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). SELECT * FROM OPENQUERY(ADSI, 'SELECT * FROM ''LDAP://LDAP Details'' WHERE sn=''bloggs''') If I run a query via the following url in Internet Explorer, I am also able to obtain the results. ldap://LDAP Details?cn,mail?sub?(sn=bloggs) However, if I try to run the following query in SQL, I receive the error message that is shown. SELECT * FROM OPENQUERY(ADSI, 'SELECT cn, mail FROM ''LDAP://LDAP Details'' WHERE sn=''bloggs''') > Cannot get the data of the row from > the OLE DB provider "ADSDSOObject" for > linked server "ADSI". Could not > convert the data value due to reasons > other than sign mismatch or overflow. I would be extremely grateful if somebody could tell me why a query to return individual fields fails, and a solution for my problem.
Maybe this KB is related to your issue: [FIX: "Could not convert the data value due to reasons other than sign mismatch or overflow" error message when you use the ADSI OLE DB provider to retrieve I8 data type properties in Windows Server 2003][1] [1]:
http://support.microsoft.com/kb/887474/en-us
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.
Hi, I've run your query in my environment ( W2K3 Advanced Server, SQL Server 2008) and it's works fine and returns the data, `SELECT * FROM OPENQUERY(ADSI, 'SELECT CN,mail FROM ''LDAP://my_DC/OU=xxx,OU=xxx_parent,DC=domain'' WHERE sn=''BLABLA''')` so, I wonder if the OS you use really makes the difference.
I will try it on a Windows 2003 and a Windows 2008 server when I can, and let you know if it works on either of those. Am I right to assume that my LDAP details must be correct if I can obtain the ADsPath values (I am using the details that are stipulated in our corporate documentation)?
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.
I apologize for taking so long to respond. Finally I have been able to obtain individual fields by using a query with the following format. SELECT * FROM OPENQUERY(ADSI, 'SELECT cn, mail FROM ''LDAP://DC=DomainName1,DC=CompanyName,DC=net'' WHERE sn=''bloggs''') 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. SELECT * FROM OPENQUERY(ADSI, 'SELECT cn, mail FROM ''LDAP://DC=DomainName1,DC=CompanyName,DC=net'' WHERE sn=''bloggs''') 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.
5 People are following this question.
