• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2213
  • Last Modified:

LDAP query works, but GC query fails

A web-based app is querying Active Directory. Everything has been working fine for the past couple of years, but now something is broken.
For all new user accounts added in the past month, they can be found with an LDAP:// query, but searching on the same user account with a GC:// query fails.

Here's a sample of the query. If I leave it as LDAP, then it returns a row for the user results. If I change the query to GC (which I want to since there are >1 domains) I get 0 rows returned, no results.

SELECT
sn,
givenName,
sAMAccountName,
department,
title,
mail,
createTimeStamp,
telephoneNumber,
physicalDeliveryOfficeName
FROM OpenQuery(ADSI,
      'SELECT sn, givenName, sAMAccountName, department, title, mail, createTimeStamp, telephoneNumber, physicalDeliveryOfficeName
      FROM ''LDAP://Pdc'' where objectcategory=''Person'' and objectClass=''User''') ADSI
Where (sn <> 'NULL' and department <> 'NULL' and title <> 'NULL')
AND sAMAccountName = 'newhire'

Nothing that I know of has changed in the environment to cause this to stop working.
any help would be appreciated!!
0
JammyPak
Asked:
JammyPak
2 Solutions
 
ahoffmannCommented:
silly question: does your database server support GC:// as source?
0
 
JammyPakAuthor Commented:
yes, the GC:// query works fine if I query on different users, it's only new hires from the past month that fail.
0
 
Chris DentPowerShell DeveloperCommented:

You can query older users in the Global Catalog?

If so, it suggests something is wrong with AD rather than anything else.

DCDiag / NetDiag are always good places to start for that.

Chris
0
Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

 
JammyPakAuthor Commented:
yeah...it's funny because when I query for an affected user using dsquery and the -gc option, it finds the user just fine. So it seems that the user is in the global catalog, but I don't know why that gc:// query won't return them.
0
 
Chris DentPowerShell DeveloperCommented:

Do you have more than one Global Catalog?

Chris
0
 
Chris DentPowerShell DeveloperCommented:

We could always try a little bit of VbScript to see if it's limited to the query above or is a more general condition.

Chris
0
 
JammyPakAuthor Commented:
yes, more than one GC. I've tried pointing at different GCs but the results are the same.
0
 
Chris DentPowerShell DeveloperCommented:

Want to try a bit of VbScript / PowerShell / .NET to see if we can reproduce the condition? For convenience I'd go for PowerShell, it's quite lovely for quickly testing things :)

http://www.microsoft.com/windowsserver2003/technologies/management/powershell/default.mspx

The script is below, just copy and paste into the PowerShell console. It will search the GC and print the results to the console if it finds any.

Chris
$NewHire = "newhire"
 
$RootDSE = New-Object System.DirectoryServices.DirectoryEntry("LDAP://RootDSE")
$GCRoot = New-Object System.DirectoryServices.DirectoryEntry("GC://" + $RootDSE.Get("rootDomainNamingContext"))
$Filter = "(&(objectClass=user)(objectCategory=person)(sAMAccountName=$NewHire))"
$Searcher = New-Object System.DirectoryServices.DirectorySearcher($GCRoot, $Filter)
$Searcher.FindAll()

Open in new window

0
 
BSonPoshCommented:
Just another way :)
$Forest = [system.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()
$GCRoot = [ADSI]"LDAP://$($forest.FindGlobalCatalog().Name)"
$NewHire = "newhire"
$Filter = "(&(objectClass=user)(objectCategory=person)(sAMAccountName=$NewHire))"
$Searcher = New-Object System.DirectoryServices.DirectorySearcher($GCRoot, $Filter)
$Searcher.FindAll()

Open in new window

0
 
JammyPakAuthor Commented:
I'll give it a try and report back...
0
 
JammyPakAuthor Commented:
both of those scripts return the affected users just fine.
0
 
JammyPakAuthor Commented:
our webmaster modified his scripts and was able to workaround this issue.
Instead of going via SQL, he's now querying AD directly.

SELECT
SN as lastname,
GIVENNAME as firstname,
cn as fullname,
DEPARTMENT,
sAMAccountName as username FROM  

--OLD WAY
--OpenQuery(ADSI, 'SELECT SN, GIVENNAME, cn, sAMAccountName, DEPARTMENT FROM ''GC://Pdc'' WHERE objectCategory = ''Person'' AND objectClass= ''User''') WHERE (sAMAccountName not like 'admin%' AND DEPARTMENT IS NOT NULL AND GIVENNAME <> 'ADMIN-')

--NEW WAY
OpenQuery( ADSI,'<GC://pdc>;(&(objectCategory=Person)(objectClass=user)(sAMAccountType=805306368)(sn=*)(!sAMAccountName=admin*)(!givenName=ADMIN-)(sn=*)(!sn=Test*)(!sn=Admin*)(!cn=Test*)(!cn=Admin*)(department=*)(userAccountControl:1.2.840.113556.1.4.803:=0));SN,GIVENNAME,cn,sAMAccountName,DEPARTMENT;subtree')
 
order by SN

(note that he's excluding accounts with test and admin in the name...)

thanks
0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now