Securing Your Data - An Encrypted Split No Strings Database



Version 5.46            Last Updated 22 July 2021                     Difficulty level :   Advanced

Section Links:            How it works     Further Info     Encryption     RC4 Examples     Downloads     Acknowledgements     Passwords     Further Reading


How the application works                                                                                                                                                       Return To Top

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 applicationsuch 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) with about 2000 records.
For the purposes of this example both FE and 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 and 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 nor 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 PersonIDprimary key field have been encrypted using RC4 encryption (the cipher key(s) have not been supplied ... nor are they 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. Any attempts to do so will cause the application to close after a warning message.
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 considered an acceptable solution to the security challenge!



Further Information                                                                                                                                                       Return To Top

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.

ENSTable
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.

ENSForm
ENSReport
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 and very strong 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 can use different encryption keys for each table and/or 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:

ReportOptions
      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 such as AES exist where the encryption is 'one-way', but coding will therefore be more complex still.



RC4 Encryption Code                                                                                                                                                       Return To Top

     The RC4 encryption code used in this application is as follows. It should be placed in a standard module e.g. modRC4Encryption




'##############################################################
'# 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



RC4 Encryption examples                                                                                                                                                       Return To Top

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      

Finally, I will repeat some comments I have written many times previously in relation to security in Access:
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

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 and 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.



Downloads                                                                                                                                                                       Return To Top

a)     The example application used in this article (Approx 1.2 MB - zipped)

          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)



Acknowledgements                                                                                                                                                       Return To Top

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 (theDBGuy) - for testing several earlier versions of this application

Philipp 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 YouTube at How (In)Secure is Access Today?- Live at Virtual Access Cascade Conference 2020.
The section starts at around 16 minutes into the presentation. Philipp explains in detail how he was able to use automation to circumvent the security in the earlier version.

The tests done by Philipp provided the incentive for me to further improve the security in this application!
Since then many further changes have been made. As a result, the methods used by Philipp no longer work in the latest version.

Depending on how users attempt to gain access from an external program, one of the following messages will be seen and the application will then close automatically.

Automation
RunningPrograms


Password Encryption                                                                                                                                                       Return To Top

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.

PasswordBruteForceCrackingTimes
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 application 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 and lower case letters and possibly special characters.



Further Reading                                                                                                                                                       Return To Top

This article is a companion to the following items at this website:

Access File Security

Improve Security

Purpose of System Tables

Security Challenges 


Colin Riddington              Last updated:    21 Jan 2022

Return to Top Return to Access Articles Return to Home Page

Click any image to view a larger version

RC4 encryption code

Encrypted data form

Main form

Report

Report Options

Automation warning message

External application message

Password hacking times