Encryption vs. Encoding

Published 19 May 08 01:01 PM

Every now and again we get the question of "what is the difference between encryption and encoding?" This came up again today so we wanted to try to answer it more broadly.

In short, the feature that we call "encryption" is a feature that was introduced for ACCDB files in Access 2007. This feature was an improvement over the feature known as "encoding" in Access 2003. "Encoding" uses a proprietary algorithm for scrambling the data in a database and was first introduced in Access 2.0. "Encryption", used in Access 2007, uses improved Windows APIs and is stronger than previous versions.

In addition to the algorithm being used, here's a summary of the functional differences:

  Encryption Encoding
File format ACCDB, ACCDE, ACCDA MDB, MDE, MDA
Requires database password? Yes No
Creates a new file? No Yes
Ribbon entry point "Encrypt with Password" "Encode/Decode Database"
Requires the database to be opened exclusively? Yes (because a database password is required) No

You'll notice that a database password is required when using "encryption" in a database in the ACCDB file format and not in the MDB file format. As a result, the behavior of these buttons is different in the Ribbon. When you click "Set Database Password" in the Ribbon in an MDB file, a database password is opened, but the file is not encoded. We've changed the text of the command in an ACCDB file to read "Encrypt with Password" to demonstrate that these are in fact a single operation.

The feature called "encoding" in Access 2003 and Access 2007 was previously called "encryption" in Access 2002 and earlier. We realize this is somewhat confusing, however, the term "encoding" more accurately describes the algorithm used for the feature so we changed it in Access 2003. Databases encoded using Access 2003 or earlier should not be considered secure.

If you're working with both the MDB and ACCDB file formats in Access 2007, there are a few things to keep in mind:

  • An encoded MDB database cannot be converted to the ACCDB file format
  • An encrypted ACCDB cannot be converted to the MDB file format
  • Because encrypted databases cannot be converted to MDB files, you'll need to remove the database password to save an encrypted ACCDB as an MDB

Lastly, we'd love to hear more from you about this. How many of you are using encryption in ACCDB files vs. encoding in MDB files? Thanks!

by robcooper
Filed under: , ,

Comments

# Albert D. Kallal said on May 20, 2008 12:12 AM:

Thanks for this little tid bit Rob. A really good use of this feature is if you have a database that your shuffling between your office and home on a jump “usb” drive.  If you lose that jump drive, and someone else finds it, then you make it MUCH harder for that person to pull the data out. I can’t say this is military grade encryption, but it’s certainly a big step up from the old encryption system.

There’s only one real use warning scenario that this encrpt feature effects:

If you encode a file, the old way, or use the new security encryption feature, keep in mind that that resulting mdb, or accDB will now NOT compress any more. When I say compression, I’m not talking about the built in compact and repair, but I’m talking about using WinZip, or the windows built in zipping (compression) utility.

Compression works by looking for patterns in the document or file, and instead of storing  hundreds of like spaces in a row, it’ll store the number 100  + <space>. So compression systems look for repeating data.

MS access applications tend to compress extremely well. Typically you’ll see a 17 meg access file compress down to less than 5MB in size.

If you use the old encoding system, or this new encryption system, you will find that if you use a zip (compresson) utility on that file, you not find any reduction in size because the internals have been scrambled and encoded.  The compression utilities will not find any repeating data anymore, and therefore they don’t compress the file really at all.

I have some built in backup utilities for some of my access software that backs up the data to a ftp site. Hence, I usually zip (compress) my access files before I do a FTP upload due to such large file reductions in size.

So about the only usage consideration here is if you’re going to FTP the file to a ftp site or your web server, then you might not to want to use this encryption feature if your using compression before you upload the file.

Albert D. Kallal

Edmonton, Alberta Canada

kallal@msn.com

# Al said on May 20, 2008 4:28 AM:

Its a real shame the encryption only allows exclusive users, is this likely to be improved in the next version? are there any known workaround for a secure, multiple user database in Access 07 (accdb) or is it simply migrate to SQL server?

Also what sort of performance hit comparison can you expect when working with unsecure/encoded/encrytpted access files, i have heard figures over 30% slower when using encryption, which seems like a heavy price to pay!

# Albert D. Kallal said on May 20, 2008 7:25 AM:

>Its a real shame the encryption only allows exclusive users

No, that is incorrect. Only when you DO THE encryption, you have open the file exclusive because the whole file is being changed (or at least a critical part of it). However, once you have encrypted the file, then you can go back to opening it as shared like as always.

So, yes…to encrypt, you MUST open the file exclusive, and this is prevent other users from opening the file WHILE THE encrypting is occurring, else you would get a big mess.

However, once you done the encryption, then you can go back to opening the file as shared.

Albert D. Kallal

Edmonton, Alberta Canada

kallal@msn.com

# Mark Jacobs said on May 20, 2008 9:43 AM:

Hi Rob,

Hoping you can help me with this. Having a discussion with our security group regarding whether or not the Access 2007 encryption for ACCDB is strong enough. Please verify that the following quote regarding encryption provider and methds are used:

[for Office Access 2007 databases (.accdb)], the cryptographic service provider (CSP) is Microsoft Enhanced RSA and AES Cryptographic Provider. The cryptographic algorithm is AES-128, and the cryptographic key length is 128-bit. Note AES-128 can be increased to 256-bit via a registry setting.

Thank you for your help,

Mark Jacobs

# Al said on May 20, 2008 10:41 AM:

Apologies for my miss understanding, and thank you for the correction Albert.

And thanks rob for the useful stuff above on encoding/encryption.

I have been considering Security/Encryption for a while and thought this might be a useful place to get some community feedback under the Access teams eyes.

MS seems to suggest from lots of its documentation that the preferred structure for professional access apps is a front/back end split.

This link

http://msdn.microsoft.com/en-us/library/bb421308.aspx

is a very useful technical article about security in general, including encryption and splitting. However i have not been able to find much documentation on combining them.

I find this strange since it seems like this would be a sensible (and common place) combination of features to produce robust secure applications.

A robust, possibly runtime based, unencrypted front end, linked to an encrypted backend, with multi user support thrown in too, would surely be a decent starting point for most serious Access applications?

The backend password should be prompted for at runtime (so its not stored in the unencrypted front end), and the linked tables connection string updated accordingly.

Does anyone agree, or have any experience of such a set up?

For example, is it possible to programatically change the password for the backend through the front end (surely a typical request if this application structure was used, yet i have not been able to find a solution)

i suppose what i am getting at that i would be nice if MS perhaps produced another technical article on how a typical professional robust and secure Access 2007 accdb database application should be structured/implemented. I imagine this kind of information would be useful to lots of developers, and help reassure the sceptics who were not satisfied with the security available in mdb files. It would also compliment the other articles such as those on deploying runtime based apps.

this link http://www.pdtl.co.uk/Access2007/Access%202007%20vPPC.pdf

may be of use to people who are also more interested in the security of their access apps.

Of course, I could have just missed something obvious again, and apologise if I have!

Regards

Al

# Albert D. Kallal said on May 22, 2008 4:04 AM:

>The backend password should be prompted for at runtime (so its not stored in the unencrypted front end), and the linked tables connection string updated accordingly.

I for the most part don’t like to have the application re-link every time it starts up.  The amount of time this can take can be considerable, and it really slows down the usability to get in and out of the application (your users will not like this).

I do agree however that you for the most part should build some type of re-link system into your application so when the need arises to move the back end, or deploy a new version if your software, then re-linking can occur. This re-linking can and should occur without having to train the users about the linked table manager. And, with the runtime they don’t have the linked table manager in a menu anyway.

I think for the most part if you’re going to encrypt the backend to keep users out of it OTHER then using your front end, then I would hard code the password to the backend in the re-link code for the front end. As you said since it’s encrypted they should not be able to see the passwords by opening up the front end with an file editor. However I think this would assume that you hidden the User interface and locked out the shift bypass key on startup.

>For example, is it possible to programatically change the password for the backend through the front end

You probably can. I bet you could likely unencrypt, and then RE encrypt with a new password via automaton. My bets are regardless as to “how” you change the password to re-encrypt the data, in all cases you need EXCLUSIVE access to the back end when you do this change.  I think this means that you pretty much open up the back and with full version of MS access, make the password change, and then announce the password to all the people that need it. Then, when they open their front ends, it’ll will fail to open the backend, and then your re-link + password code will kick in and run.

For the most part, if you want a password on the front end, then you could give the front an encrypted password that has no relationship to the backend data file at all ( this is probably what I would do).

So, I would probably hide this BE password from the users, and they would never see or  even know about it.  Remember, this password would be the same for all users anyway.  If you decide to change the password, you’d have to have a means of updating or telling all users front ends that this password has been changed, and I think there’s a bit of a management issue here that will cause problems that might not be worth anyone’s time to solve.

>how a typical professional robust and secure Access 2007 accdb database application should be structured

The real problem is it is a physical file, and if people have the password, then they can open it up, and they can also take a physical copy of the file home with them.

I think if you need more security beyond the basic encryption and password features that are offered, and your moving into an environment where you have multiple users, and you want high security, then you’re only real solution is to move the backend data to SQL server. There’s a good number versions of SQL server, and there several versions that are free, and they work great with MS access.

More importantly, SQL server has the ability to use the windows password (active directory) for passwords. In other words the restrictions and what data they can get at is going to be based on their windows logon password. From a management point of view, we can’t just keep dumping more passwords and more logon prompts to all our users, eventually we’re gonna send them to rooms of padded walls of a don’t stop this!

More importantly, us developers can’t keep on writing password systems either.

In addition to SQL server, you can also consider SharePoint, and move your access tables to SharePoint and then link to them. In both cases, SQL server, or SharePoint, the windows password and security system is used.

You can also consider using what is called user level security, but that’s been depreciated in access 2007 (access 2007 still supports it, you just use mdb file format).

I think if you need to go beyond the basic features of encryption and security that access has built in, then your best move the back end data to SQL server.

Albert D. Kallal

Edmonton, Alberta  Canada

kallal@msn.com

# Gogoud said on May 22, 2008 8:38 AM:

I think Access 2007 encryption is good but there are shortcomings:

Unless you use a script such as here http://www.utteraccess.com/forums/showflat.php?Cat=&Board=89&Number=1514212&Zf=&Zw=&Zg=0&Zl=a&Main=1512441&Search=true&where=&Zu=124279&Zd=l&Zn=&Zt=7&Zs=&Zy=#Post1514212&Zp= you will need to tell users the database password, which may negate the security.

Also while the database is open its objects (i.e. the data) can be 'sniffed' from an external vb script using automation. The only workaround I know of is the Security Toolkit from http://premierdatatech.co.uk/resources.aspx

I hope that in a future release it is an option on a per database basis to make the Access Application object private.

# Alan Cossey said on May 24, 2008 4:26 AM:

Though I haven't tried it out yet, you may be interested in the following article (if you can read German and/or work out things from the VBA quoted). If, repeat if, I have understood it correctly, making your Access 2007 tables into system tables allows you to use the old user-level (MDW-related) security on your tables in a .accdb file. I'm not sure whether it means you are still vulnerable to the tools on the internet which allow you to break MDW-related security. Does anyone know whether using Access 2007 encryption helps? Since these tools would be able to hack the details in an MDW file, I am referring to the situation where:

* The developer supplies the application WITHOUT the MDW used during development.

* The back end is encrypted.

Alan Cossey (www.pdtl.co.uk/www.premierdatatech.co.uk)

# Alan Cossey said on May 25, 2008 4:23 AM:

Thanks to Tom Wickerath for pointing out that I didn't actually include a link to the article. Their home page is www.access-im-unternehmen.de and the link to follow is the one marked "Tabellen in Access 2007-ACCDBs schützen".

Alan Cossey

# Wayne Phillips said on May 25, 2008 5:56 AM:

Mark Jacobs: take a look at my article here on changing the default cryptographic service provider for Access 2007:

http://www.everythingaccess.com/tutorials.asp?ID=Changing-the-encryption-type-in-Access-2007

New Comments to this post are disabled

About robcooper

Rob Cooper is a Test Lead at Microsoft working on Microsoft Access. He started at Microsoft in 1998 working in Access product support in Charlotte, NC and then moved to Redmond to join the test team in 2001. For Access 2007, Rob worked on the new Grouping Pane for Reports, and security features such as Database Encryption and Disabled Mode. He is also a co-author on Expert Access 2007 Programming and Access 2007 VBA Programmer's Reference, both published by Wrox. Rob also spends time on www.utteraccess.com reading and answering questions.
Page view tracker