Last Updated 20 Jan 2022 Difficulty Level : Advanced
In the first part of this article I showed that:
Adding a password to the MDB/MDE frontend does not encrypt the linked table name or the password needed to open the BE file.
The contents of the BE file remain completely insecure UNLESS whole file JET encryption is added separately (Access 97 or later)
For comparison, I converted all the test files to ACCDB format but otherwise identical to those above
1. A backend file BE.accdb with one table Table1. The file was encrypted with password dinsdale.
2. A frontend file FE.accdb. I then linked this to Table1 in the BE file
3. The frontend was copied, encrypted with the password MDS and saved as FE - PWD.accdb
In ACCDB files, much stronger 128-bit encryption is used which encrypts the entire file
Unlike MDB files, password protecting an ACCDB file ALSO encrypts the file
For that reason, zipping an encrypted ACCDB/ACCDE file will have little effect on file size
By comparison zipping a password protected MDB/MDE file does significantly reduce file size.
Some reduction occurs EVEN if the MDB/MDE file is also encoded/encrypted
Both versions of the frontend were then saved as ACCDE files: FE.accde and FE - PWD.accde
The same tests were run as for the MDB / MDE files
ACCDB FE file
ACCDE FE file
Neither ACCDB nor ACCDE files offer any additional security in terms of the BE file.
As a result, the contents of the BE file remain insecure
For comparison, here are the results using password protected (encrypted) ACCDB / ACCDE files.
Password protected ACCDB FE file
Password protected ACCDE FE file
The password protected ACCDB & ACCDE files encrypt the entire file including BOTH the linked table name and password information
Therefore, to keep the data in a BE file secure, the FE file needs to be in ACCDB/ACCDE format and encrypted using password protection.
Linking to Other Data Sources
ACCDB/ACCDE files can also link to other data sources including MDB & SQL Server tables.
Providing the FE is encrypted, the details of these link tables are also secure.
MDB files cannot link to ACCDB files. However, they can of course be linked to SQL tables including those created in later versions of SQL Server providing the datatypes can be recognised by the MDB file.
SQL Server is a much more secure environment so does that mean linked SQL Server tables are also secure in an MDB file? Sadly, the answer is, not really!
I added links to several SQL Server tables in a password protected MDE file.
As with any other linked tables in MDB/MDE or ACCDB/ACCDE files, the connection strings are visible though the password is NOT shown.
However, using a hex editor, the full SQL connection string including the password is shown for each table
The SQL table names are also easily found. For example:
The SQL field names are also listed. For example:
This is the same linked table viewed in Access:
After all these tests,it is somewhat of a relief to state that I was unable to find the linked SQL table data using the hex editor.
The SQL data DOES appear to be encrypted.
However, as the full SQL connection string and table names have been exposed, it may not be that difficult for users to gain access to the SQL datafile itself.
Password Recovery Utilities
Password recovery utilities can be useful if you forget the password to your own applications.
However, these can just as easily be used for other applications that are not your own work.
Running a quick online check will soon find several utilities (mostly free) that will crack passwords on MDB files.
This is usually very quick as the level of encryption is relatively weak
By comparison, I am only aware of one commercial utility that can crack ACCDB passwords.
This is done by ‘brute force’ trying every possible combination of characters until one is successful.
If a strong password is used, the process can take MANY hours or even DAYS and is therefore unlikely to be worth the effort in most cases.
Obviously, there are many other factors involved in making databases as secure as possible including:
a) The use of workgroup security (AKA user level security) for MDB/MDE files
b) Protecting the location of the BE file by saving on a secure network and limiting user permissions to the folder
c) Using a SQL server backend database
d) Using strong passwords that cannot easily be hacked
However, to repeat, an Access database can NEVER be made 100% secure.
A capable and determined hacker can break any Access database given sufficient time and determination
Click to download this article as a PDF file
I would welcome feedback on the information provided in this article
Colin Riddington Mendip Data Systems Last Updated : 20 Jan 2022