Mendip Data Systems
Database applications for businesses and schools
Securing Your Data - An Encrypted Split No Strings Database
Return to Home Page
This is a significantly updated version of an example application 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.
To demonstrate its use, I am setting a simple security challenge: export the backend data to an external application such as Excel.
The attached example 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.
The application consists of an ACCDE frontend (FE) and an ACCDB backend (BE). For the purposes of this example both FE & BE must be saved in the same folder.
Make sure it is a trusted location.
The ACCDE FE has been locked down with the Access application interface hidden, the navigation pane & ribbon removed and is encrypted with a simple password isladogs. The ACCDB BE file is also encrypted with a different and much stronger password – not supplied or required to use the application
The backend datafile contains 1 ‘deep hidden’ table (though it would work equally well with a standard table)
The frontend database contains several forms and a report. None of the forms or the report have a saved record source. 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).
Instead, disconnected ADO recordsets (AKA in-memory recordsets) are created using code when the object is loaded and destroyed when it is closed.
This approach works perfectly for forms but cannot be used with reports. For that reason, the report data is actually a form with a disconnected ADO recordset used as a subreport.
All the fields in the BE table except the autonumber PersonID PK field have been encrypted using RC4 encryption (the cipher key has not been supplied … nor is it needed to use this)
The application also contains several additional security measures to deter hacking using automation. For example:
a) It cannot be opened from a non-trusted location
b) It cannot be run using automation from an external application
c) Right click context menus have been disabled as have various keyboard shortcuts such as Ctrl-C, Ctrl-V, F12 etc.
Taken together, these measures should prevent anyone being able to export the data to an external application such as Excel (hence the challenge!).
NOTE:
It is IMPOSSIBLE to prevent anyone taking a screenshot of the data. For that reason, the forms/reports should not allow users to view the entire dataset if your data is so sensitive that this approach is used. For that reason, the use of screenshots is not an acceptable solution to the security challenge!
FURTHER INFO:
1 In this example, the entire application interface has been hidden so all forms are shown ‘floating on the desktop’. The report is shown with just the
Access title bar - no ribbon or quick Access toolbar(QAT) However, doing each of these certainly isn’t essential to the idea behind the application,
2. I have deliberately left the encrypted data table so it can be viewed but NOT directly edited. As it contains encrypted data, editing those fields would lead
to partly encrypted data being visible in the form
Colin Riddington Mendip Data Systems 26/12/2020
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)
EncryptNoStrings32_2010 32-bit ACCDE frontend FEX32.accde & BEX.accdb (Access 2010 format)
EncryptNoStrings32_365 32-bit ACCDE frontend FEX32.accde & BEX.accdb (Access 365 format)
EncryptNoStrings64 365 64-bit ACCDE frontend FEX64.accde & BEX.accdb (Access 365 format)
b) Encrypted Split No Strings Database - this article as a PDF file (approx 0.3 MB - zipped)
Difficulty level : Moderate
4. Be aware that creating the editable form will take much longer than usual as unbound controls must be used. The 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
5. If anyone does manage 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 decrypted data using a report (if allowed) or just take screenshots of the data.
6. This approach is only worth considering if your data is highly sensitive.
If you do want to use this approach with highly sensitive data of your own, ensure that:
a) The BE database is given a different password to the FE (see below for password security info)
b) Both the ribbon and navigation pane are removed from the FE. All interaction via forms ONLY
c) End users should NEVER be told the BE password
d) All data is stored in the BE. There should be no data tables in the FE
e) The FE is distributed as an ACCDE so the code is not accessible
f) 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 as that is several orders of magnitude more secure if properly configured
g) 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
You could use different encryption keys for each table or even use a different cipher key for each field if it seems worth the additional coding effort needed
h) OPTIONAL - for additional security, the BE tables can be ‘deep hidden’ as in this DEMO. However, it isn’t essential to do so provided you ensure users have
no means of accessing the BE
7. 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 little point encrypting fields with limited values such as Gender (M/F) or Title (Mr/Mrs/Ms/Miss etc). However, I have done so here for completeness.
8. As mentioned earlier, disconnected ADO recordsets work well for forms but CANNOT be used with reports. There are various alternative solutions including:
Click any image to view a larger version
a) Access databases can NEVER be made 100% secure
b) A capable and determined hacker can break any Access database given sufficient time and motivation.
c) 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.
d) Access apps (or any applications) are only as secure as the weakest part of the security used
3. 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
Method 3 was used in this version of the example application
9. 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
The RC4 encryption code used in this application is as follows. The code should be placed in a standard module e.g. modEncryption
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: abracadabra Enc: cïöTcn‰¯
Effect of altering the original string:
Org: Xgothic19 Key: abracadabra Enc: \çíHbdÛ§°
Org: Xgothic819 Key: abracadabra Enc: \çíHbdÛ®¸w
Further Reading
This article is a companion to the following items at this website:
Encrypted data form
Main form
Report
Report Options
Finally, I will repeat a comment I have written many times previously in relation to security in Access:
I hope this idea will be interesting for others to use / adapt / improve.
I have used both encryption and the 'no strings' approach for particularly sensitive data in both Access & SQL Server 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.
To provide feedback on this application, please contact me by email or use the feedback form at the end of this article.
CREDITS
I would like to thank various Access developers who have suggested ideas and improvements for this application. Particular thanks are due to:
Chris Arnold – for providing valuable assistance with code used with disconnected ADO recordsets
Leo (DBGuy) - for testing several earlier versions of this application
Phillip Stiefel – for informing me of weaknesses in earlier versions which he used (with my permission) in presentations on Access security to Access developers. One of these presentations can be found on You Tube at How (In)Secure is Access Today?- Live at Virtual Access Cascade Conference 2020.
The section starts at around 16 minutes into the presentation and he explains in detail how he was able to circumvent the security in the earlier version
Phillip’s work was largely responsible for me significantly improving the security in this application.
The approaches used by Phillip no longer work in the latest version.
Password Encryption
ACCDB/ACCDE passwords also encrypt the entire file using up to 128-bit encryption. For that reason, these passwords can only be broken by a brute force attack. If the password is reasonably strong this can be a very lengthy process
Chart taken from www.linkedin.com/posts/cbtech-support_ever-wonder-how-long-it-would-theoretically-activity-6715288518240845824-Aiyx
According to https://www.security.org/how-secure-is-my-password the strong BE password used in this ap0plication would take about 41 trillion years to crack using a brute force attack....in fact slighlty less as I've given you some hints to get you started!!
By comparison, the supplied FE password (isladogs) is very weak and would only take a few seconds to crack. In a real-life application, the FE password would need to be strengthened using a longer password containing a mixture of numbers, upper & lower case letters and possibly special characters
'##############################################################
'# RC4 encryption function
'# Author: Andreas J”nsson http://www.freevbcode.com/ShowCode.asp?ID=4398
'# RC4 is a stream cipher designed by Rivest for RSA Security.
'#
'# Amended by Colin Riddington / Chris Arnold 14/03/2019 to fix error 9
'##############################################################
Public Function RC4(ByVal Expression As String, ByVal Password As String) As String
On Error Resume Next
Dim rb(0 To 255) As Integer, x As Long, Y As Long, z As Long, Key() As Byte, ByteArray() As Byte, temp As Byte
If Len(Password) = 0 Then
Exit Function
End If
If Len(Expression) = 0 Then
Exit Function
End If
If Len(Password) > 256 Then
Key() = StrConv(Left$(Password, 256), vbFromUnicode)
Else
Key() = StrConv(Password, vbFromUnicode)
End If
For x = 0 To 255
rb(x) = x
Next x
x = 0
Y = 0
z = 0
For x = 0 To 255
Y = (Y + rb(x) + Key(x Mod Len(Password))) Mod 256
temp = rb(x)
rb(x) = rb(Y)
rb(Y) = temp
Next x
x = 0
Y = 0
z = 0
ByteArray() = StrConv(Expression, vbFromUnicode)
'Next line changed after discussion with Chris Arnold as it causes error 9 - subscript out of range
'arrays start at 0 so this needs to end with Len(Expression)-1
' For x = 0 To Len(Expression)
For x = 0 To Len(Expression) - 1 'Colin Riddington - 14/03/2019
Y = (Y + 1) Mod 256
z = (z + rb(Y)) Mod 256
temp = rb(Y)
rb(Y) = rb(z)
rb(z) = temp
ByteArray(x) = ByteArray(x) Xor (rb((rb(Y) + rb(z)) Mod 256))
Next x
RC4 = StrConv(ByteArray, vbUnicode)
End Function