How to get users of a security group with LDAP using SQL Server 2005

[Marcus Müller]

This simple question drove me mad and took me a long time to figure it out:

How to get all users of a security group from an Active Directory with LDAP
using SQL Server 2005?

The reason for getting all users of a group was that I wanted to get the
email addresses of all group members and send them an email via a stored
procedure. After seaching the web, newsgroups and MSDN I haven't found
anyone who had implemented this with T-SQL. There are some solutions with VB
Script but they are useless if you want to transfer them them to T-SQL.
Thus, here is the pure, short T-SQL code (with a little bit of ldap):

SELECT name, mail
FROM OPENQUERY( ADSI,
'SELECT name, mail
FROM ''LDAP:// DC=[domain],DC=[tld]'' WHERE
memberOf=''CN=[groupname],OU=[organizationalunit],DC=[domain],DC=[tld]'' AND
objectCategory=''Person'' AND objectClass = ''USER''')

Variables are marked with brackets:

[domain] = Name of the Active Directory domain (e.g. "YourDomain")
[tld] = Name of the top level domain (e.g. "local")
[groupname] = This is the name of the security group whose members you want
to know (e.g. "Domain Admins")
[organizationalunit] = Name of the organizational unit (e.g. "Users")

You might think, uhh that's really simple, but the statement uses a feature
of the ADSI provider which isn't really obvious:

The "memberOf" attribute of a user returns an error 7346 if you try to
retrive the attribute as a column in the select statement because it
contains multiple values. I guess the attribute is returned as an array
which can't be casted into a sql compliant data type. Thus, I was really
amazed that the "FROM" clause was able to transfer the "memberOf=..."
statement into something meaningfull.
As far as I tested, the ADSI provider translates the "memberOf=..."
statement into an "EXISTS" statement in which the left part of the equation
has to exist as array entry. Additionally, wildcards like
"memberOf=''CN=abc*'' " are not allowed. You have to enter the complete path
of the group in your Active Directory.

Hopefully, this helps anybody who has searched for the same issue and did't
find any clue. If I have overlooked something, please tell me. The basics of
joining Active Directory Objects with Microsoft SQL Server are explained in
the MSDN article "Distributed Query" (
http://msdn2.microsoft.com/en-us/library/aa772380(VS.85).aspx ).

Yours,

Marcus Müller

[Richard Mueller [MVP]]

Post by Marcus Müller
How to get all users of a security group from an Active Directory with
LDAP using SQL Server 2005?
The reason for getting all users of a group was that I wanted to get the
email addresses of all group members and send them an email via a stored
procedure. After seaching the web, newsgroups and MSDN I haven't found
anyone who had implemented this with T-SQL. There are some solutions with
VB Script but they are useless if you want to transfer them them to T-SQL.
SELECT name, mail
FROM OPENQUERY( ADSI,
'SELECT name, mail
FROM ''LDAP:// DC=[domain],DC=[tld]'' WHERE
memberOf=''CN=[groupname],OU=[organizationalunit],DC=[domain],DC=[tld]''
AND objectCategory=''Person'' AND objectClass = ''USER''')
[domain] = Name of the Active Directory domain (e.g. "YourDomain")
[tld] = Name of the top level domain (e.g. "local")
[groupname] = This is the name of the security group whose members you
want to know (e.g. "Domain Admins")
[organizationalunit] = Name of the organizational unit (e.g. "Users")
You might think, uhh that's really simple, but the statement uses a
The "memberOf" attribute of a user returns an error 7346 if you try to
retrive the attribute as a column in the select statement because it
contains multiple values. I guess the attribute is returned as an array
which can't be casted into a sql compliant data type. Thus, I was really
amazed that the "FROM" clause was able to transfer the "memberOf=..."
statement into something meaningfull.
As far as I tested, the ADSI provider translates the "memberOf=..."
statement into an "EXISTS" statement in which the left part of the
equation has to exist as array entry. Additionally, wildcards like
"memberOf=''CN=abc*'' " are not allowed. You have to enter the complete
path of the group in your Active Directory.
Hopefully, this helps anybody who has searched for the same issue and
did't find any clue. If I have overlooked something, please tell me. The
basics of joining Active Directory Objects with Microsoft SQL Server are
explained in the MSDN article "Distributed Query" (
http://msdn2.microsoft.com/en-us/library/aa772380(VS.85).aspx ).

"Name" returns the Common Names of the users, which need not be unique
(except in a container or OU). If you want the pre-Windows 2000 logon names
(which are unique in the domain), use "sAMAccountName".

Although the space seems to not hurt, it is advised not to have spaces the
AdsPath. For example I would remove the space in:

FROM ''LDAP:// DC=[domain],DC=[tld]''

The AdsPath in the FROM clause should include the Distinguished Name of the
domain (or the base of the search). For example, it could be:

FROM ''LDAP://dc=Company,dc=MyDomain,dc=com''

You must specify the full Distinguished Name of the group in the WHERE
clause. For example, it could be:

WHERE
memberOf=''cn=TestGroup,ou=Sales,ou=West,dc=MyCompany,dc=MyDomain,dc=com''

The objectCategory and objectClass clauses restrict the results to user
members of the specified group. If you want all members, including groups,
computers, contacts, etc., skip those clauses.

The memberOf attribute is a multi-valued DN (Distinguished Name) attribute.
Wildcards are not allowed in any DN attributes (except memberOf=''*'', which
means a member of any group, or the Not of that clause). The provider
recognizes a match in the WHERE clause if any value in the collection
(array) matches.

[b***@b2solutions.life]

Post by Marcus Müller
How to get all users of a security group from an Active Directory with LDAP
using SQL Server 2005?
The reason for getting all users of a group was that I wanted to get the
email addresses of all group members and send them an email via a stored
procedure. After seaching the web, newsgroups and MSDN I haven't found
anyone who had implemented this with T-SQL. There are some solutions with VB
Script but they are useless if you want to transfer them them to T-SQL.
SELECT name, mail
FROM OPENQUERY( ADSI,
'SELECT name, mail
FROM ''LDAP:// DC=[domain],DC=[tld]'' WHERE
memberOf=''CN=[groupname],OU=[organizationalunit],DC=[domain],DC=[tld]'' AND
objectCategory=''Person'' AND objectClass = ''USER''')
[domain] = Name of the Active Directory domain (e.g. "YourDomain")
[tld] = Name of the top level domain (e.g. "local")
[groupname] = This is the name of the security group whose members you want
to know (e.g. "Domain Admins")
[organizationalunit] = Name of the organizational unit (e.g. "Users")
You might think, uhh that's really simple, but the statement uses a feature
The "memberOf" attribute of a user returns an error 7346 if you try to
retrive the attribute as a column in the select statement because it
contains multiple values. I guess the attribute is returned as an array
which can't be casted into a sql compliant data type. Thus, I was really
amazed that the "FROM" clause was able to transfer the "memberOf=..."
statement into something meaningfull.
As far as I tested, the ADSI provider translates the "memberOf=..."
statement into an "EXISTS" statement in which the left part of the equation
has to exist as array entry. Additionally, wildcards like
"memberOf=''CN=abc*'' " are not allowed. You have to enter the complete path
of the group in your Active Directory.
Hopefully, this helps anybody who has searched for the same issue and did't
find any clue. If I have overlooked something, please tell me. The basics of
joining Active Directory Objects with Microsoft SQL Server are explained in
the MSDN article "Distributed Query" (
http://msdn2.microsoft.com/en-us/library/aa772380(VS.85).aspx ).
Yours,
Marcus Müller

Thanks for this!