I recently found myself supporting an Excel spreadsheet
created by business users using Office 2010 while waiting for my next project.
I have been tasked with updating a new Access database that was created by one of
the business users with data from the sheet as it is processed. Should be a
simple task, right?
The Access database is broken apart into a frontend
consisting of the forms and queries and the backend that contains the tables. One
of the requirements of the Access database is that the tables are password
protected. When a password is applied to an Access database, the file is encrypted
so the file simply can’t be opened in another application and read. A password
is required when using either Access directly or using another product and
importing data.
Beginning with Office 2007, Microsoft began using their
encryption library “Microsoft Enhanced Cryptographic Provider v1.0” (or
CryptAPI) as described in this
post by Garry Robinson and this
post by Wayne Phillips. By default the file is encrypted with RC4 using a
40 bit key and the password is hashed using SHA-1. The big takeaway from this
new encryption method in Access is that Microsoft stopped embedding the
password in the file as it had done in Office 2003 and earlier, so it is no
longer trivial to decrypt the file without a password. A brute force approach
should be necessary. Unfortunately, RC4 is susceptible to attack and
decryption. A short explanation is available at Wikipedia.
In Office 2010 another new Cryptography library was
introduced, the Cryptography
API: Next Generation. It is designed to be a long term replacement to the
CryptoAPI. By default, Access 2010 is encrypted with this library and uses AES-128
for the file encryption and SHA-1 for the password hashing. This provides a
much stronger encryption level. While it is susceptible to cracking at speeds
greater than a brute force attack, it is still quite slow to break the
encryption. This Wikipedia
article explains in more detail.
So the big question is do we really need an encryption
lesson in order to connect Excel to Access and insert some data? Shouldn’t all
that be handled behind the scenes? The answer to the last question is yes it
should, but no it’s not. While the AES-128 works seamlessly while you are in
Access or Excel independently, while trying to connect the two platforms it is
not working as it should. In my case, using either ADO or DAO to try to make a
database connection in VBA to connect to the Access database, a “Not a valid
password” error is thrown. Apparently the Next Generation library is used in
Access and in Excel to encrypt and decrypt their own files, but was not
implemented (or incorrectly implemented) in the ADO and DOA data libraries. This
Technet
thread shows the official answer by Microsoft is that it is not possible to
connect via ODBC and my tests and others
have shown the same issues with DAO and ADO.
Is there a solution to this? In Access under File ->
Options -> Client Settings is a selection for Encryption Method. Changing
this from Use default encryption (Higher Security) to Use legacy encryption
(good for reverse compatibility and multi-user databases) switches from the
Next Generation library and AES-128 back to CryptAPI and RC4-40. While this is
less secure, it does allow you to connect to the Access database through VBA. I
have read that users using Windows XP have not had this issue, but in reading
over the Next Generation library details, it seems that the Next Generation was
shipped as a part of Server 2008 and Windows 7 and greater, so my suspicion is
that Windows XP is already defaulting back to CryptAPI as it is the latest
cryptography library installed by default.
The majority of my testing has been on Windows 7 32-bit with
Office 2010. I have also tested with Windows 8.1 64-bit and Office 2013 with
the same results.
No comments:
Post a Comment