This article was written in response to numerous forum questions about methods of preventing data being stolen from Access databases.
For example, this thread at Access World Forums: Prevent Importing ODBC tables from ACCDE
It is intended to show how the data in an Access database can be made reasonably secure against hackers whilst still allowing full functionality to authorised users.
The attached DEMO application should behave exactly as any split database …
… BUT there are no linked tables and therefore no connection strings visible in the navigation pane or MSysObjects system table etc.
In addition, the data is protected using a RC4 encryption cipher.
There are THREE versions of the DEMO app - ACCDB ; 32-bit ACCDE ; 64-bit ACCDE
Each zip file contains a split database with 2 files. Save both files in the same folder
a) 32-bit ACCDE frontend FEX32.accde & BEX.accdb
b) 64-bit ACCDE frontend FEX64.accde & BEX.accdb
c) FE.accdb & BE.accdb
Both ACCDE versions have been locked down with the navigation pane & ribbon removed
All BE files and the ACCDE FE files are encrypted with password 'isladogs'
The ACCDB FE file is NOT password protected so you can view the code if you are interested in knowing how this works.
However, I recommend you try out the appropriate ACCDE version for your version of Access first.
The backend (BE) datafile contains 1 ‘deep hidden’ table, tblBEData (though it would work equally well with a standard table)
The frontend (FE) contains 2 forms and a report
Both of the forms and the report have no saved record source.
Instead the record source is created using code when the object is loaded and destroyed when it is closed
As the forms/reports have no record source, there is no link to the BE table.
Therefore, there is no connection string accessible to that table (other than in the code)
The BE table contains 8 fields of which all except the autonumber PersonID PK field have been encrypted
Colin Riddington Mendip Data Systems 14/03/2019
To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks
Click to download :
a) The example application used in this article (Approx 1.2 MB - zipped)
EncryptNoStringsACCDE32 32-bit ACCDE frontend FEX32.accde & BEX.accdb
EncryptNoStringsACCDE64 64-bit ACCDE frontend FEX64.accde & BEX.accdb
EncryptNoStringsACCDB ACCDB frontend FE.accdb & BE.accdb
b) Encrypted Split No Strings Database - this article as a PDF file (approx 0.3 MB - zipped)
Difficulty level : Moderate
Me.RecordSource = "SELECT tblBEData.PersonID, RC4([Title],'" & RMP_RC4_Key & "') AS XTitle," & _
" RC4([LastName],'" & RMP_RC4_Key & "') AS XLastName, RC4([FirstName],'" & RMP_RC4_Key & "') AS XFirstName," & _
" RC4([Gender],'" & RMP_RC4_Key & "') AS XGender, RC4([DOB],'" & RMP_RC4_Key & "') AS XDOB, " & _
" RC4([Company],'" & RMP_RC4_Key & "') AS XCompany, RC4([EMail],'" & RMP_RC4_Key & "') AS XEMail" & _
" FROM tblBEData IN '' [MS Access;PWD=isladogs;DATABASE=" & CurrentProject.Path & "\BE.accdb];"
1. In the unlocked BE version of this DEMO, I have deliberately left the table so it can be viewed and therefore also be EDITED (NOT recommended!)
As it contains encrypted data, editing those fields will lead to partly encrypted data being visible in the form
2. This approach is only worth considering if your data is highly sensitive
Be aware that creating the forms will take much longer than usual as unbound controls must be used.
The editable form contains 2 sets of each control in order to allow editing of the encrypted data.
Unbound controls are used to display the decrypted data
Doing this also means code needs to be added to each unbound control to encrypt the entered data
3. If anyone manages to directly access the data tables, all they will see is encrypted data
However, it is of course still possible for anyone with authorised access to the FE to print the data using reports or just take screenshots of the data.
4. If you decide to use this approach with highly sensitive data of your own, I would STRONGLY RECOMMEND that:
• The BE database is given a different password to the FE.
End users do NOT need to know the BE password
• All data is stored in the BE. There should be no data tables in the FE
• The FE is distributed as an ACCDE file so the code is not accessible
• The Access BE file is stored securely on the server to which end users have no access
Much better still - use SQL Server or similar for the BE database
• Both the ribbon and navigation pane are removed from the FE. All interaction via forms ONLY
• A strong 128-bit encryption method is used such as RC4 (or any other secure cipher)
XOR encoding is NOT recommended as it is too easy to decode
• Different encryption keys are used for each table.
You could even use a different key for each field if it seems worth the additional coding effort needed
• OPTIONAL - for additional security, the BE tables can be ‘deep hidden’ as in the locked versions of this DEMO.
However, it isn’t essential to do so if you ensure users have no means of accessing the BE
5. See the next section for examples of the code used in this DEMO
6. If you do find any security issues in the ACCDE versions, please let me know.
I do have several additional security measures that I use to deter unauthorised hacking . . . but I can’t give away all my secrets!
7. I decided not to encode the encryption key & BE password for this DEMO as it would just add confusion.
In any case, as the code will be used in an encrypted ACCDE file, it is impossible for end users to retrieve this information.
That is unless a specialist company is employed to reverse engineer the encryption of the ACCDE file itself.
Reputable firms will only do that provided proof of ownership can be established which will not normally be possible.
8. Finally let me repeat a comment I have written many times previously in relation to security in Access:
Click any image to view a larger version
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.
Access apps (or any applications) are only as secure as the weakest part of the security used
You need to be aware that encrypting date or number fields is problematic as the encryption cipher converts data into ‘random’ text strings.
Hence, I have used a text field for the date of birth (DOB) field above
There is clearly little point encrypting fields with limited values e.g. Gender (M/F) or Title (Mr/Mrs/Ms/Miss etc)
However, I have done so here for completeness.
Although the table contains encrypted data, it is displayed unencrypted in the main form and the report
The main form is fully editable and new records can be added – any changes will automatically be encrypted
I hope this idea will be interesting for others to use / adapt / improve.
I've used both encryption and the 'no strings' approach for particularly sensitive data but have never felt it necessary to do so for a whole database.
I'll leave others to decide how practical this would be for an entire application!
I would be grateful for any feedback on this article including details of any errors or omissions
If you have any questions about this approach, please send me an email or use the feedback form below
It is intended that a follow up article will explore the use of disconnected ADO recordsets as an alternative approach
When the form frmBEData or the report are opened, the record source is set using:
FROM tblBEData IN '' [MS Access;PWD=isladogs;DATABASE=" & CurrentProject.Path & "\BE.accdb];"
For example, the code RC4([LastName],'" & RMP_RC4_Key & "') AS XLastName is DECRYPTING the LastName field and it is referenced here
as XLastName to help clarify the data source.
Similarly, for each of the other encrypted fields
The encryption key constant is saved in the header section of the modEncryption module.
For additional security it could itself be encrypted . . . but using a different method!
The connection string to the BE table is the line:
Once again, the password itself could be encrypted … again using a different method.
When a field is updated in an unbound control, code like this is used to update the encrypted field in the BE table:
CurrentDb.Execute "UPDATE tblBEData IN '' [MS Access;PWD=isladogs;DATABASE=" & CurrentProject.Path & "\BE.accdb] " & _
" SET tblBEData.FirstName = RC4('" & strText & "','" & RMP_RC4_Key & "')" & _
" WHERE (((tblBEData.PersonID)= " & [Forms]![frmBEData].[PersonID] & "));"
Encryption and decryption are done using the same RC4 cipher and key. In other words, the encryption is reversible.
This feature makes coding much easier to manage but it is also a weakness of RC4.
However, unless the encryption key is known, it is almost impossible to decrypt the data
Other ciphers exist where the encryption is ‘one-way’, but coding will therefore be more complex still
When a new record is added, the following code is used:
'get encrypted data for saving to table
strLN = "RC4('" & Forms!frmBEData.txtLastName & "','" & RMP_RC4_Key & "')"
strFN = "RC4('" & Forms!frmBEData.txtFirstName & "','" & RMP_RC4_Key & "')"
strC = "RC4('" & Forms!frmBEData.txtCompany & "','" & RMP_RC4_Key & "')"
strE = "RC4('" & Forms!frmBEData.txtEMail & "','" & RMP_RC4_Key & "')"
strDB = "RC4('" & Forms!frmBEData.txtDOB & "','" & RMP_RC4_Key & "')"
strG = "RC4('" & Forms!frmBEData.cboGender & "','" & RMP_RC4_Key & "')"
strT = "RC4('" & Forms!frmBEData.txtTitle & "','" & RMP_RC4_Key & "')"
'append new record
CurrentDb.Execute "INSERT INTO tblBEData ( Title, LastName, FirstName, Gender, DOB, Company, EMail ) " & _
" IN '' [MS Access;PWD=isladogs;DATABASE=" & CurrentProject.Path & "\BE.accdb]" & _
" SELECT " & strT & " AS Title, " & strLN & " AS LastName," & _
" " & strFN & " AS FirstName, " & strG & " AS Gender," & _
" " & strDB & " AS DOB, " & strC & " AS Company, " & strE & " AS EMail;"
When the form/report is closed the record source is destroyed using Me.RecordSource = ""
RC4 Encryption examples
Using the same string (gothic19) with 5 different keys:
Org: gothic19 Key: abc Enc: ªòA1¼ÜJ
Org: gothic19 Key: conundrum Enc: oÞÈN\jBú
Org: gothic19 Key: a53frt23 Enc: í9 ¿tRõ
Org: gothic19 Key: nempnett67thrubwell Enc: ˆ´¡¦!“
Org: gothic19 Key: rmp4_key Enc: Ì 8+ž~
Org: gothic19 Key: abracadabra Enc: cïöTcn‰¯
Effect of altering the original string:
Org: Xgothic19 Key: abracadabra Enc: \çíHbdÛ§°
Org: Xgothic819 Key: abracadabra Enc: \çíHbdÛ®¸w
This article is a companion to the following items at this website: