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] :
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)?
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.