Active Directory authentication using Excel VBA

[DMcClenagan]

Hi,

From an Excel workbook userform, I want to capture a logon name and password, and then authenticate against Active Directory.

The only information I want from the process is whether the user name/password combination is valid in AD.

I know how to do all the Excel stuff (userform, etc.), and I know how to get information from AD using an administrator binding. What I am missing is being able to verify whether the Excel workbook user is the AD user that they claim to be.

Thanks,

Don McClenagan
vbatrain.com

 

[DMcClenagan]

Hi,

I figured out a way to do this, but I had to use a PHP web page and pass it the username and password in the url. I used Ldap_bind to see whether I could bind to Active Directory using the user name and password, and then I put the results in the body of the web page. I used QueryTable from Excel to capture the web page contents, and so I got my authentication.

However, this was really cheating since I used php and VBA. I would still be interested in a purely VBA solution, but I am having my doubts...

Don McClenagan
vbatrain.com

 

[xenou]

Not sure but VB provides an ENVIRON() function that will return the system user (environment variables):

Sub Test()
MsgBox Environ("username")
End Sub

 

[zKy]

You can get the AD username this way:

Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
    tString = ""
    On Error Resume Next
    sLen = GetUserName(rString, 255)
    sLen = InStr(1, rString, Chr(0))
    If sLen > 0 Then
        tString = Left(rString, sLen - 1)
    Else
        tString = rString
    End If
    On Error GoTo 0
    ReturnUserName = UCase(Trim(tString))
End Function

</pre>Stolen from here.

 

[DMcClenagan]

Thanks for the replies.

Both of your methods work for getting the user name of whoever is logged on to the machine. This would work for me in many cases, but not all.

What I am after is making the current user tell the program his user name and password, which the program will then authenticate against AD. I need to know this, since I am then supplying to Excel information for that user's eyes only.

Nonetheless, thanks again. I did not know either of the methods you showed me.

Don McClenagan
vbatrain.com

 

[Kenneth Hobson]

Not sure what you mean by authenticating by Active Directory. You can check for the username by one of those methods. Application.Username is not a reliable way to do that. There are a few other ways to get the username but I find the Environ method the easiest.

As for the password, you would have to set the username and password comparisons in your code. Most use the Textbox control on a userform and set the PasswordChar to something like "*" to hide the password as it is entered.

 

[DMcClenagan]

I should be more clear. Here is what I want to happen:

1. User clicks a button.
2. I load and show a user form with two text boxes, a regular one for a user name and another one for the password (with the little *** in it)
3. User provides that information and clicks an OK button.
4. I collect the data and contact a different computer, the Domain Controller, which controls the Active Directory information, and ask it if the user name that was supplied matches the password for that user (this is what I mean by "authentication")
5. If the user is "authenticated" (i.e., verified as being the Active Directory user they claim to be), then I provide them with information from Active Directory that they are entitled to see.


It is only step 4 that I have been unable to do with Excel VBA alone. As mentioned in a previous post, I can do it with php and VBA, which I think is what I will do unless something better shows up.

Don McClenagan
vbatrain.com

 

[xenou]

It sounds like you could, for instance, not be logged in as "yourself" but still supply your user ID and password...(?)...which you want to verify regardless of who's logged in on the local system (?).

In general you should be able to rely on user's being authenticated in virtue of being logged in to the network (I log in as me, system will faithfully report that its me logged in). So Environ() should be able to tell you whose logged in.

I'm not sure if I quite see the gain - but its a different form of authentication. Environ() has the advantage of polling the O/S so no danger of entering someone else password. But the disadvantage that an unattended machine could be a target to gain access. (Edit - I am assuming I guess that you must be logged in on your local machine here...)

I would imagine that the AD piece requires interface with Windows Server and fairly skilled programming (or networking) knowledge - its certainly beyond me anyway.

Edit (hadn't seen your last post but in any case contacting the domain controller is probably outside the realm of VBA and would require some kind of API call or other programming object (Shell?) to get the message through).

 

[DMcClenagan]

Well, it seems like I might have asked a question that is not in the realm of this forum. If that's the case, sorry about that. But, I just thought I would ask.

As for justifying the logic behind my authentication requirement: I, for one, frequently logon to my company network using a VPN tunnel. In that case, the Environ variable is not my active directory ID, but is whatever I make it on my local machine. I am not the only one that logs in remotely.

Thanks,

Don McClenagan
vbatrain.com

 

[Kenneth Hobson]

Don't give up. A search at google found Active Directory links. I will check back tomorrow if no one has solved it by then.

Some links to try:
http://msdn.microsoft.com/en-us/library/aa772162(VS.85).aspx
http://www.freevbcode.com/ShowCode.Asp?ID=710