Is it safe to use Environ("Username") as authentication method in MS Access / Active Directory?

Question

I'm developping an application within our small company environment. This is our setup in a nutshell:

• Employees log into their PCs via their Active Directory login
• The application I'm developing uses MS Access as the front end and MS SQL Server 2019 as the back end (Windows authentication mode)

I want to be able to authenticate users in the application without the need to entering their AD login, just based on the fact that they are logged into their PC. Then I want to be able to grant users permissions in the application as I see fit.

This is what I came up with:

tblEmployees:

EmployeeID	EmployeeName	ADLogin
1	John Doe	COMPANY\john.doe
2	Peter Wilson	COMPANY\peter.wilson

When any user starts the MS Access application, it will do something like this on start-up:

Private Sub Form_Load()

    Dim rsUser As Recordset
    Dim intEmployeeID As Integer
    Dim strEmployeeName As String
    
    Set rsUser = CurrentDb.OpenRecordset("SELECT * FROM tblEmployees", dbOpenSnapshot, dbReadOnly)
    rsUser.FindFirst "ADLogin='" & Environ("USERDOMAIN") & "\" & Environ("USERNAME") & "'"
    
    If rsUser.NoMatch Then
    
        ' User does not have access to the application
        rsUser.Close
        Set rsUser = Nothing
        
        Application.Quit
        
    Else
    
        ' User with this AD Login has been found and that means that he does have access
        intEmployeeID = rsUser("EmployeeID")
        strEmployeeName = rsUser("EmployeeName")
        
        TempVars("EmployeeID") = intEmployeeID
        TempVars("EmployeeName") = strEmployeeName
        
        
        DoCmd.OpenForm "frm_MainMenu"
        Forms!frm_MainMenu.Requery
        DoCmd.Close acForm, Me.Name, acSaveYes
        
        Forms!frm_MainMenu!txtLoggedUser = TempVars("EmployeeName")
        
    End If

    rsUser.Close
    Set rsUser = Nothing
    
End Sub

Then I will use TempVars("EmployeeID") throughout the application to make forms and buttons available and so on.

My question is: Is this a good practice to do? Is it secure? Is there perhaps a better way to do this? Thanks for any tips.

Answer 1

First of all let me start by saying Access is not secure, but it depends on your colleagues knowledge and how far are they willing to go.

In terms of your solution, I believe there's no need to load the entire table to the recordset and then try to find the desired record when you can filter it directly at source. In addition, string concatenation is (most of the times) bad practice, instead create a query (since its usage will be frequent) and pass the inputs as parameters.

See an example:

The query (your fields might be different but you get the idea)

PARAMETERS [Domain] Text (50), [Username] Text (50);
SELECT *
FROM T
WHERE T.Domain=[Domain] AND T.Username=[Username];

---

You can also create a temporary query from code.

Const SQL As String = "The SQL command above"

'No query name means it's temporary and will be deleted once the method runs.
Dim q As DAO.QueryDef
Set q = CurrentDb().CreateQueryDef("", SQL) 

---

To call it and check the logged user:

Dim q As DAO.QueryDef
Dim r As DAO.Recordset

Set q = CurrentDb().QueryDefs("YourQueryName")

'Using the Environ() method.
q.Parameters("[Domain]").Value = Environ("USERDOMAIN")
q.Parameters("[Username]").Value = Environ("USERNAME")

'OR alternative method to get the username/domain from @Andre in comments
'This is more secure than the Environ() method.
With CreateObject("WScript.Network")
    q.Parameters("[Domain]").Value = .UserDomain
    q.Parameters("[Username]").Value = .UserName
End With

'read-only, no need for changes.    
Set r = q.OpenRecordset(dbOpenSnapshot)

'Not found
If r.EOF Then
    DoCmd.Quit acQuitPrompt
    Exit Sub
End If

'Found
'The recordset now contains whatever fields the query selected.
'Do what needs to be done.
'...

'Clean up
If Not r Is Nothing Then r.Close
If Not q Is Nothing Then q.Close

---

Lastly, I would change the intEmployeeID variable to be of type Long and add error handling to the method.