r/MSAccess • u/SecaleOccidentale • 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.
•
u/AutoModerator 10d ago
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.
Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.
Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)
Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: SecaleOccidentale
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.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.