Results 1 to 8 of 8

Thread: Searching Active Directory with SQL Server 2000

  1. #1
    Join Date
    Oct 2004
    Posts
    5

    Searching Active Directory with SQL Server 2000

    I was wondering how exactly one can search the Active Directory with SQL Server 2000. There is all kind of stuff on the Internet about how to connect them and how useful it is to connect them together - plus how you can use Active Directory to query SQL Server, but I`ve never encountered any article where they explain how SQL Server can search the Active Directory.
    I`m thinking about how one has their user-database in Active Directory with all users in groups, but only uses groups in SQL Server. How exactly can SQL Server figure out how a user belongs to a group?
    Does Active Directory tell SQL Server about that when the user tries to access something from SQL Server, or does SQL Server already knows which group is accessing it as soon as the user authenticates in Active Directory?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Look at 'OLE DB Provider for Microsoft Directory Services' in sql books online.

  3. #3
    Join Date
    Oct 2004
    Posts
    5
    Thanks for the suggestion, helped me out quite a bit

    I have progressed a bit, but now I want to use SQL Server 2000 to lookup what groupmemberships a user has.
    SQL Server has all kind of groups with restrictions, so it wants to know if the user is a member of the groups in AD in order to view the tables in SQL Server.

    I`ve found plenty of code to handle it in a webapplication, but I believe it`s also possible to compare the SQL Server group with the AD group with a query. Anyone who can help me to explain how to extract the users from a certain group in AD with a query?
    That would explain which users are in that group and then gain access to the table.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Use xp_logininfo 'nt group name', 'MEMBERS' to return all members in that group.

    Use xp_logininfo @acctname = 'domain\login_id', @option = 'all' to find out windows group membership for user that has sql access and permission path.

  5. #5
    Join Date
    Oct 2004
    Posts
    5
    That`s a useful option, but not in my case, I think.

    Example:
    I have user 'John Doe' in AD, who is a member of the groups 'Secretary' and 'Lunch'.
    In my SQL Server I have both groups listed as well.

    How is it possible that SQL Server checks with AD if the person trying to log in is in the group 'Lunch'? Since SQL Server only has the groups listed and not the users.

    I`ve tried this by using the 'memberOf' function in order to retrieve the group memberships from John Doe from a good old Query Analyzer, but the 'memberOf' function doesn`t give any results when I apply that one in the query.

    Example:
    select * from OPENQUERY (LDAP, '
    SELECT cn, adspath, memberOf
    FROM ''LDAP://DC=company,DC=com''
    WHERE memberOf = ''cn=development,dc=company,dc=com''
    ')

    I also have an application in ASP.NET which might be able to request which memberships John is in, but I want to make SQL Server retrieve that info.
    Any suggestions?
    Or perhaps am I using an completely wrong query?
    Last edited by Leja2; 11-08-2004 at 10:50 AM.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    You can use "xp_logininfo 'Lunch', 'MEMBERS'" to return all members in that group. Sql server does same check when a windows account connects to it.

  7. #7
    Join Date
    Oct 2004
    Posts
    5
    http://support.microsoft.com/default...b;en-us;297368
    --
    Above site says that it`s not possible to use that command in an OPENQUERY, for reasons specified there.

    The SQL Server and the AD aren`t on the same PC, but are on the same network. So we make AD a linked server and connect to it via an OPENQUERY command.

    Any suggestion to how I can read from the AD as linked server into SQL Server?
    Or just an example how to make that query, since my attempts have been quite futile so far. I`ve already posted my query in a previous post how I handle it - which doesn`t work, btw
    I can retreive all kind of data, but not the users from a specific group.
    Last edited by Leja2; 11-09-2004 at 04:12 AM.

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    Don't have to run that in openquery.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •