0

I am using the query below to connect to AD from a SQL Server 2014. The user id used for linked server ADSI is an admin and has full rights.

This query returns only 7 members but in fact there are 21 members. Powershell returns 21 members (can't use it for different reasons)

SELECT 
    sAMAccountName as Login, 
    CN as Name, 
    GivenName as FirstName,
    SN as LastName, 
    DisplayName as FullName 
FROM 
    OPENQUERY(ADSI, 'SELECT
                         sAMAccountname,
                         givenname,
                         sn,
                         displayname,
                         CN 
                     FROM
                         ''LDAP://DOMAIN/DC=...,DC=...'' 
                     WHERE 
                         objectCategory = ''person'' 
                         AND objectClass = ''user''  
                         AND memberOf = ''CN=group1_Users,OU=Groups,DC=...,DC=...'' 
                     ORDER BY CN')

I am not sure at this point what could be wrong. I looked all over and it seems there is no solution.

I only want to pass that one particular group name.

However, doing the opposite, meaning looking for a user ID, getting the ADSPATH and passing it to get the groups that the user is a member of works.

This is working for me but I don't need it.

Query AD Group Membership Recursively Through SQL

I also tried this but I still get 7 members

DECLARE @path NVARCHAR(MAX) = 'CN=group1_Users,OU=Groups,DC=...,DC=...',

SET @Query = 'SELECT cn, AdsPath, samAccountName
              FROM OPENQUERY (ADSI, ''<LDAP://domain/DC=...,DC=...>;(& 
                                      (objectClass=User)(memberof:1.2.840.113556.1.4.1941:=' + @Path +
                                     '));samAccountName,cn, adspath;subtree'')'

EXEC SP_EXECUTESQL @Query
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Overdrive
  • 57
  • 8
  • Can you show us the PowerShell command you used that gave you 21 members? – Gabriel Luci Dec 05 '18 at 20:51
  • This turned out to be a "Setting". On the ADSI linked server, you must specify the domain name even if you are admin and you specify just username/password it wont work. it must be DOMAIN\Username . – Overdrive Dec 11 '18 at 14:51

1 Answers1

0

This turned out to be a setting on the SQL linked server properties. if you use "be made using the login's current security context" and you are a domain admin it will not work . you have to specify "Be made using this security context" AND specify the DOMAIN name in the user such as DOMAIN\Username. It did not work without the DOMAIN name.

Overdrive
  • 57
  • 8