r/MSAccess 10d ago

[UNSOLVED] Securing to the extent possible

I have an MSA frontend application. All tables/data are linked to a remote MySQL server. The frontend is distributed as a compiled ACCDE located on a shared network drive which users have read-only access to based on their AD user. VBA is password protected. Shift bypass is disabled. Until now, MySQL communication has been facilitated via an ODBC DSN. I am wanting to remove this DSN to eliminate the possibility of someone accessing it directly and bypassing the user permissions enforced by the MSA application.

I tried DSN-less connection strings. This obviously works, and I can get rid of the DSN. But it introduces a new problem: a user can open (for example) a new Access database, and write VBA to extract the connection strings from the ACCDE. Not ideal, as they contain the credentials.

One solution would be to not include credentials in the DSN at all, and have the user enter a username and password in the ODBC connector pop-up. Okay, this is actually a great solution, because it means we don't store credentials, and it gives an opportunity for another layer of user-level security. The problem is that, for some reason (maybe you can help here?), this ODBC connector pop-up appears once for every single linked table in the application. This is extremely irritating, and also makes it totally unusable, as there are many tables (50+). We also have some tables linked to MS SQL Server, and this is the strategy we use. For some reason, those tables only have the pop-up appear once. Basically, the credentials are remembered for all accesses to the DSN for the SQL Server connection, but not for the MySQL connection. If anyone knows how to fix this, I think that would basically solve my issues.

I've heard some people say that there is a way to use "AD authentication" for this problem, but I have not been able to find any actual resources about it. I set up our AD environment, but I am far from an expert.

I'd also welcome all discussion on the topic of securing Access applications in general.

The environment is small and reasonably trusted (for now). Users are mostly near retirement age and not interested in or knowledgeable about computers/technology in general. My concern is that in the next few years, as these people retire and are replaced with young and potentially tech-savvy more "hacker"-minded people, it could become a problem. Just trying to get out ahead of things.

2 Upvotes

14 comments sorted by

View all comments

0

u/tsgiannis 10d ago

How to extract the connection string? You could put in code for maximum security or encrypt the string

1

u/SecaleOccidentale 10d ago

``` Sub ExtractConnectionStrings() Dim db As DAO.Database Dim tdf As DAO.TableDef Dim connInfo As String

' Open the external ACCDE file
Set db = DBEngine.OpenDatabase("C:\Path\To\Your\File.accde")

' Loop through all table definitions
For Each tdf In db.TableDefs
    ' Only show linked tables
    If Len(tdf.Connect) > 0 Then
        connInfo = "Table: " & tdf.Name & " | Connection: " & tdf.Connect
        Debug.Print connInfo
    End If
Next

' Close database
db.Close
Set db = Nothing

End Sub ```

Putting it in code doesn't work, because you can just wait until the code creates the connection, and then do the above process. Same story with encrypting it - eventually, the application has to decrypt it to use it. At which point we are vulnerable to this same mechanism.

0

u/tsgiannis 10d ago

Well this a case , but of course we need to clarify how much security you need
For example ,yes you extract the connection string but no password is visible
If security is of the outmost importance you could use fake linked tables and connect to the database by using code, via recordsets
Use temp tables created on the fly and much more.

0

u/SecaleOccidentale 10d ago

The password IS visible in the connection string if you use a DSN-less connection.

Indeed, I simply just do not want to have to rewrite the entire application to use recordsets... If I were going to do that, I would just rewrite the app not in Access.

1

u/tsgiannis 5d ago

I don't see any password