Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

LDAP query works, but GC query fails

Posted on 2008-10-07
12
Medium Priority
?
2,190 Views
Last Modified: 2012-05-05
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
Comment
Question by:JammyPak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 51

Expert Comment

by:ahoffmann
ID: 22666552
silly question: does your database server support GC:// as source?
0
 
LVL 16

Author Comment

by:JammyPak
ID: 22669816
yes, the GC:// query works fine if I query on different users, it's only new hires from the past month that fail.
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 22669902

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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 16

Author Comment

by:JammyPak
ID: 22671749
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
 
LVL 71

Expert Comment

by:Chris Dent
ID: 22672796

Do you have more than one Global Catalog?

Chris
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 22672804

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
 
LVL 16

Author Comment

by:JammyPak
ID: 22677782
yes, more than one GC. I've tried pointing at different GCs but the results are the same.
0
 
LVL 71

Assisted Solution

by:Chris Dent
Chris Dent earned 400 total points
ID: 22677912

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
 
LVL 18

Expert Comment

by:BSonPosh
ID: 22678465
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
 
LVL 16

Author Comment

by:JammyPak
ID: 22689077
I'll give it a try and report back...
0
 
LVL 16

Author Comment

by:JammyPak
ID: 22710996
both of those scripts return the affected users just fine.
0
 
LVL 16

Accepted Solution

by:
JammyPak earned 0 total points
ID: 22722437
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Always backup Domain, SYSVOL etc.using processes according to Microsoft Best Practices. This is meant as a disaster recovery process for small environments that did not implement backup processes and did not run a secondary domain controller that ne…
In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question