Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

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

Return to Top
* Required

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

Return to Access Articles

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

ReportOptions ENSTable ENSForm ENSReport

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

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