Solved

Active Directory authentication/group membership check with MSAccess 2007 VBA

Posted on 2011-09-26
12
1,446 Views
Last Modified: 2012-05-12
I'm unfortunately responsible for maintaining some old interfaces written in VBA through MS Access. Currently I'm migrating these over to a new server and one of the things I want to do is improve the login system for these interfaces by integrating them with Active Directory. It's pretty simple: All I need to do is (a) check to make sure the provided username/password combination are correct, and if so, (b) check to see if the user is in a specific Active Directory group.

Any ideas on how to go about doing this? I had written a library in .NET that these interfaces were using on the old server, but on the new server it's not working properly. If there's some way that I can pretty easily and effectively do it right through VBA, I'd prefer that over having to troubleshoot this library that I wrote.

EDIT: I should add, I've been playing around with ADODB to make the connection. It seems to work and I can look up AD object properties but I'm not sure what the best way is to check the username/password.
0
Comment
Question by:elorc
[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
  • 5
  • 5
  • 2
12 Comments
 
LVL 37

Assisted Solution

by:Neil Russell
Neil Russell earned 250 total points
ID: 36709593
OK, here goes......

You best solution is going to be the fact that the user has ALREADY Authenticated againt the domain when they log in.  Therefore all you need to check is if they are a member of the security group that allows access...

Code below is functional but i just hacked it together from several sources with no inline error checking, but it worked.

In access VBA Insert the snippet below into a a new MODULE

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

Public Const adOpenStatic     As Integer = 3
Public Const adLockReadOnly   As Integer = 1
Public Const adCmdUnspecified As Integer = -1

' Testing function. Replace "NAME OF GROUP" with a real AD Security group name
Sub TestMe()
    Debug.Print IsUserInGroup("NAME OF A GROUP")
End Sub


' returns the  Domain User Name for the currently logged in user
Function ReturnUserName() As String
    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


'Test if the current logged in user is a member of a named group
'DOES NOT WORK WITH Primary Group in AD
Public Function IsUserInGroup(groupName As String) As Boolean

    Dim rs, rs2 As Object
    Dim uName As String
    Dim LdapUser As String
    Dim strSQL As String
    
    Dim i As Integer
    Set rs = CreateObject("ADODB.Recordset")
    
    strSQL = "SELECT * " & _
             "FROM 'LDAP://DC=xshis,DC=nhs,DC=uk'" & _
             "WHERE objectClass='user' AND objectCategory='Person' AND sAMAccountName ='" & ReturnUserName & "' "

    rs.Open strSQL, "Provider=ADSDSOObject;", adOpenStatic, adLockReadOnly, adCmdUnspecified
    
    If Not rs.EOF And Not rs.bof Then
            uName = rs.Fields("ADSPath")
            LdapUser = Mid(uName, 8)
            strSQL = "SELECT * " & _
                     "FROM 'LDAP://DC=xshis,DC=nhs,DC=uk'" & _
                     "WHERE objectClass='Group' AND Member = '" & LdapUser & "' "

            Set rs2 = CreateObject("ADODB.Recordset")
            rs2.Open strSQL, "Provider=ADSDSOObject;", adOpenStatic, adLockReadOnly, adCmdUnspecified
            While Not rs2.EOF And Not rs2.bof
                If (InStr(1, rs2.Fields(0).Value, "CN=" & groupName, vbTextCompare) > 0) Then
                    IsUserInGroup = True
                    GoTo exitFunc
                End If
                rs2.movenext
            Wend
    End If
    IsUserInGroup = False
    
exitFunc:
    'Close connection and tidy up
    rs2.Close
    rs.Close
    Set rs2 = Nothing
    Set rs = Nothing
    
End Function

Open in new window


And there you are. You can test if a user is in a group.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36709830
In addition ... if you wish to protect your data, then place your data in a Shared Folder, then assign an Active Directory group full permissions in to the folder.  All your Front Ends should be local (or private) to the user who has access to the back end.

Also, as an alternative to Neilsr's suggestion, here is what I use to determine the groups the current logged in user is in.  It returns a pipe delimited list of groups the user is in for the passed domain.

Public Function GroupsForCurrentUser(strDomain As String) As String

    Dim sADsPath As String, sGroupName As String _
        , sDescription As String, sUsersGroupList As String
    Dim oTarget As Object
    Dim vGroup As Variant
    Dim wshNetwork As Object
    
    Set wshNetwork = CreateObject("wscript.network")
    
    ' Bind to Computer object
    Set oTarget = GetObject("WinNT://" & strDomain & "/" & wshNetwork.UserName & ",user")
    
    For Each vGroup In oTarget.Groups
        sUsersGroupList = sUsersGroupList & "|" & vGroup.Name
    Next
    
    sUsersGroupList = UCase(sUsersGroupList)
    
    GroupsForCurrentUser = sUsersGroupList & "|"
    
End Function

Open in new window


I will use the function like this ...

If GroupsForCurrentUser("someDomain") Like "*|someGroupName|*"  Then ....

Open in new window

0
 
LVL 1

Author Comment

by:elorc
ID: 36709948
Thanks for the replies. For security and auditing purposes, these interfaces are required to authenticate the user again when they are launched before access can be granted. We are not allowed to assume that the user is authenticated because they are logged into Windows already.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36709969
>>  We are not allowed to assume that the user is authenticated because they are logged into Windows already. <<

Do you mean authenticated to use your app?  or .. Are you not allowed to assume the logged in user is the one in front of the computer?
0
 
LVL 1

Author Comment

by:elorc
ID: 36710130
Whenever they load the interface, it has to prompt them for the password. The assumption is that just because they are logged into Windows does not mean they're the person currently at the keyboard, so the interface must require login credentials every time it's opened.
0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 250 total points
ID: 36710322
The code in the module attached to the post found by clicking this link:

http://www.vbforums.com/showpost.php?s=61328f1d176ebb6d4d6ca86649adace2&p=1411899&postcount=4

Will likely help you out, honestly it was more complex than what I would hope for after doing the same thing in C#, but from a brief read of the code, seems like what you want.  It is likely there are other technics out there, but this one caught my eye.

If this does not work, we can look at some other references I found.
0
 
LVL 1

Author Closing Comment

by:elorc
ID: 36710435
That seems to work. What a cluser though. :)

I've been working on rewriting these crappy interfaces as actual .NET applications, but it's pretty time consuming so it's taking me a while to get them all done. This will keep the ones I haven't gotten to yet limping along for the time being. Thank you.
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 36710462
Nice find! :D

Can I suguest you use the first part to find the current logged on username and just prompt for the password? Avoid a user running the app in another users windows session?  I know i would find that insecure somewhere along the line if your concerns are what they are.

Regards
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36710556
Yes it is a cluster! ... so I kept digging -- it was really bothering me! .. and this is what I came up with.  It works for me on my PC in VBA, but I am an administrator, so I don't know if permissions will be an issue.

Public Function ValidateUser(strDomain As String, strUserName As String, strPassword As String) As Boolean
    
    Dim oADobject As Object 'IADsOpenDSObject
    Set oADobject = GetObject("WinNT:")
    
    On Error Resume Next
    oADobject.OpenDSObject "WinNT://" & strDomain, strUserName, strPassword, &O1 'ADS_SECURE_AUTHENTICATION)
    
    ValidateUser = (Err = 0)
    Err.Clear
       
End Function

Open in new window


By the way, it seems the code I first linked you to was born out of this MS article ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;279815
0
 
LVL 1

Author Comment

by:elorc
ID: 36710666
That second code you provided works under normal users as well. When the login credentials are incorrect it looks like it can sometimes take several seconds to return false, but that's fine. I like the simplicity of that solution.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36710761
Cool! ..

In my testing, I did notice the delay when an error was thrown also.  But, like you, I prefer the simplicity and will take the hit of the small delay versus the hit of a module with code that I would rather not touch!

Either way you go, I am glad the information found helped you out!  I learned some stuff too, so it was a double bonus!

Good luck on your project(s)!
0
 
LVL 1

Author Comment

by:elorc
ID: 36711346
I'll just explain to users that the small delay is "an intentional implementation designed to reduce the effectiveness of brute force attacks."

8)


Thanks again for the assistance that you both provided. I was able to put it all together to do exactly what I was looking for.
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

For anyone that has accidentally used newSID with Server 2008 R2 (like I did) and hasn't been able to get the server running again because you were unlucky (as I was) and had no backups - I was able to get things working by doing a Registry Hive rec…
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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