Next Tip Secure Microsoft Access Passwords and Encryption in Access 2007 

An article by Garry Robinson with research from Wayne Phillips

Hello Microsoft Access fans and sceptics. 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.

 Open your database exclusively
Figure 1 – Open your database exclusively

Choose the Database Tools tab and choose Encrypt with Password as shown in Figure 2.

 Password Encryption on the 2007 ribbon
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

HKEY_CURRENT_USER/Software/Microsoft/Office/12.0/Common

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.

 creating a key in the registry
Figure 3 ~ creating a key in the registry

Create Multi-String Value called DefaultEncryption (if doesn't exist) as shown in figure 4:

 create a multi-value key in the registry
Figure 4 ~ create a multi-value key in the registry

Rename the value New Value #1 as shown in figure 5 to

DefaultEncryption

 renaming the field value
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
RC4
128

 adding the encryption setting values
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.

 how to look at the database in Notepad
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.

 The encryption used is visible in an editor
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.

 Microsoft Word will show you encryption algorithms on your computer
Figure 9 ~ Microsoft Word will show you encryption algorithms on your computer

Cleanup

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.

Performance

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.

Download Samples

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.

Recommendations

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.


Your Sample Database Is Called   "encrypt_downloads.zip"

Sample database is suited to Access 2007 +

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

 

 

Other Related Articles That You Might Want to Read at Our Site

Samples of Good Access Programming Practises

Microsoft Access Security And Passwords

Access Traps for the Naïve Developer

External Links

To find out more detail about encryption and Access passwords, I highly recommend that you head to this article by Wayne Phillips.
http://www.everythingaccess.com/encrypt

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.
http://office.microsoft.com/en-gb/help/HA011403111033.aspx

For a description of RC4
http://en.wikipedia.org/wiki/RC4

For a description of SHA password encryption
http://en.wikipedia.org/wiki/SHA-1

For an overall description of the security measures for Access 2007, read Garry's article for Microsoft
http://msdn.microsoft.com/en-us/library/bb421308.aspx

 

Our Tools and Resources

  • RSS & Newsletter Here
    Join our newsfeed or sign up for our informative newsletter on Office Automation, Access and VB topics
  • Get Good Help
    If you need help with a database, our Australian Professionals could be the answer

  • Smart Access is online 
    The best magazine written about Microsoft Access is now being transferred to the web. There are 400 articles written by a 100 authors in the collection. 

    Purchase Smart Access

  • The Workbench
    Find out who has your database open, start the correct version of Access, easy compacting and backups, change startup options, creation versions,  shutdown database

  • Convert Access to SQL Server  
    Upsize to SQL Server 2005 or 2008, easily repeated conversions, highly accurate SQL query  translation and web form conversion.
  • Purchase the Popular FMS Products  
    If you purchase the Popular FMS products from us, you will receive a complimentary of Smart Access Gold, Silver or Bronze Collections [Your choice]

 

 

vb123 Professionals


Get Good Help Here

If you need help with a database or Office programming, our Professionals could be the answer because we have worked on many similar solutions



Garry's Blog
Find out a few other things that Garry has been writing about Microsoft Access.


About The Editor ~ Contact Us
Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia

Access 2003 Security

MS Access Security

Read More here

Other Related Articles That You Might Want to Read at Our Site

Consolidation Queries

How To Create A Crosstab Query

Implementing a Successful Multi-user Access/JET Application