SQL LDAP Query Issue

Question

Trying to filter AD groups. I've got this that works:

SELECT name, distinguishedName    
FROM OPENQUERY( ADSI,'SELECT name, distinguishedName    
FROM ''LDAP://hhsc.org/DC=hhsc, DC=org''    
WHERE objectCategory = ''group'' AND proxyAddresses=''*'' ')    
ORDER BY name

It works but we've got email addresses in Security Groups, so I'm trying filter that with this:

SELECT name, distinguishedName    
FROM OPENQUERY( ADSI,'SELECT name, distinguishedName    
FROM ''LDAP://hhsc.org/DC=hhsc, DC=org''    
WHERE objectCategory=''group'' AND groupType:1.2.840.113556.1.4.803:=''2147483648'' ')

ORDER BY name

It keeps throwing:

Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT name, distinguishedName FROM 'LDAP://hhsc.org/DC=hhsc, DC=org' WHERE objectCategory='group' & groupType:1.2.840.113556.1.4.803:='2147483648' " for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

I suspect it's syntax in groupType, but it's got me beat.

FYI that identifier or whatever you call it for groupType works in a CFLDAP query that gives me just my Distribution Lists.

Been working on this off and on for awhile now, but am not making any progress.

Thanks in advance.

Can you do a select * just to see the column names and see if you are referencing them correctly? — abbottmw
What is the DBMS you are using? Most likely you need to quote that column name in your WHERE clause. Like "groupType:1.2.840.113556.1.4.803:" = ... Assuming that is the actual column name — Miguel-F
@Miguel-F That was going to be my next suggestion. If it is a column name, quotes or [] around the column may be needed. In the CFML docs it also says "In ColdFusion, illegal characters are automatically mapped to the underscore character; therefore, column names in the query result set might not exactly match the names of the LDAP attributes." — abbottmw
If I change the syntax on groupType to: ''groupType:1.2.840.113556.1.4.803:''=2147483648 ') (Note that's two single quotes around groupType and removed the ' around the number) the query runs, I briefly see the column names and data, but then it goes away and I get Msg 7330 level 16 State 2 Line 1 Cannot fetch row from OLE DB provider "AdsDSOObject" for linked server "ADSI". Googling that error, I have Allow inprocess is checked in the ADsDSOObject provider, my windows login to the server is in the local admin group. (Comment Continued) — toleolu
Ran the query logged in as SQL sa, same problem. The login account for the linked server is the same login account we use in other similar applications. Thanks — toleolu

toleolu toleolu · Accepted Answer · 2015-05-21T18:43:48

In case it helps a fellow noob, this works:

SELECT name, distinguishedName    
FROM OPENQUERY( ADSI,    
'<LDAP://domainName.com/DC=????,DC=????>;    
(&(objectClass=group)(!(groupType:1.2.840.113556.1.4.803:=2147483648)));    
name, distinguishedName;    
subtree')    
ORDER BY name

Note, in my case, I found that if I did not put .com, .org or whatever in domain name, it would not work. Also, in my case, I'm running the query from the AD root (I guess that's what you call it)DC=????, DC=???? you can put OU's or CN's in front of that of course.

Server is SQL 2008 R2 64 bit, Active Directory is Server 2003 32 bit. (Yes, we're upgrading.)

On to the next headache!!!

SQL LDAP Query Issue

1 Answers