Hi all,
I am having trouble with connecting to my companies LDAP to retrieve userinfo. I can return basic information from my local area using the following connection settings: - Set oRoot = GetObject("LDAP://rootDSE")
-
'work in the default domain
-
sDomain = oRoot.Get("defaultNamingContext")
-
Set oDomain = GetObject("LDAP://" & sDomain)
-
sBase = "<" & oDomain.ADsPath & ">"
However this is producing the server as being DC=ww007,DC=mycompany,DC=net. Which does not have available the same information as our company wide ldap.
I have use JXplorer to find my site within our huge directory (over 400k employees) and the connection would be: - ldap://scd2ldap.mycompany.net:389/cn=MCGUINNESS NAME Z002DTRW,l=NUR S,ou=E F,o=mycompany,c=GB??base?(objectClass=*)
The attributes available to me are vast such as gender/department/faxnumber etc etc.
I want to be able to enter the cn value and return all of the other attributes associated with that.
My attempted code has always failed and this is what I am using at the moment:
As stated this is not currently connecting to the company wide ldap as far as I can see, or atleast I am not understanding how to return company specifc attributes.
Anyone able to guide me here? I am complete stumped.
12 6100 NeoPa 32,556
Expert Mod 16PB
Unfortunately, since I left my last company I no longer have access to the work I did using LDAP on my domain controllers :-(
I remember I found a lot out from googling though - I particularly played with LDIFDE.Exe. A very powerful tool, but great care needed to avoid doing damage if using an Admin account.
I guess it's your original GetObject line that's faulty, but I'm not familiar enough with your setup, or of using LDAP outside my quite narrow requirements as they were, to point you much better I'm afraid.
I have been googling for days now and I feel sometimes I get what's going on and then other times it is all a confused mess. I think the fact that I cant get anything being returned when I try to connnect to the full directory is what is causing the most frustration.
I can't get my head around whether infact I am capable of getting all these extra company specific attributes using my local settings or whether they are only held by connecting to the full server. Intellisense (if thats the correct name) brings up the basic attributes when using the code pasted above, but as I said, its the company specific ones which never seem to return.
NeoPa 32,556
Expert Mod 16PB
Well, I'll be happy to share with you the work I did on this if I ever manage to get a copy of it, but it sounds like your domain/forest setup may be more complicated than mine was so you need to determine exactly how that works before anything I can give you will help too much I suspect.
The bottom line is that even with my old code, I needed a thorough understanding of what was what as far as servers and domain were concerned before I could use the concepts in anger.
I have just taken a look at the basic information using dsquery.
What I found is infact I can search within my ww007 server and find myself (obviously) which leads me on to two conclusions.
Firstly I was able to find that I can do an advanced search which has within it all of the company attributes which I require.
Secondly this now means that I do not need to connect to the overall company wide activedirectory i.e. scd2ldap.company.net , I can simply use the local binding I get due to being connected to the ww007 server (where all the users are that I require further info on).
However this does mean I need to understand how to obtain these other attributes.
As stated earlier I am able to return the basics, following on from what the vba help brings up when querying user attributes, however I need to find out how to get the non standard attributes out of the AD. - With user
-
-
sAns = "First Name: " & .FirstName & vbCrLf
-
sAns = sAns & "Last Name " & .LastName & vbCrLf
-
This is the part of the code I use to get the normal attributes. But it will not bring up the others held within the AD that seem to be more company specific. Is there a different object I need to be referencing other than "user" I wonder..
NeoPa 32,556
Expert Mod 16PB
You need to find your way around the fundamental structure of course. Users are items, but there are various other items as well that you can probably discover by playing with the ADUC (Active Directory Users and Computers) plug-in. There are other AD plug-ins that give other info of course, but I found most of what I needed in ADUC.
To find the correct LDAP names for all the available properties I used LDIFDE.Exe to do a full (or partial) dump of a folder that I'd identified in my search through ADUC (The main Users folder where all my users were stored). With that dump I had a pretty full list of all the user attributes I needed. The top level structure of the tree is also quite available by searching I expect, but the specific attributes are another matter. I came across one or two, but the list I found in the LDIFDE dump was much fuller, even if without any particular explanations. You can work out most of the ones you need though, and test them by putting dummy values in on a dummy user. If the value changes then you know which attribute name reflects the item you changed.
NeoPa I have done a bit more digging around the attributes available to me.
It seems the global AD (named scd2) contains more information than our local AD (within my ww007 server for the offices). After running a search on myself using ADUC I am presented with my profile on the ww007 server which containes quite a lot of attributes but only around 10 of the 40 contain any data.
These attributes are similar to those on the corporate directory (scd2) but the scd2 has a lot more attributes, which I have viewed using some free LDAP browsing software (such things as gender etc. which the local AD doesnt seem to have as an attribute at all.)
This leaves me with the issue that the information I need is definitely on this corportate directory.
I have used the following code succesfully on my local directory to return its attributes: - Private Sub cmdSample_Click()
-
'Code to retrieve information from the Active Directory given the SN.
-
'Change dc=cityofcomputer and OU information.
-
'Searches in givenName if not found in SN AD field.
-
Dim varInfo As Variant
-
varInfo = ""
-
Dim LN As Variant
-
LN = InputBox("Enter SN:")
-
Const ADS_SCOPE_SUBTREE = 2
-
-
Set objConnection = CreateObject("ADODB.Connection")
-
Set objCommand = CreateObject("ADODB.Command")
-
objConnection.Provider = "ADsDSOObject"
-
objConnection.Open "Active Directory Provider"
-
Set objCommand.ActiveConnection = objConnection
-
-
objCommand.Properties("Page Size") = 1000
-
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
-
-
objCommand.CommandText = _
-
"SELECT Name,AdsPath,givenName,SN,title,telephonenumber,Department,OU, CN, initials, displayname, " _
-
& "userAccountControl, sAMAccountName, distinguishedName, physicalDeliveryOfficeName, mail,wWWHomePage,homePhone,pager,mobile,ipPhone,Info " _
-
& "FROM 'LDAP://OU=550,OU=Depts,OU=Users,OU=E7GN,OU=E7G,OU=EUsers,DC=ww007,DC=company,DC=net' WHERE " _
-
& "objectCategory='user' And SN= '" & LN & "'"
-
-
'objCommand.CommandText = _
-
' "SELECT Name,AdsPath,givenName,SN,title,telephonenumber,Department,OU, CN, initials, displayname, " _
-
' & "physicalDeliveryOfficeName, mail,wWWHomePage,homePhone,pager,mobile,ipPhone,Info " _
-
' & "FROM 'LDAP://dc=cityofmadison,dc=com' WHERE " _
-
' & "objectCategory='user' And SN= '" & LN & "'"
-
-
'objCommand.CommandText = _
-
' "SELECT * FROM 'LDAP://dc=cityofmadison,dc=com' WHERE " _
-
' & "objectCategory='user' And SN = '" & LN & "'"
-
-
Set objrecordset = objCommand.Execute
-
If objrecordset.EOF And objrecordset.BOF Then
-
objrecordset.Close
-
MsgBox "No Records match...Trying searching on GivenName."
-
objCommand.CommandText = _
-
"SELECT Name,AdsPath,givenName,SN,title,telephonenumber,Department,OU, CN, initials, displayname, " _
-
& "userAccountControl, sAMAccountName, distinguishedName, physicalDeliveryOfficeName, mail,wWWHomePage,homePhone,pager,mobile,ipPhone,Info " _
-
& "FROM 'LDAP://OU=550,OU=Depts,OU=Users,OU=E7GN,OU=E7G,OU=EUsers,DC=ww007,DC=company,DC=net' WHERE " _
-
& "objectCategory='user' And givenname = '" & LN & "'"
-
Set objrecordset = objCommand.Execute
-
If objrecordset.EOF And objrecordset.BOF Then
-
MsgBox "No records found matching on GivenName."
-
objrecordset.Close
-
Exit Sub
-
End If
-
End If
-
objrecordset.MoveFirst
-
-
Do While Not objrecordset.EOF
-
varInfo = "Name = " & objrecordset.Fields("Name").Value & Chr(10) _
-
& "AdsPath = " & objrecordset.Fields("Adspath").Value & Chr(10) _
-
& "givenName = " & objrecordset.Fields("givenName").Value & Chr(10) _
-
& "SN = " & objrecordset.Fields("SN").Value & Chr(10) _
-
& "title = " & objrecordset.Fields("title").Value & Chr(10) _
-
& "Telephonenumber = " & objrecordset.Fields("telephonenumber").Value & Chr(10) _
-
& "Department = " & objrecordset.Fields("Department").Value & Chr(10) _
-
& "CN = " & objrecordset.Fields("CN").Value & Chr(10) _
-
& "OU = " & objrecordset.Fields("OU").Value & Chr(10) _
-
& "Displayname = " & objrecordset.Fields("Displayname").Value & Chr(10) _
-
& "Initials = " & objrecordset.Fields("Initials").Value & Chr(10) _
-
& "PhysicalDeliveryOfficeName = " & objrecordset.Fields("PhysicalDeliveryOfficeName").Value & Chr(10) _
-
& "Mail = " & objrecordset.Fields("Mail").Value & Chr(10) _
-
& "wWWHomePage = " & objrecordset.Fields("wWWHomePage").Value & Chr(10) _
-
& "HomePhone = " & objrecordset.Fields("HomePhone").Value & Chr(10) _
-
& "Pager = " & objrecordset.Fields("Pager").Value & Chr(10) _
-
& "Mobile = " & objrecordset.Fields("Mobile").Value & Chr(10) _
-
& "ipPhone = " & objrecordset.Fields("ipPhone").Value & Chr(10) _
-
& "Info = " & objrecordset.Fields("Info").Value & Chr(10) _
-
& "distinguishedName = " & objrecordset.Fields("distinguishedName").Value & Chr(10) _
-
& "sAMAccountName = " & objrecordset.Fields("sAMAccountName").Value & Chr(10) _
-
& "userAccountControl (512=unlocked <>512=locked) = " & objrecordset.Fields("userAccountControl").Value & Chr(10) _
-
' & "userPrincipalName = " & objrecordset.Fields("userPrincipalName").Value & Chr(10) _
-
-
MsgBox varInfo
-
objrecordset.MoveNext
-
Loop
-
End Sub
However when I replace: - 'LDAP://OU=550,OU=Depts,OU=Users,OU=E7GN,OU=E7G,OU=EUsers,DC=ww007,DC=company,DC=net'
With: - l=NUT S,ou=E F,o=SIEMENS,c=GB,dc=scd2ldap,dc=company,dc=net
Where I know using the ldap browser software that NUT S is the container for all users I just keep getting table does not exist errors :|
After 5 hours of testing and messing around I have finally brought up some sign of progress!
This does not currently do what I want but it has finally shown I can query the corporate directory (world wide) using code.
The following checks my local site for female employees ( OI OI! ): - Sub gogdfgdf()
-
-
-
Set ado = CreateObject("ADODB.Connection")
-
ado.Provider = "ADSDSOObject"
-
ado.Properties("User ID") = ""
-
ado.Properties("Password") = ""
-
ado.Properties("Encrypt Password") = False
-
ado.Open "ADS-Anon-Search"
-
-
'ServerName = "scd2ldap.mycompany.net/cn=Z0003PJO,l=NUT S,ou=E F,o=mycompany,c=GB"
-
ServerName = "scd2ldap.mycompany.net/l=NUT S,ou=E F,o=mycompany,c=GB"
-
filterStr = "(&(objectclass=scdPerson)(gender=F))"
-
-
Set objectList = ado.Execute("<LDAP://" & ServerName & ">;" & filterStr & ";ADsPath;SubTree")
-
-
While Not objectList.EOF
-
Debug.Print objectList.Fields(0).Value
-
objectList.MoveNext
-
Wend
-
-
-
-
End Sub
-
I found that one of the objectclasses were titled scdPerson where most other scripts/AD's use "user". Hopefully by changing this class object I can discover all of the attributes as NeoPa stated earlier.
At the moment also the data is presented as:
LDAP://scd2ldap.mycompany.net/cn=SANDRA Z002WERNP2H,l=NUT S,ou=E F,o=mycompany,c=GB
LDAP://scd2ldap.mycompany.net/cn=ANNE Z002NUEWRYD,l=NUT S,ou=E F,o=mycompany,c=GB
LDAP://scd2ldap.mycompany.net/cn=KIRSTY Z000WR3PIY,l=NUT S,ou=E F,o=mycompany,c=GB
LDAP://scd2ldap.mycompany.net/cn=MARY Z000R3PIZ,l=NUT S,ou=E F,o=mycompany,c=GB
I will need to work on outputting this in a more userfriendly manner that I can use in my databases.
Ok I am much further on now and I think I am on to my final issue.
I use the following function to do a debug.print of info from the corporate directory based on the second name of the person I want to find: - Public Function UserInfo12(LoginName As String) As String
-
-
Dim rs As ADODB.Recordset
-
Dim sBase As String
-
Dim sFilter As String
-
Dim sDomain As String
-
Dim sAttribs As String
-
Dim sDepth As String
-
Dim sQuery As String
-
Dim sAns As String
-
Dim user As IADsUser
-
Dim counter As Integer
-
On Error GoTo ErrHandler:
-
-
Set ado = CreateObject("ADODB.Connection")
-
ado.Provider = "ADSDSOObject"
-
ado.Properties("User ID") = ""
-
ado.Properties("Password") = ""
-
ado.Properties("Encrypt Password") = False
-
ado.Open "ADS-Anon-Search"
-
-
servername = "scd2ldap.mycompany.net/l=NUT S,ou=E F,o=mycompany,c=GB"
-
-
sBase = "<LDAP://" & servername & ">"
-
sFilter = "(&(objectClass=*)(cn=" & LoginName & "))"
-
sAttribs = "adsPath"
-
sDepth = "subTree"
-
-
sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth
-
-
-
Set rs = ado.Execute(sQuery)
-
-
-
Do While Not rs.EOF
-
Set user = GetObject(rs("adsPath"))
-
-
'Debug.Print rs.RecordCount
-
'set counter to 0 before looping
-
With user
-
-
On Error Resume Next
-
-
sAns = "Gender: " & .gender & vbCrLf
-
sAns = sAns & "Display Name:" & .DisplayName & vbCrLf
-
sAns = sAns & "Company:" & .company & vbCrLf
-
sAns = sAns & "Department:" & .departmenttext & vbCrLf
-
sAns = sAns & "Locality:" & .localitynational & vbCrLf
-
sAns = sAns & "E mail:" & .mail & vbCrLf
-
sAns = sAns & "Function:" & .mainfunction & vbCrLf
-
sAns = sAns & "Phone:" & .mobile & vbCrLf
-
sAns = sAns & "GID:" & .tcgid & vbCrLf
-
sAns = sAns & "CN:" & .CN & vbCrLf
-
sAns = sAns & "Cost unit location:" & .costlocationunit & vbCrLf
-
sAns = sAns & "Cost location:" & .costlocation & vbCrLf
-
sAns = sAns & "Nick name:" & .nickname & vbCrLf
-
sAns = sAns & "title:" & .Title & vbCrLf
-
sAns = sAns & "Organisation:" & .o & vbCrLf
-
End With
-
Debug.Print sAns
-
'UserInfo12 = sAns
-
rs.MoveNext
-
Loop
-
-
-
-
'uncomment below to return function value
-
'UserInfo12 = sAns
-
'Debug.Print sAns
-
ErrHandler:
-
-
On Error Resume Next
-
If Not rs Is Nothing Then
-
If rs.State <> 0 Then rs.Close
-
Set rs = Nothing
-
End If
-
-
If Not ado Is Nothing Then
-
If ado.State <> 0 Then ado.Close
-
Set ado = Nothing
-
End If
-
-
-
End Function
However the function only returns 1 value when I try to re-implement the functions return value. The debug.print line prints out 5 records since there are multiple people with the same second name.
NeoPa 32,556
Expert Mod 16PB Munkee: - l=NUT S,ou=E F,o=SIEMENS,c=GB,dc=scd2ldap,dc=company,dc=net
Should it be l=NUT S, or ou=NUT S, ? Unfortunately I have nothing here to test on so everything I say has to be from memory, and this work was all a few years ago now.
NeoPa 32,556
Expert Mod 16PB Munkee:
The following checks my local site for female employees ( OI OI! ):
I had to laugh :-D What more do you need? I almost ask. Munkee:
However the function only returns 1 value when I try to re-implement the functions return value.
I'm afraid I never accessed this via ADODB. Your code is not familiar to me, and I don't know the circumstances under which rs.EOF would be set TRUE (thereby ending your loop). I would suggest looking there though, or even debugging through the code to see exactly where it differs from your expectations (See Debugging in VBA).
I would also suggest looking into indenting of code. I'm not sure why an experienced programmer such as yourself would not indent their code, but if it's not something you've come across then I seriously suggest you look into it. It not only makes it very much easier for other coders to review your work, but it also makes it easier for you to see what bits of code go with what. Non-indented code is so much harder to work with. Only incorrectly indented code is harder (of which I'm sorry to say we see a fair bit of in some of our questions).
Lastly, what a good job you've made already, digging into this notoriously difficult subject. Not everyone sticks at it long enough to get results. Good luck in your ongoing endeavours.
Thank you for all of the flattery, experienced programmer I am far from! but I do like to research a good challenge. I like to think the vast majority of my code resembles that of franken-vba where I seem to have become quite proficient at bolting parts of every one elses together and stitching it all up to work.
I have some very bad habits which I need to get out of, such as remembering to indent.. and removing redundant code.
Anyway after yet more digging the final result is nearly there and the thread can come to an end: - Public Function UserInfoo(LoginName As String) As String
-
-
Dim rs As ADODB.Recordset
-
Dim sBase As String
-
Dim sFilter As String
-
Dim sDomain As String
-
Dim sAttribs As String
-
Dim sDepth As String
-
Dim sQuery As String
-
Dim sAns As String
-
Dim user As IADsUser
-
Dim counter As Integer
-
Dim bigstring As String
-
Dim strHeaders As String
-
-
On Error GoTo ErrHandler:
-
-
Set ado = CreateObject("ADODB.Connection")
-
ado.Provider = "ADSDSOObject"
-
ado.Properties("User ID") = ""
-
ado.Properties("Password") = ""
-
ado.Properties("Encrypt Password") = False
-
ado.Open "ADS-Anon-Search"
-
-
servername = "scd2ldap.mycompany.net/l=NUT S,ou=E F,o=SIEMENS,c=GB"
-
-
sBase = "<LDAP://" & servername & ">"
-
sFilter = "(&(objectClass=*)(cn=" & LoginName & "))"
-
sAttribs = "adsPath"
-
sDepth = "subTree"
-
-
sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth
-
-
-
Set rs = ado.Execute(sQuery)
-
strHeaders = "Gender;Display Name;Company;Department;Locality;E mail;function;phone;GID;CN;cost unit location;costunit;nickname;title;organisation;"
-
-
Do Until rs.EOF
-
Set user = GetObject(rs("adsPath"))
-
sAns = ""
-
'Debug.Print rs.RecordCount
-
'set counter to 0 before looping
-
With user
-
'need some error coding for IF IS NULL then leave BLANK value in there. THis will stop the stuff displaying crap in the listbox
-
On Error Resume Next
-
sAns = sAns & .gender & ";" & .DisplayName & ";" & .Company & ";" & .departmenttext & ";" & .localitynational & ";" & .mail & ";" _
-
& .mainfunction & ";" & .mobile & ";" & .tcgid & ";" & .CN & ";" & .costlocationunit & ";" & .costlocation & ";" & .nickname & ";" _
-
& .Title & ";" & .o & ";"
-
End With
-
Forms.form1.listbox1.AddItem (sAns)
-
rs.MoveNext
-
Loop
-
bigstring = strHeaders & sAns
-
'bigstring = strHeaders & sAns
-
Debug.Print bigstring
-
Forms.form1.listbox1.ColumnCount = 16
-
Forms.form1.listbox1.RowSourceType = "Value List"
-
Forms.form1.listbox1.ColumnHeads = True
-
'Forms.form1.listbox1.RowSource = bigstring
-
'uncomment below to return function value
-
-
'Debug.Print sAns
-
ErrHandler:
-
-
On Error Resume Next
-
If Not rs Is Nothing Then
-
If rs.State <> 0 Then rs.Close
-
Set rs = Nothing
-
End If
-
-
If Not ado Is Nothing Then
-
If ado.State <> 0 Then ado.Close
-
Set ado = Nothing
-
End If
-
-
-
End Function
-
Function will output to a listbox showing all records within the recordset using the very simple .additem within the loop. Now for the tidying up!
NeoPa 32,556
Expert Mod 16PB Munkee:
franken-vba
I ROFLed (well, guffawed loudly anyway. I hope I didn't wake the wife).
Good for you on finding the solution. I feel a bit like a tutor - where I was unable to tell you any answers (in my case bacause I had no access to any) but only give a few bare tips and send you on your way to flesh them out into a workable solution. I'm very pleased that you managed to in the end. Congratulations :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bonj |
last post by:
Like a web app can store its connection string in the web.config file, can a
windows forms or console app or windows service store its connection string
in app.config?
Is it advisable?
I know...
|
by: Tdar |
last post by:
Sorry for the dup post but looking for a response and this is being posted
under my MSDN
managed newsgroups handle and in a different newsgroup
Hi,
As I said in the past post I am using this...
|
by: sam44 |
last post by:
Hi,
At startup the user log on and chooses the name of a client from a
dropdownlist, which then changes dynamically the connection string (the
name of the client indicates which database to use)....
|
by: mbasil77 |
last post by:
I'm trying to port a piece of Java LDAP conneciton code to DOTNET.
I've done LDAP in DOTNET before, but I keep getting a very strange
message. The Java code looks like:
public static boolean...
|
by: anirudh lokray |
last post by:
Hi friends....
I am working on a C# application with connects to the SQL Server 2000 database giving the server name, username, password, max pool size, database name as the parameters. When i...
|
by: shofu_au |
last post by:
Hi Group,
A question about threads and asynchronous TCP sockets. In the
attached code, even if the host TCP server is not running my code
reports that the connection has been established.
...
|
by: =?Utf-8?B?Y2FzaGRlc2ttYWM=?= |
last post by:
Hi,
I have recieved the following error in an application:
"Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. This may have occured because all...
|
by: nidhijani |
last post by:
Hey
giv me some idea abt connection of an aspx form wth SQL Server2005
in .net 2005
|
by: Nullabee |
last post by:
Hi All.
I used to connect to a DB2 database on an iSeries (V5R2) through DB2 Connect 8.2 PE.
On my new PC, I installed DB2 Database 9, and got no mention of any licensing problems, until I tried...
|
by: kmcq |
last post by:
I've contacted my host and they have no idea what connection string I need to use to access the MS SQL database we have running on their equipment.
I'm trying to connection from ASP using OLE.
...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |