How to Plug Microsoft Access accde and mde Security Leaks

by Patrick Wood 10. October 2011 20:54

I was shocked. I had heard that Constants and Variables could be seen by opening an accde or mde file with a hex editor but when I opened my file I was stunned to see so much of my data, objects, and code in plain text. I am not an Access Security expert, but I decided to take on the challenge of this security issue and when I was through I was very pleased with the results. In this article we will examine the security risks of accde and mde files and present some ways you can greatly improve the security of your files.


The Bad News About accde and mde File Security

You don't have to be an Access expert to break the security of an Access Database. Anyone can do it with a free hex editor easily downloaded from the internet. The hex editor revealed the data in every local table in my accde database and worse.


1) A Simple Hex Editor Reveals Much Information

Here is a partial list of what I was able to read with a free hex editor:

  • Names of all Tables both Local and Linked
  • All Field Names in Local Tables
  • All Values in Tables Except for Hyperlinks
  • All Query Names and Query SQL
  • All Global Constants
  • All Module Names
  • All Procedure Names
  • Most Procedure Arguments
  • Some Code in Procedures
  • Usually Two or More Variable Names in Each Procedure
  • All Form RecordSource Queries and SQL
  • All Control Names and Property Values
  • All List Box and Combo Box RowSource Queries and SQL Statements
  • All References with File Names and Full Paths

I found the code of a couple of procedures virtually intact. As we shall see, this revealed data can lead to even further exploitation.


2) Your Forms and Controls are Vulnerable

If you have controls on a form that hold sensitive data such as confidential Customer information a user can very easily get that data if they can open the database. A control with the name txtSQLServerConnection may give someone access to the company database. Who knew we were so vulnerable?


3) Those Who Know How Can Read Your Constants and Run Your Procedures

For those who know how, it is very easy to read your Constants and run your Subs and Functions. I will not reveal how it is done here, but for experienced developers it is a piece of cake.

I know of no way of safeguarding your Constants short of encrypting your database and that option is not always available. So I think it best not to use Constants for any sensitive data like Connection Strings or Passwords.

Most procedures can easily be run by those who know how. This can reveal a lot of sensitive data and if you use queries in code your database is especially vulnerable. Your data may easily be read, altered, or deleted. That is depressing enough without going further, so I will share some good news.


The Good News About accde and mde File Security

1) Encrypting Our Database May Provide Greater Security

Encrypting your database with a password may provide better protection in keeping unauthorized users from getting information from your database. I wrote "may provide" because there are some cases where this does not help. Unfortunately it can't keep the authorized users from giving their password to others or taping it on a note on their monitor or trying to hack the database themselves. The security of your database is only as strong as your weakest user. In addition, encrypting a database is not always an option for distributed applications, especially when users have different versions of Access because they can only be opened using the version of Access by which they were encrypted.


2) We Can Obfuscate the Names of Procedures, Arguments, and Variables

Variables strA, strB, etc. are a lot better at concealing important information than strAccountNumber and strPassword. Fortunately your comments all get removed when the accde or mde file is created. So if you don't want to purchase software to obfuscate your code for you, you can do it yourself and use comments in your original accdb or mdb file to document your code so you can always know what your code is doing.


3) We Can Use a Procedure Argument As a "Password"

It is so very easy to run the procedures in an accde or mde file once you know how. Give me your unencrypted accde or mde file and I can be running procedures in it in less than 5 minutes without any preparation in advance. So I am not going to share that secret here. But I will share how you can easily foil this type of attack.

If we can use Passwords to open files then we can use them to protect our code from hackers who would run our procedures. When I write an important procedure I add an additional Argument that is used like a Password. When the Procedure runs it checks for that Argument and if it is not correct then the Procedure does nothing. The following example shows how this works.

'The PartColorNotes Procedure is named to mislead hackers.
'In this case it returns the SQL Azure Database Connection String
Public Function PartColorNotes(strIn As String) As String
    If strIn = "Wb_gR%/PD\-k&yZq~j>l" Then
        PartColorNotes = "ODBC;" _
            & "DRIVER={SQL Server Native Client 10.0};" _
            & ",1433;" _
            & "UID=MyUserName@MyServerName;" _
            & "PWD=MyPassword;" _
            & "DATABASE=MySQLAzureDatabaseName;" _
            & "Encrypt=Yes"
        PartColorNotes = vbNullString
    End If
End Function


First, if I was a hacker I could not care less about the PartColorNotes. And I would definitely give up trying to run this procedure long before I discovered the "Password" and that is the point. We cannot keep the bad guys totally out of our database but we can make it so difficult to get our information it is not worth the effort. Some good news about this method is that after using my other security measures I did not see any string values in my accde and mdb files so this security measure should be very effective.

Below is a procedure that can produce the "Passwords" for your Procedure Arguments and which you can also use to create SQL Azure and SQL Server Passwords.

' Procedure : GetPWDCharacters
' Author    : Patrick Wood
' Date      : 8/10/2011
' Purpose   : Get Random Characters (These are also safe to use for ODBC Passwords)
' Arguments : intCount is the number of characters you want returned
' Example   : strPassword = GetPWDCharacters(15) - Returns a string of 15 characters
Function GetPWDCharacters(ByVal intCount As Integer) As String
    Dim i As Long
    Dim j As Long
    Dim arrCharacters() As Variant
    Dim strCharacters As String

    'Check function arguments.
    Debug.Assert intCount >= 0

    'Build an array of Characters including all letters and numbers and safe special characters
    'This procedure omits these ODBC characters which Microsoft advises us 
    'not to use in Login Names and Passwords: []{}(),;?*!@.
    'See for details.
    arrCharacters = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", _
        "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", _
        "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "A", "B", _
        "C", "D", "E", "F", "G", "I", "J", "K", "L", "M", "N", "O", "P", "Q", _
        "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "~", "#", "$", "%", "^", _
        "&", "_", "+", "-", "=", "<", ">", "\", "/")

    'Loop for the length of the requested password string
    For i = 1 To intCount

        'Get a random number to use with the array
        j = Int((UBound(arrCharacters) - LBound(arrCharacters) + 1) * Rnd + LBound(arrCharacters))

        'Get a Random character from the array and add it to the string
        strCharacters = strCharacters & arrCharacters(j)
    Next i

    GetPWDCharacters = strCharacters
End Function


4) We Can Use SQL Server Or SQL Azure As a Back End

The only way to secure the data in your tables is to place your tables in a secure back end database such as SQL Azure or the Free Express version of Microsoft SQL Server. SQL Server Express has excellent security features that even meets HIPAA security requirements. SQL Server's fine grained security features also enable you to Grant and Deny Permissions to SQL Server objects or individual users to Database Roles. And SQL Server Express is free for you to download and distribute.

There is no foolproof way to keep data stored in Access tables secure. Nothing I did could keep the table values from being read as plain text in a hex editor. Even if you did go through the trouble of encrypting your data before storing it in your tables, it is still too easy for someone to delete or change your data. That is why it is so great to have the free Express versions of SQL Server available to us.

If you are using SQL Azure or SQL Server linked tables in Access you can write a function that returns the Connection String such as the PartColorNotes Function above. This enables you to safely use code to access your tables. To avoid the security risks involved with using ODBC linked tables see my article entitled Building Safer SQL Azure Cloud Applications with Microsoft Access.


5) Decompiling Our Database Immediately Before Creating Our accde or mde File Helps

This is a very crucial step. This did more to keep my sensitive information from being read with a hex editor than anything else I tried, with the exception of encrypting the database with a Password. This is most effective if you use linked tables for important information which prevents a hex editor from reading the table values.

Databases need to be Decompiled because Compacting and Repairing does not remove all the old code you no longer use. It leaves behind a large amount of sensitive information that can be exploited. I found that Decompiling my database and then immediately creating an accde or mde file removed a huge amount of text, code, and other sensitive information from my file. This was so effective it restored my faith in using accde and mde databases.

Decompiling your database should be the last step you take before creating the accde or mde file. FMS provides an article with instructions that shows you how to decompile your database as well as create a shortcut that makes decompiling much easier. After decompiling your database it will usually be much smaller and less likely to have unexplained errors.

Decompiling is however an undocumented and unsupported feature so to be safe you should never decompile without making a backup copy first. It is best to keep your original file in a safe place and decompile the copy of the database.


We have looked at some of the serious security issues with accde and mde files and have presented a number of steps to greatly improve the security of your databases. You should now be able to distribute your accde and mde files with greater confidence in their security.



Comments are closed

About the author

Access Security Blog is a collaborative effort of several people, including Tom van Stiphout, Scott Diamond, Patrick Wood, and Paul Bardinelli.