First Published 20 Mar 2019 Last Updated 12 Mar 2022 Difficulty Level : Advanced
Server Based DB
This article is a companion to the following articles on this website:
Access File Security
Purpose of System Tables
As I have already written elsewhere:
Access databases can NEVER be made 100% secure
A capable and determined hacker can break any Access database given sufficient time and motivation.
However, by erecting various barriers, it is certainly possible to make the process so difficult and time consuming that it isn't normally worth attempting.
This article summarises some of the main steps that developers should take to improve security – it is not necessarily complete
Make sure you make several backups as you do each step as its only too easy to lock yourself out!
The second part of this article explains how to do each step with an example application containing the code needed
The third part of this article discusses a wish list of changes I would like to see added to ACCDE databases to further improve security.
Remember that Access apps (or any applications) are only as secure as the weakest part of the security used
1. Split your databases Return To Top
In a multiuser environment, Access applications should ALWAYS be split with:
a) Shared backend file(s) on the server and only containing tables
Users should connect to the backend files on a LOCAL area network (LAN)
- NOT on a WIDE area network (WAN) as it is likely to be slow and cause connection / corruption issues
NEVER use a cloud based location such as OneDrive or Dropbox as this significantly increases the risk of corruption.
Similarly users should NEVER run a split database when connected to the server using a WIRELESS connection
b) All forms, reports, macros and module code in a frontend database
Each user needs to have their own copy of the frontend database located on their own workstation
It is ESSENTIAL that users do NOT share the same copy of the frontend as corruption WILL occur
2. Backend files (BE) Return To Top
a) Hide all tables
b) Hide the navigation pane
c) Disable various Access options e.g. UNTICK all the following:
- allow full menus
- allow default shortcut menus
- use Access special keys (this disables the SHIFT bypass – but see below)
d) Do ONE of the following:
- Hide the ribbon
- Remove Privacy Options from the File menu (otherwise users can undo all the above)
e) Encrypt the BE, using a strong password. Do NOT store this password anywhere in the application (FE or BE)
There is no benefit in using an ACCDE file for the backend database if your backend database only contains tables.
3. Frontend file (FE) Return To Top
a) Do everything listed above to the frontend as well – no ribbon / no nav pane / shift bypass disabled etc
b) ALWAYS use an ACCDE so your code is compiled and therefore not available to end users
c) Rename as ACCDR and add code to prevent it being used if the file type is changed back again
d) Use strong encryption with a different password to that used in the backend file(s)
e) A VBA project password can also be used but it is easy to disable these. DO NOT rely on a VBA password alone!
f) It is strongly recommended that users login with user name & password:
- if possible do NOT store the passwords in the database - instead use Active Directory
- If you MUST store user passwords in the database itself, make sure you use strong 128-bit encryption such as RC4 (see below)
- Do NOT just encode passwords as doing that is usually very easy to decode and DO NOT:
- store the encryption cipher anywhere it can be viewed
- include decryption code in the database
- if you must store the encryption cipher in the DB, encrypt that as well (using a different method)
4. Other Security Options Return To Top
Other possible measures include:
- Add registry strings that are checked by the FE & if the settings are altered, prevent the FE running
- Using activation type code to 'lock' the application to a particular workstation and prevent copying
- Prevent users having direct access to the folders containing BE files
- Consider removing the taskbar whilst the application is in use but, if so, ensure that is reversed afterwards
It is possible for knowledgeable users to do ALL the following (and more) from an external application such as Notepad/Excel or another Access database
- re-enable the Shift Bypass
- view the connection strings to the BE tables (which exposes the BE password)
- change the start up form e.g. to bypass the user login form
- bypass everything on first opening if the location isn’t trusted
I am deliberately NOT going to explain how to do any of these things here
5. RC4 Encryption of Passwords Return To Top
The RC4 encryption code is freely available online and provides very strong 128-bit encryption - good enough for use in Access
However, RC4 encryption is reversible and is no longer considered secure enough for commercial databases storing information such as credit card data etc
For further details, see https://en.wikipedia.org/wiki/RC4
6. File versus server-based databases Return To Top
Access is a file-based application which is fundamentally why it can never be 100% secure
If you have mission critical data, an Access BE is NOT the right solution.
Using a server-based BE such as SQL Server will significantly improve the security of your data.
However, it does require someone with a good knowledge of SQL Server to maintain it.
7. Downloads & Further Reading Return To Top
Click this link for a detailed explanation of the various steps involved: Securing Your Database
The link includes an example application you can use for testing purposes
Click this link to download this article as a PDF file: Improve Access File Security
8. Security Challenges Return To Top
All the above security features (and more) are included in my various security challenges
Their purpose is partly fun but are also designed to encourage users to improve their own security by looking into weaknesses in other apps.
Each challenge is meant to be solvable so there are deliberate built-in weaknesses (as well as a couple of unintentional flaws included by mistake!)
9. Feedback Return To Top
I would be grateful for any feedback on the article including details of any errors or omissions.
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 12 Mar 2022
Return to Access Articles Page Page 1 of 3 1 2 3 Return to Top