Use a password protected Access database in C#

[password protected Access database]

This example shows how you can open and use a password protected Access database in C#. It also shows how you can use the database’s password protection to provide a login form for your program.

Note that the security used by a password protected Access database may not be very secure. There are several program available for download that claim to recover lost passwords. Different versions of Access databases use laughably simple security so it may be possible to recover their passwords by simply looking in their headers. For more information, see the Stack Overflow post Access mdb password recovery.

In any case, this program demonstrates a general technique that you can use with other kinds of databases. For example, you could use a similar approach to provide a login for an Oracle, SQL Server, MySQL, or other database.

Before I explain the example’s code, I should tell you how you can make a password protected Access database.

Making a Password Protected Access Database

The details for making a password protected Access database varies depending on the version. In Access 2013, first open the database for exclusive use. To do that, press Ctrl+O and browse for the database file. After you select the file, click the dropdown arrow next to the Open button and select Open Exclusive.

Next, open the File menu. Select the Info tab and click Set Database Password. Enter and verify the password and click OK.

If you are using another version of Access, you may be able to find features similar to those described here. Otherwise search the internet for instructions for your version.

The Example

This example is similar to the example Display database pictures in a ListView control in C# except it uses a password protected Access database. Download that example to see details about how the program loads data from the database into a ListView control.

When this example starts, the following Form_Load event handler executes to get the database password.

// Get the password from the user.
private void Form1_Load(object sender, EventArgs e)
{
    // Get the password from the user.
    PasswordForm frm = new PasswordForm();
    if (frm.ShowDialog() == DialogResult.Cancel) Close();
    string password = frm.txtPassword.Text;

    // Select the first style.
    cboStyle.SelectedIndex = 0;

    // Initialize the ListView.
    lvwBooks.SmallImageList = imlSmallIcons;
    lvwBooks.LargeImageList = imlLargeIcons;

    // Make the column headers.
    lvwBooks.MakeColumnHeaders(
        "Title", 230, HorizontalAlignment.Left,
        "URL", 220, HorizontalAlignment.Left,
        "ISBN", 130, HorizontalAlignment.Left,
        "Picture", 230, HorizontalAlignment.Left,
        "Pages", 50, HorizontalAlignment.Right,
        "Year", 60, HorizontalAlignment.Right);

    // Compose the database file name.
    // This assumes it's in the executable's directory.
    string db_name = Application.StartupPath +
        "\\books_with_images.mdb";

    // Compose the connect string.
    string connect_string =
            "Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=" + db_name + ";" +
            "Mode=Share Deny None" +
            ";Jet OLEDB:Database Password=" + password;

    try
    {
        // Try to connect to the database.
        OleDbConnection conn = new OleDbConnection(connect_string);
        conn.Open();

        // Load the data.
        LoadData(conn);

        conn.Close();
        conn.Dispose();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        MessageBox.Show("Invalid password");
        Close();
    }
}

The code first displays a PasswordForm. That form simply contains a password text box and OK and Cancel buttons. (See the picture at the top of this post.)

The password form’s OK and Cancel buttons have DialogResult properties set to OK and Cancel, respectively. If the user clicks the OK button, then the Load event handler’s call to ShowDialog returns OK. If it returns anything else, for example if the user clicks Close or presses Alt+F4, ShowDialog returns Cancel and the Load closes the main program’s form and the program ends.

If all goes well, the Load event handler stores the password entered by the user in the variable password. The code then performs a few tasks to prepare the main form’s ListView control.

Next, the program composes a string holding the database’s name. In this example at design time, I opened Solution Explorer, right-clicked the project entry, and selected Add > Existing Item. I then selected the database and clicked Add.

When you add a database to a project in this way, Visual Studio tries to create a connection for the database and add it to the project. This program doesn’t need that connection, so I closed the resulting dialogs.

[password protected Access database]

Because this example uses a password protected Access database, Visual Studio displays the dialog on the right. Whenever you see this dialog, click No to prevent Visual Studio from including a database password in a configuration string that any hacker could read. In this example, the program will get the database password from the user at run time.

After it composes the database’s name, the program builds a connect string by filling in the database’s path and the password entered by the user. You may need to modify the string depending on the type of database and your runtime database engine.

Next, the program starts a try catch block. It creates an OleConnection object passing its constructor the connect string.

The code then tries to open the connection. This is where the program will fail if the password is incorrect. If there is an error, the catch block writes the exception message to the Console window, displays an error message, and closes the main for to end the program.

In general, you should not give detailed error messages to the user at this point. For example, if you are using a more sophisticated database that requires a user ID and password, you should not tell the user that the user ID is invalid because that lets an attacker know that he should try a different user name. Similarly, you should not say that the user name is valid but the password is invalid. That lets an attacker know that he has a valid user name. Simply give a generic “Invalid user ID/password” message and leave it at that.

If any other error occurs, for example if the code tries to use a database table that doesn’t exist, the user sees an incorrect error message. If you run the program in Visual Studio, you can read the true message from the Console window.

If the database opens successfully, the program calls the LoadData method to load data into the program’s ListView control. Download the example and see the earlier post for information about how that works.

Finally, the program closes and disposes the database connection.

If you need to use the database again later, store the connection in a form-level variable so you can reuse it. Because the connect string is already filled in, you will only need to open, use, and close the connection.

Download the example and give it a try. The password for the included database is Secret.


Download Example   Follow me on Twitter   RSS feed   Donate




About RodStephens

Rod Stephens is a software consultant and author who has written more than 30 books and 250 magazine articles covering C#, Visual Basic, Visual Basic for Applications, Delphi, and Java.
This entry was posted in database, Office and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *