Results 1 to 7 of 7
  1. #1
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875

    Authenticate to Active Directory From Access

    In my last post (here), I mentioned the possibility of authenticating to Active Directory as a means of securing an Access Database.

    Well, I've been looking into this more and I can't figure out how to do it. I've found plenty of snippits that look at the currently logged on user, but none that actually go through the AD authentication process (with a password and everything).



    What I'm thinking of doing (or trying, really) is fairly simple:
    • A user opens the Access Database and is presented with a log on Form.
    • They enter a Username and Password which Access compares against Active Directory.
      • If Access finds that user and the password authenticates, Access checks to see if they're in the "Access Database Users" Group (again, in Active Directory).
        • If they are a member of that group, then Access brings up the main Database Switchboard.
        • If not, then Access gives them an error message and lets them try to log in again.

      • If the user doesn't authenticate, then Access gives them an error message and lets them try to log in again.

    • And, of course, after multiple failed tries (3-5), the database closes and/or sends an alert to the admin (me).


    I want Access to query the user/pass so that a different user than the one logged on to the local computer can connect to the Database with their own (separate) username and permissions. Also, so random people can't just open up Access and mess with the DB if the user leaves the computer unattended without locking it.

    Anyone have any idea on how to actually perform that log on process? I can create the log on Form, query the username and group membership, but I haven't found a way to verify the password.

  2. #2
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    EUREKA!!!!

    Code:
    Public Function AuthenticateAD(Username As String, Optional Password As String = "") As Boolean
      On Error GoTo Error_AuthenticateAD
    
      Dim adoConn As New ADODB.Connection
      Dim rst As ADODB.Recordset
      Dim objDomain As Object
      Dim objUser As Object
    
      Dim strAttribs As String
      Dim strBase As String
      Dim strDepth As String
      Dim strFilter As String
      Dim strLoginName As String
      Dim strQuery As String
    
      AuthenticateAD = False
    
      Set objDomain = GetObject("LDAP://" & GetObject("LDAP://rootDSE").Get("defaultNamingContext"))
      strBase = "<" & objDomain.ADsPath & ">"
      strAttribs = "adsPath"
      strDepth = "subTree"
    
      strFilter = "(&(objectCategory=person)" & _
                  "(objectClass=user)" & _
                  "(cn=" & Username & "))"
      strQuery = strBase & ";" & strFilter & ";" & strAttribs & ";" & strDepth
    
      On Error Resume Next ' Don't break on failed login
      adoConn.Open "Data Source=Active Directory Provider;Provider=ADsDSOObject", Username, Password
      Set rst = adoConn.Execute(strQuery)
    
      Debug.Print "Login Attempt 1, Username=" & Username & ", Error=" & Err.Number
    
      If Err.Number = -2147217911 Then
        ' User Name and SAM Account Name may be different, check!
        On Error GoTo 0
    
        adoConn.Close
        adoConn.Open "Data Source=Active Directory Provider;Provider=ADsDSOObject" ' Connect anonymously to search for User Name
    
        ' Look for the CN of our current User ID
        strFilter = "(&(objectCategory=person)" & _
                    "(objectClass=user)" & _
                    "(sAMAccountName=" & Username & "))"
    
        strQuery = strBase & ";" & strFilter & ";" & strAttribs & ";" & strDepth
        Set rst = adoConn.Execute(strQuery) ' Search!
    
        If Not rst.RecordCount = 0 Then
          Set objUser = GetObject(rst("adsPath"))
    
          strLoginName = objUser.cn
    
          Set objUser = Nothing
    
          On Error Resume Next
          Err.Clear
    
          adoConn.Close
          adoConn.Open "Data Source=Active Directory Provider;Provider=ADsDSOObject", strLoginName, Password
          Set rst = adoConn.Execute(strQuery)
    
          Debug.Print "Login Attempt 2, Username=" & strLoginName & ", Error=" & Err.Number
    
          On Error GoTo 0
        End If
      End If
    
      If Err.Number = 0 Then
        AuthenticateAD = True
      End If
    
    Function_Closing:
      If Not rst Is Nothing Then
        If rst.State <> 0 Then
          rst.Close
        End If
    
        Set rst = Nothing
      End If
    
      If Not adoConn Is Nothing Then
        If adoConn.State <> 0 Then
          adoConn.Close
        End If
    
        Set adoConn = Nothing
      End If
    
      Set objDomain = Nothing
    
      Exit Function
    
    Error_AuthenticateAD:
      AuthenticateAD = False
    
      Resume Function_Closing
    End Function
    The above function will return True if the user exists in Active Directory AND the supplied password matches. I had to add an extra check because (at least where I work), the actual name of the User Object in AD and the "login account name" aren't always the same.

  3. #3
    ifjake is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2014
    Posts
    1
    This didn't work until I commented out the "On Error GoTo 0" after the second login attempt.

    Code:
    Debug.Print "Login Attempt 2, Username=" & strLoginName & ", Error=" & Err.Number
    
    
    'On Error GoTo 0
    I am incredibly green at VBA and access, but that command seems to reset the error number to 0, which nullifies that last check, making this always return true. The debug log looks right as rain though, and that second check where it searched for the SAM account name proved necessary for my place of employment, so I echo, EUREKA!!!

    Thanks!

  4. #4
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    On Error GoTo 0 is VBA-speak for "Turn error trapping back on using the last saved settings" (in this case, it re-enables jumping out to the label Error_AuthenticateAD on an error).

    It doesn't surprise me that you had to modify my code though. The AD setup here is what one would charitably call "a mess". However, I'd be careful with uncommenting that line in this instance as that might mean you're not actually verifying the user's password is correct.

    I'd try it a couple of times with both good and bad passwords and check the debug output to get the specific error number (if there is one).

  5. #5
    lsalvucci is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2014
    Posts
    3
    Hello,
    I'm interested in using this code to also authenticate against AD but not 100% sure how to use this with my login form. Can you possibly shed some light on how you set your DB up with this code?
    Thanks
    Larry

  6. #6
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Quote Originally Posted by lsalvucci View Post
    Hello,
    I'm interested in using this code to also authenticate against AD but not 100% sure how to use this with my login form. Can you possibly shed some light on how you set your DB up with this code?
    Thanks
    Larry
    The above function does all the work for you of finding your Active Directory on the network, identifying the default ou, etc. So really all you need to supply are the username and password to check.

    Just create a Login Form that pops up and prompts the user to log in. Then, if they authenticate correctly (if the function above returns true), you close the Login Form and open your main switchboard Form (or whatever starting Form you have set up).

    The Login Form should have two text fields where they user enters their username and password, and a button that passes those fields to the above function when clicked. If the result is true, the Switchboard Form loads and the user continues on. If it returns false, they user is given another chance to enter their password.

    I'd also recommend using some method of limiting the number of bad passwords a user can enter (have the Form close Access after three bad passwords in a row) or of forcing a delay of several seconds after a bad password is entered.

  7. #7
    lsalvucci is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2014
    Posts
    3

    Testing the Authentication of the username and password doesn't seem to work correctly.

    I don't believe that code authenticates the Password as well. I've been tinkering with it and it only gives a true value if the username is authenticated. I tried putting in a bad password along with the correct username and it still returned a value of TRUE. Is there more to that code that does the authentication of the password?


    Quote Originally Posted by Rawb View Post
    The above function does all the work for you of finding your Active Directory on the network, identifying the default ou, etc. So really all you need to supply are the username and password to check.

    Just create a Login Form that pops up and prompts the user to log in. Then, if they authenticate correctly (if the function above returns true), you close the Login Form and open your main switchboard Form (or whatever starting Form you have set up).

    The Login Form should have two text fields where they user enters their username and password, and a button that passes those fields to the above function when clicked. If the result is true, the Switchboard Form loads and the user continues on. If it returns false, they user is given another chance to enter their password.

    I'd also recommend using some method of limiting the number of bad passwords a user can enter (have the Form close Access after three bad passwords in a row) or of forcing a delay of several seconds after a bad password is entered.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Active Directory & Access 2010: Importing Users
    By royarellano in forum Access
    Replies: 0
    Last Post: 12-08-2011, 02:32 AM
  2. MS Active Directory
    By pkelly in forum Access
    Replies: 9
    Last Post: 10-21-2011, 08:26 AM
  3. access+Active directory
    By cpcp in forum Access
    Replies: 6
    Last Post: 11-15-2010, 02:30 AM
  4. Active Directory coding
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-28-2010, 08:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums