Microsoft Access Support

Les Desser · Guest

I wonder if someone could give me some pointers on the following.

I need to securely encrypt an Access 2003 database currently sitting on
a server with multiple users linking to it from their individual PCs via
an Access 2003 front-end.

I need a hardware/software solution that will:-

1. Encrypt the database on the server so that a copy of it is of no use
to anyone. This is to cover if the server is stolen.

2. Ensure that only authorised users directly connected to the network
can access the database and preferably only via the designated Access
front-end database.

3. Authorised users cannot get a decrypted copy of the file via their
PCs - i.e. cannot use Explorer or similar to copy the file in decrypted
form.

I presume that the solution would include hardware and software
elements.

I would be grateful for any guidance.

Many thanks.
--
Les Desser
(The Reply-to address IS valid)

Keith Wilby · Guest

"Les Desser" <NewsDump1@dessergroup.com> wrote in message
news:oqNl8QroAd$HFA5u@dessergr0up.invalid...

MikeB · Guest

"Keith Wilby" <here@there.com> wrote in message
news:47fdd2fc$1_1@glkas0286.greenlnk.net...

Salad · Guest

Les Desser wrote:

Larry Linson · Guest

As far as I know, there is no security that can be applied via Access itself
that will fulfill your requirements with an "Access" (Jet or ACCDB)
datastore. Access Workgroup Security is no longer supported for Access
2007's standard ACCDB and ACCDE; software to de-secure an Access MDB for
versions prior to Access 2007 can be found, freely downloadable, by
searching the Internet; the encryption provided by Access itself only
prevents reading from outside Access (e.g., to stop someone exploring with a
low-level disk reader/dump program). Access' encryption is not customized to
the database and can be read by any copy of the same version of Access, or
the Access runtime.

The normal approach for extremely sensitive data is to store it in a server
database; Access can be used as a client application, either with MDB
connectivity via ODBC to any ODBC-compliant server DB, or (with MS SQL
Server only) via the ADP, aka Access Project, via OLEDB (however, my
understanding is that security with ADP has flaws, too, and is not
recommended by knowledgeable users for sensitive data). Then, you can use
the security provided by the server database, and the network itself -- and
on those issues, you would need to find an appropriate newsgroup for the
server database and network security, or vendor sites, to determine if the
combination can meet your requirements.

A generally accepted axiom, in regards to encryption and security with file
server databases such as Access, is that if you allow someone access to your
database, or they have it in their hands, any security you apply can be
broken. The availability of "cracks" in the "warez" world for very
expensive software packages distributed to users confirms this -- the
manufacturers and vendors of software packages costing tens of thousands of
dollars or more per copy certainly have adequate incentive to protect that
software in the most secure way from unauthorized access, and just can't
manage to defeat the "warez phreaks" who take security as a challenge (often
providing their cracks for free, just to demonstrate that they can). Many of
those cracks, illegal though they may be, do work as advertised, to the
dismay and chagrin of the people who sell the software to which they give
unlimited access.

I regret that I don't have more encouraging words for you.

Larry Linson
Microsoft Office Access MVP

"Les Desser" <NewsDump1@dessergroup.com> wrote in message
news:oqNl8QroAd$HFA5u@dessergr0up.invalid...

Les Desser · Guest

In article <B8idncgFlbWVhWPanZ2dnUVZ_v2pnZ2d@earthlink.com>, Salad
<oil@vinegar.com> Thu, 10 Apr 2008 06:37:10 writes

Les Desser · Guest

In article <sOqLj.23706$4O1.6011@trnddc03>, Larry Linson
<bouncer@localhost.not> Thu, 10 Apr 2008 15:58:16 writes

[...]

Thank you for your comprehensive response.

Also thanks to the other posters for their ideas.

The background is that the Access application has been developed over
many years and it is not really viable to re-write it.

Due to the prospect of some commercially sensitive data being now stored
in the database, it has become desirable to secure the data.

I do not have major concerns about the Access front end as

1. the staff using it are trustworthy
2. the data would have to be extracted table by table
3. the front-end is an MDE and I think I can securely (reasonably) hide
the table view.

To steal the data via the front end (or an alternative front end once
Access security had been broken) would be non-trivial and they would
have to work within the office (as the data would be encrypted).

My main concern is how to, on the one hand, encrypt the data on the
server (TrueCrypt?) so that if the server is stolen the data cannot be
read, and on the other hand, allowing the Access front end to read the
decrypted data but somehow blocking access to the decrypted data to the
Windows file copy facility.

As far as I can see, once the decrypted data is visible to the PCs
running the Access front end, it is also a matter of a few seconds to
copy the whole decrypted data mdb using Explorer.

Salad · Guest

Les Desser wrote:

The Frog · Guest

Hi Les,

This is a problem that I have solved once before, and I can tell you
that it is fraught with dangers. I have managed to incorporate AES 256
bit encryption on the individual tables, complete with a user
control / access system. I must stress just how much a pain in the
arse this was / is.

The way that I achieved this was to use encryption the same way the
EFS does. Basically it works like this:

1/ You need to generate a *RANDOM* key to be used for the AES
algorithm for each table. I used GUIDs for this and adapted the GUID
to a key.

2/ You need to make a 'master' Asymettric key pair to act as a data
recovery in case of emergency. Use a different GUID.

3/ You take the 'master' key pair, and using the private key encrypt
the AES key for each table, and store the encrypted AES key as a table
property of your defining.

4/ Lock away the AES keys, as well as the 'master' key pair.

5/ At the field (contents) level you use the AES encryption, specific
to the table, to encrypt the contents of each field.

What you should now have is the entire database encrypted with AES
encryption. (I will try to find the links to the VBA code for this).
At this stage no user can access the data in any meaningful way,
unless they happen to have a neat way of breaking either the symmetric
encryption on the fields or the asymmetric used to encrypt the AES key
itself.

Now comes the user part:

For each user you need to generate an asymmetric key pair. This is in
turn used to make an encrypted copy of the AES key, which is attached
to the tables as a property with a name of your choosing. I suggest
the property name either be the user name / id, or some other easily
identifiable term that is specific to each user.

In this way you can also only give users access to the tables that
they need simply by making sure that you dont issue them with an
encrypted copy of the AES key for that table. I suppose you could
extend the model further even to the column / field level if you
wanted, but I thought that to be overkill.

I used the public part of the key pair to encrypt the AES keys, and as
per normal kept the private part private Smile

It was my intention to
eventually use certificates with a token to handle this but the
project never went that far.

Anyway, back on with the task at hand. We had two options for handling
the private keys with this. The first was to have them stored in files
on disk / usb key etc.., or alternatively to have them stored in a db,
themselves encrypted. We eneded up using the second system due to
practicality. Users were asked for a username / password to access the
system. The password was MD5 hashed, and in turn the MD5 hash was used
to decrypt the private key again using AES. The way that we knew if
the password was correct was to have the MD5 hash also stored in
encrypted format with the private key. This became the weak point of
the system, but as I said we didnt get to the point of using
certificates and tokens. If the username / password combo was able to
successfully decrypt the AES encrypted private key it would also
successfully decrypt a copy of the MD5 hash associated with that key.
This was done in a table with 3 columns, username, password, private
key.

The administration of this was done through a separate database / app
that was not accessable to anyone except the administrators. It was
kept on a secure usb key (if you can call them that). I am sure you
can think of ways of securing the physical media. User key pairs were
added as needed and expired / removed as needed.

I am sure that you could also implement time restrictions, as well as
network card or IP address restrictions to the application. For
example it is possible to gather the MAC address of a network card, as
well as the IP address of the card as a means of testing the 'local'
environment of the application, though I would have to think about how
you would secure / administer that data.

In the end it comes down to how far you want to go to secure the data
and is it worth it? The method described above will certainly give
your data a heavy level of encryption by most current standards, but
that does not necessarily mean that the model suits your purposes or
is suitable for the task.

The app I wrote is currently stored securely and I do not have access
to the finished code, however I do remember that I located a lot of
what I needed freely available on the net. I will have a look over the
weekend and see if I can locate the sites where the code came from for
the different algorithms.

Hope this gives you some food for thought.

Cheers

The Frog

paii, Ron · Guest