x

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.
more ▼

asked Apr 19, 2012 at 10:15 PM in Default

Frustrated gravatar image

Frustrated
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

5 answers: sort newest

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.
more ▼

answered May 20, 2012 at 07:01 PM

Frustrated gravatar image

Frustrated
0 1 1 1

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered May 01, 2012 at 09:45 PM

Frustrated gravatar image

Frustrated
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

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)?
more ▼

answered Apr 22, 2012 at 07:02 PM

Frustrated gravatar image

Frustrated
0 1 1 1

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).
Apr 23, 2012 at 09:30 PM Oleg
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Apr 21, 2012 at 03:29 PM

JD gravatar image

JD
104 6 7 8

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Apr 20, 2012 at 07:12 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

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.
Apr 20, 2012 at 12:21 PM Frustrated
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x26
x8

asked: Apr 19, 2012 at 10:15 PM

Seen: 4645 times

Last Updated: May 20, 2012 at 07:01 PM