LDAP to Active Directory as Linked Server - Views

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

LDAP to Active Directory as Linked Server - Views

Author

Topic

alphaz
Starting Member

2 Posts

Posted - 2004-09-30 : 17:33:12

I have a view set up on data from the Active Directory set up as a linked server. (Win 2003 Server / MS SQL Server 2000). Here is the query used to set up the view:


CREATE view ActiveDirectoryUsers as
SELECT CAST(objectSID AS bigint) AS SID, sAMAccountName AS UserID,
title, givenName AS FirstName, sn AS SurName, cn AS FullName,
distinguishedName AS DN, telephoneNumber AS Phone, mobile,
facsimileTelephoneNumber AS Fax, manager AS ManagerDN, department,
mail AS Email
FROM OPENQUERY(ADSI,
'SELECT objectSID, sAMAccountName, title, distinguishedName,
telephoneNumber, mobile, facsimileTelephoneNumber, manager,
department, givenName, mail, cn, adspath, sn
FROM ''LDAP://DC=example,DC=com''
WHERE objectCategory=''Person''
AND objectClass=''user''
AND sn=''*''
ORDER by sAMAccountName')
Rowset_1

PROBLEM
I need to set up a view of a particular Security Group (simply known as a 'group') within Active Directory, but nothing I've tried works. My attempts have revolved around adding an extra 'AND' to the 'WHERE' clause in the above query,
EG:

AND objectGroup=''Group'' AND name=''ExampleName''

The problem might be about somehow getting the name attribute to be related to the objectGroup attribute, but it could be anything else also.

Any clues would be greatly appreciated.
alphaz

alphaz
Starting Member

2 Posts

Posted - 2004-09-30 : 23:31:21

The answer is, apparently, You can't. Membership of groups is a multi-valued field in Active Directory (of course), and there's no way for SQL Server to talk to AD to pick out an object on the basis of a value being -among- some field's multiple values. Or is there?

Anyway, I have written a direct LDAP enquiry to get an array with DN values for group members, which I then select against from the view I have of Active Directory Users to get their other data.

Quick PHP code:



function getADGroupMembersByDN($group, $ldap_server, $base_dn, $ldap_user, $ldap_pw) {
   // connect to server
   if (!($connect=@ldap_connect($ldap_server)))
      die("PHP:TREG:'utility.php':getADGroupMembersDN():LDAP CONNECT: ".ldap_error($connect));
   ldap_set_option($connect, LDAP_OPT_PROTOCOL_VERSION, 3);
   ldap_set_option($connect, LDAP_OPT_REFERRALS, 0);

   // bind to server
   if (!($bind=@ldap_bind($connect, $ldap_user, $ldap_pw)))
      die("PHP:TREG:'utility.php':getADGroupMembersDN():LDAP BIND: ".ldap_error($connect));

   // search
   $filter = "(&(objectClass=group)(name=$group))";
   if (!($search=@ldap_search($connect, $base_dn, $filter)))
      die("PHP:TREG:'utility.php':getADGroupMembersDN():LDAP SEARCH: ".ldap_error($connect)); 

   $info = ldap_get_entries($connect, $search); 
   ldap_close($connect);
   return $info[0]['member'];
}



function  selectOnArray($db, $table, $valColumn, $valArr, $selectColsArr, $sortIndex = 0) {
   $sql = "SELECT ";
   $len = count($selectColsArr);
   for ($i = 0; $i < $len; ++$i) {
      $sql .= $selectColsArr[$i];
      if ($i < $len-1) $sql .= ',';
   }
   $sql .= " FROM $table WHERE ";
   $len = count($valArr);
   for ($i=0; $i < $len; ++$i) {
      $sql .= "$valColumn = '{$valArr[$i]}'";
      if ($i < $len-1) $sql .= ' OR ';
   }
   $sql .= " ORDER BY {$selectColsArr[$sortIndex]}";
   $res = $db->query($sql);
   if (DB::isError($res)) die("Php: utility.php: selectOnArray(): ".$res->getMessage());
   else return $res;
}

Thanks for you interest.
alphaz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources