[AccessD] Field Level AES Encryption

Thank you for your review of this software. Very helpful.

However there is a flaw in the modifications you have made. The storing of the password in our application. You are relying on Access encryption to protect that password. I do not actually know what type of encryption Access uses, but I suspect it might be weaker than AES. So you just built a back door.

IMHO.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jm.hwsn
Sent: Thursday, April 28, 2011 1:00 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Field Level AES Encryption

I thought I would send this information to this group, especially since Philippe Pons asked the question about hiding information.

I had a requirement to encrypt a single field within a database.  This field contains the Social Security Number (SSN) of the users of the database.  The SSN is used as an individual's password and by government standards MUST be encrypted.

After much searching and testing different suggestions, I found a solution.
It involves a Class and a module and uses the American Encryption Standard
(AES) developed by Joan Daemen and Vincent Rijmen hence call Rijndael.

The class was written in 2001 by Phil Fresle and works flawlessly.  I don't understand how it works nor would I even attempt to change it.
The original code was written in C.  The Class was written for VB6 but works in VBA without modifications.  Fresle is from the UK. his website is:
www.frez.co.uk/ On his website is the latest version of the Class.

But, I found the best solution which incorporates Mr. Fresle's class on an Excel Website by Dermot Balson from Australia.
His website is:  http://www.westnet.net.au/balson/ModellingExcel/VBA.shtml
Mr. Balson by his own admission is "an actuary who loves working in Excel."
The class on Mr. Balson's site needs to be updated with the latest version from  Mr. Fresle's site.

What I like about Mr. Balson's solution is that the module contains two functions.  One to encrypt the field and the other to decrypt the data.
Also,  changing from 128 bit to 192 or to 256 bit encryption is simply changing a public KeyLength.  The results are stored in a table in pure ASCII so it's easy to use the ASCII key if needed.
What I didn't like was the PassPhrase (password) was required for each function and the decryption of the data left blank spaces at the end.

So I modified the module by creating a function for the password. of course it's hidden in the module and password protected through Access' encryption.

Now I can change the PassPhrase in one place and change the encryption key size at anytime. And if needed, I can use the password field as text.

Life if good!
HTH
Jim



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com