An article by Garry Robinson with research from Wayne Phillips
Hello Microsoft Access fans and skeptics. In this article, I am going to describe how you can improve on what already is a very significant security improvement in Access 2007, database encryption. The technical research for this article was provided by Wayne Phillips from everythingaccess.com.
Microsoft Access 2007 introduced a new file format for storing Access database information. This file format can be identified by any file that ends with .ACCDB. Whilst Microsoft Access 2007 still supports the file type of .MDB, the future of Access is going to be built around the .ACCDB format.
One of the most significant changes with the .ACCDB format is a new method of encryption that is based around the database password. This change is a very significant security improvement because encrypted ACCDB databases are (in theory) strong enough that they can only be cracked using very computer intensive brute-force password recovery. Of course, as with all security, if lots of people do things the same way the risk is far higher that someone will work out how to breach the security. This definitely will apply to encrypting an Access 2007 database because the default encryption algorithm used is RC4 with a 40-bit key, one that is not as strong as it could be. In this article I am going to show you how to make your password encrypted database more secure than the standard ACCDB encryption.
What is this encryption thing and why is it important
Here I would like to surmise from a detailed article on this topic by Wayne Phillips from the everythingaccess.com website. Wayne writes “Under the hood, Access 2007 reads and writes to the .ACCDB file in chunks also known as 'pages'. These pages are of a fixed size: 4096 bytes long. Encryption occurs at page-level and in order to keep things simple (and for performance reasons) the encrypted pages must also be 4096 bytes long. In the ACCDB, every encrypted 'page' has a unique key which is derived from a password hash (which includes random base data). This is very significant because now the database password is no longer stored in the file (unlike earlier versions). This approach means only brute-force password recovery is possible.
Another thing that Wayne points out is The default encryption uses only a 40-bit key, which does let it down a little, but fortunately this can be bumped up to 128-bit encryption. Further on in this article, I will show you how to do that.
Before you start encrypting
If you are like me, when you read an article about databases, you will be tempted to rush to try it out on the database you are using. In this case, don't do this; use the databases that come with this article and experiment in a folder that doesn't have any other database. When you're ready to add a password to your own database, make sure you have a system for remembering passwords e.g. Printed and stored in a safe deposit box.
Encrypting an Access 2007 Database with the standard 40 bit key
To encrypt a database, getting started is a similar process to that which you had to undertake for Access 2003 encryption. First you have to open Microsoft Access without selecting a database and then you browse to the database using the Open More Files option (see RHS of figure 1). After you choose the database, click the down arrow next to the Open button (see bottom of figure 1) and choose Open Exclusive.
Figure 1 – Open your database exclusively
Choose the Database Tools tab and choose Encrypt with Password as shown in Figure 2.
Figure 2–Password Encryption on the 2007 ribbon
Enter a strong password (letters and numbers and special characters) and verify. Please write down your password somewhere at this stage.
Changing a password
Every now and again it is a good idea to change the database password. To do this, open the database exclusively. Now you will find the Decrypt Database button has replaced the Encrypt With Password button on the Database Tools tab in the Access ribbon. Use that button to remove the password.
Encrypting an Access 2007 Database with a 128 bit key
As mentioned before, Access now supports different RC4 encryption algorithm providers (also called cryptographic providers). Now let's see how you can encrypt your ACCDB database with a longer key length, giving stronger encryption. Before you do this, it is recommended that you have a recent full system back up because you will now be shown how to change the Windows registry.
To apply a stronger encryption to an ACCDB database, you need to make changes to your registry. To do this, click on the Windows start button and choose Run and enter RegEdit into the program dialog box.
Navigate through the hierarchy in the registry to the following key
Note: the 12.0 in this registry key means Office 2007
Create a new key called Security (as shown in figure 3) if it doesn't already exist.
Figure 3 ~ creating a key in the registry
Create Multi-String Value called DefaultEncryption (if doesn't exist) as shown in figure 4:
Figure 4 ~ create a multi-value key in the registry
Rename the value New Value #1 as shown in figure 5 to
Figure 5 ~ renaming the field value
Paste the following (on 3 separate lines in the one box) as shown in figure 4:
Microsoft Enhanced Cryptographic Provider v1.0
Figure 6 ~ adding the encryption setting values
Note: The format for the multi-value string is:
Value 1. Encryption provider name
Value 2. Encryption Algorithm
Value 3. Encryption Key Length
That completes your registry setup and the next time you encrypt a 2007 database, the (stronger) encryption algorithm that you specified in the registry will be used.
Note: If you are worried that this is all too complex; remember only the person who applies a password has to make this registry key change. For everyone else, Access will sort out the encryption method to use from information inside the database.
Checking what encryption key has been used
If you navigate to the database in Windows Explorer, right click on the filename and choose to Open With Notepad or a hex editor of your choice as shown in figure 7.
Figure 7 ~ how to look at the database in Notepad (in Windows XP Explorer)
Once you have the database open in your editor as shown in figure 8, you can find the encryption algorithm written near the top of the file. If no encryption has been used this area will be blank.
Figure 8 ~ The encryption used is visible in an editor
Warning: whatever you do please don't change and save the database file when using these types of editors or you may corrupt it.
Finally you may even be tempted to try other RC4 encryption providers. One way to find out what other RC4 encryptors are on your machine is to save a Word file in encrypted format as shown in Figure 8.
Figure 9 ~ Microsoft Word will show you encryption algorithms on your computer
If you don't like the concept of 128 bit encrypted databases and want to return to using the standard 40 bit encryption, rename or delete any registry entries that you have added to the registry whilst following this article. After that, remove the password and add the password again.
One topic that I have neglected in this article is performance. If you're thinking of encrypting a database that had already runs slow, please test performance before implementing encryption and after implementing encryption. You may notice different performance for different encryption algorithms.
Compressed files are not compressed
If the database has been encrypted, compressing using a tool such as WinZip will not compress the file at all.
To Find Out More
To find out more detail about encryption and Access passwords, I highly recommend that you head to this article by Wayne Phillips.
Here is what Microsoft has to say on this topic in an article that is aimed towards encryption of the more popular Office file types.
For a description of RC4
For a description of SHA password encryption
For an overall description of the security measures for Access 2007, read Garry's article for Microsoft
The sample download that I have set up includes a database that has NOT been encrypted, one that has been encrypted with RC4 with 40 bits key length and one that has been encrypted to RC4 with 128 bit key length. You will need Access 2007 to make these databases work and the password I have used for these samples is vb123.com
I suggest that you open the 128 bit encrypted file on all target computers to make sure that the encryption algorithm exists on those computers. If it doesn't, those computers won't be allowed to open the database.
Use strong passwords to prevent brute-force attack
Use the RC4-128 bit encryption algorithm if security is critical
Test that RC4-128 encrypted databases can be opened by your key users
About my researcher
Wayne Philips is a very smart fellow that has provided an Access database recovery service at http://www.everythingaccess.com for a number of years and truly understands the structure of an Access Database. I found this out because Wayne has solved problems for me that would have stumped most of the best Microsoft Access specialists that I have met.
» See All Articles by Columnist Garry Robinson