Authenticate user using Active Directory/Windows authentication in MS Access 2007

Question

We have application built on MSAccess 2007. We want to add a new user login concept for this application. I want to Authenticate user using Active Directory/Windows authentication.I want create a log file for this user. (like we do for .net applications using forms authentication) How do I do that on MS Access 2007.

Furthermore this application runs 24 hours, it will not be shutdown. There can be multiple users using this application. As I said this application is used 24/7, There are multiple shifts running and different users login and logout. During the user login and logout session, we need to keep track of the log for a particular user. This application uses SQL server 2005 as database server.
Your advise is a great help for me, to develop this kind of module on MS Access 2007

Kingsize

I don't believe you can do what you're asking for. Using the Windows user logon (AD is not something special -- it's just a UI to NTFS users/security) you'll have to force the user to log off Windows and log back on. – Ryder

Same(?) question for old versions of Access: https://mcmap.net/q/412669/-ms-access-permissions-with-active-directory-users/321973 – Werewolf

Maybe related: support.microsoft.com/en-us/help/316748/… – Werewolf

Answer 1

Since the user has to log on to the pc, then you can simply grab their logon by using the code from http://www.mvps.org/access/api/api0008.htm:

' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If ( lngX > 0 ) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

And, the following is a vbs script, but it should work in Access just fine also:

Displays Group Membership and Active Directory Location

The following code can be run to display the group membership of an Active Directory group and also let you know each member’s LDAP Distinguished Name. The output will name the text file the group name and will include all the members and their location in Active Directory. Just copy this into a txt file and rename to .vbs Enjoy!

Set objGroup = GetObject(“LDAP://cn=GroupName,ou=OUName,DC=DomainName,DC=local“)
Set objFileSystem = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFileSystem.OpenTextFile(objGroup.Get(“name”) & ” – Members.txt“, 2, True, 0)
For Each objMember in objGroup.Members
  objFile.WriteLine objMember.Get(“sAMAccountName”) & VbTab & _
    objMember.Get(“cn”) & VbTab & _
    objMember.Parent
Next
Set objFile = Nothing
Set objFileSystem = Nothing
Set objGroup = Nothing

Copalm

Thank you Albert D. Kallal. Its works fantastic. Yes I actually wanted to know the network userid. I can log the details when ever the user is logged in and logged out. Thanks it is a great help. Can we also maintain sessions timeout in Access please, as we do in .net environment. – Kingsize

Answer 2

In C#.Net use

Console.WriteLine("UserName: {0}", Environment.UserName);

Bog

Answer 3

Using VBA in Access it's pretty easy.

Dim User As String
    Let User = Application.UserName
Dim arrUser
    Let arrUser = split(User, ",")
    User = arrUser(1)
    arrUser = split(User, "@")
    User = arrUser(0)

This seems to work for me in my VBA app.

Madelaine