SQL LDAP query problem
Trying to filter AD groups. It works for me:
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
This works, but we have the email addresses in the security groups, so I'm trying a filter that goes 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
He continues to throw:
Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query: "SELECT name, distinctName FROM 'LDAP: //hhsc.org/DC=hhsc, DC = org' WHERE objectCategory = 'group' and groupType: 1.2 .840.113556.1.4.803: = '2147483648' "to execute from OLE DB provider" ADsDSOObject "for linked server" ADSI ".
I suspect this is the syntax in groupType, but it beat me up.
FYI that id, or whatever you call it for groupType, works in the CFLDAP request that only my mailing lists give me.
Working on this and keep going for a while, but I am not making any progress.
Thanks in advance.
source to share
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 that in my case, I found that if I hadn't put .com, .org, or whatever in the domain name, it didn't work. Also, in my case, I am running a query from the AD root (I assume you call it) DC = ????, DC = ???? you can put OU or CN in front of that, of course.
SQL Server R2 R2 64 bit, Active Directory is 32 bit server. (Yes, we are updating.)
For the next headache !!!
source to share