Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 969
  • Last Modified:

Pulling AD EmployeeID attribute via SQL Server

Hi, I am trying to pull AD data into SQL and I've got everything working except for the employeeID attribute.  I can't get the employeeid values to populate into SQL.  Does anyone have any ideas on why this is not working?  I have attached my code.
SELECT     TOP (100) PERCENT displayName, mobile, facsimiletelephoneNumber, telephoneNumber, mail, department, physicalDeliveryOfficeName, title, givenName, 
                      employeeid
FROM         OPENQUERY(AD, 
                      '
select  givenName,title,
    physicalDeliveryOfficeName,
    department, mail,
    telephoneNumber,  facsimiletelephoneNumber,
    mobile, 
    displayName, employeeid
    
from    ''LDAP://ausdc2:3268/ OU=Dynamic Systems,  DC=fgi_main,DC=local''
where objectCategory = ''Person''  
        and  
         objectClass = ''user'' 

')
                       AS Rowset_1
WHERE     (givenName IS NOT NULL)
ORDER BY displayName

Open in new window

0
imstac73
Asked:
imstac73
  • 6
  • 4
  • 2
1 Solution
 
HainKurtSr. System AnalystCommented:
do you get null in the result?
0
 
HainKurtSr. System AnalystCommented:
do you have employeeid in LDAP?

do you have any other tool to query LDAP, and from that tool, do you get employeeid?
0
 
imstac73Author Commented:
Yes
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
HainKurtSr. System AnalystCommented:
whats is so special for employeeid in LDAP? do you see any difference between employeeid & other fields?
0
 
imstac73Author Commented:
I don't know. I don't deal with the AD side at all; only SQL.  
0
 
imstac73Author Commented:
My AD guy was able to query the employeeid using another tool and the data is there.
0
 
HainKurtSr. System AnalystCommented:
is it one value or multiple values? what happens if you use empid instead of employeeid
does it crash or give null again...
0
 
imstac73Author Commented:
It gives an error if I use empid.
0
 
afinneranCommented:
Try ldp.exe to query AD. http://support.microsoft.com/kb/224543

If you google it you should find the download somewhere. Run it from your desktop and connect with your AD account and you should have rights to browse the AD tree. There may be more than one piece of data stored in the employeeid field and using ldp.exe will tell you what is in there. In general LDAP is case sensitive and you also need the full DN which can be hard to get sometimes.

Not sure from the SQL side but the user making the LDAP call also needs read rights to AD. Would you get an error if the authentication failed?
0
 
imstac73Author Commented:
I'm doubtful that it is permissions as I am able to pull other AD attributes.
0
 
afinneranCommented:
Is employeeid in the same container (CN)and at the same level as the others?

displayName, mobile, facsimiletelephoneNumber, telephoneNumber, mail, department, physicalDeliveryOfficeName, title, givenName
0
 
imstac73Author Commented:
Turns out that by changing the port number used in the connection to the domain controller server solved the problem.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now