Get Email Address from User Name 1

[jmeckley]

Here is my situation. I have ASPX pages accessing SQL Server 2000. Is it possible to use the user names in the sysusers table to get their email address through code?

Example:
sysuser table (not all columns are shown)
[tt]ID UserName
1 jmichaels
2 tsmith
3 djones[/tt]

through stored procs and ASP I would like to get their email address
[tt]jmeckley = jon_michaels@work.com
tsmith = tom_smith@work.com
djones = deb_jones@work.com[/tt]

I found IADsUser in the help files, but I cannot figure out how to use it, or what the agruements mean. The example from the help file said:
[tt]Dim usr As IADsUser
Set usr = GetObject("WinNT://Microsoft/JSmith,user&quot
fulNamb = usr.EmailAddress[/tt]

When I entered this into my code file IADsUser gave me an error: [tt]Type 'AIDsUser' is not defined[/tt]. My guess is I am not calling it from the correct location. I may need something like ???.???.IADsUser I don't know what the other things are though.
The other piece I do not understand is the arguement passed to GetObject [tt]"WinNT://Microsoft/JSmith,user"[/tt]. What is this, and how would the path vary for 2K and XP operating systems? The IADsUser type has many other values you can get, email is the one I am looking for.

Maybe I am on to something, maybe I am way off. Thank you in advance for any input you have.

Jason Meckley
Database Analyst
WITF

 

[kaijanm]

You need to add a reference (Add Reference under Project menu) to the COM object called Active DS Type Library. And then on your page add Imports ActiveDS or whatever is appropriate for the language you're using. This will take care of the problem of it not knowing IADsUser.

I'll keep playing with the other part of it and let you know if I can find anything. If you answer your question about the username, please post back!

One of the things I like about these forums is that I get a lot of good ideas from the posts!

Thanks!
Kimberly

 

[jmeckley]

Here is what I have found so far.

with the code
[tt]Dim usr As IADsUser
Dim fulName As String
Set usr = GetObject("WinNT://Microsoft/JSmith,user&quot
fulName = usr.EmailAddress()' or usr.EmailAddress.ToString()[/tt]

However, I do not know how to get it for a different user. The [tt]GetObject[/tt] function should be passed a filename or a class. The text above is taken directly from the help file, and means nothing to me.

I am assuming at this point I need the user's active directory file I want to look up. I don't know if, how, or where these files are located. I also don't know if one person can access another persons file.

Thank you for the path thus far




Jason Meckley
Database Analyst
WITF

 

[kaijanm]

This is the page I found the COM object on. It's for VS 6.0, but you might be able to glean some more info from it.

http://msdn.microsoft.com/library/d...ting_up_visual_basic_for_adsi_development.asp

I don't know why I didn't think to post it before. I wish I had more time to work on this. I'd like to get it figured out.

Thanks!
Kimberly

 

[jmeckley]

I believe I am very close now. Here is what I have/understand so far.

Code:
Dim usr As IADsUser
usr = GetObject("LDAP://W2KRoot/jmeckley,user")
lblEmail.Text = usr.EmailAddress
lblUser.Text = User.Identity.Name

By using the ActiveDS COM Object I can gain access to IADsUser. This is my connection to Active Directory. I then set [tt]usr[/tt] = to the active directory object I want to look at.
1. LDAP is the protocol to access AD
2. W2KRoot is the server AD is running on
3. jmeckley,user is the object I want access to.

I found information on these points from MSDN. I also read that I can query AD directly in Sql Server. I am going over to that message board to see if anyone has done work with AD. Maybe I could write a stored proc that would get the email address for me instead of VB code.

Jason Meckley
Database Analyst
WITF

 

[jmeckley]

I got it to work! I can access Active Directory and get any piece of information I need from it.

I use Sql Server and .Net.

1. Add Active Directory to Sql Server
[tt]sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'[/tt]
ADSI is the name you use to reference Active Directory when you want to use it.

2. Create a view for easy access
[tt]select * from OpenQuery( ADSI,'<LDAP://W2KRoot>;(&(objectCategory=Person)(objectClass=user));name, adspath;subtree')[/tt]
Name and ADsPath are the fields you want to include in the view, I cannot find any other field to add from AD.

3. Write a stored procedure to return the ADsPath
[tt]Create Proc sp_ActiveDirectory_s
@User as char(20)
AS
Select ADsPath
From ADUser_v
Where name = @User[/tt]

4. Add a reference (Menu Project -> Reference) to the COM object called Active DS Type Library

5. Write code to access Active Directory
[tt]Imports ActiveDS
...
Dim usr As IADsUser
Dim ActDir as String
Dim Email as String

ActDir = 'call sp_ActiveDirectory_s
usr = GetObject(ActDir)
Email = usr.EmailAddress[/tt]

There are many more fields from AD that you can access once usr is set. Title, Address, Groups, etc can all be accessed.

I hope this helps some of you out there. Active Directory is a very power tool. If you see anything that can be expanded upon or more effecient please post.

Jason Meckley
Database Analyst
WITF