Recipe 1.4 Connecting to a Secured Access Database

Problem

You want to connect to a Microsoft Access database that has been secured with user-level security and a workgroup file.

Solution

Use the Jet OLEDB:System Database attribute in the connection string to specify the path and filename of the workgroup information file or system database.

The sample code contains a single event handler:

Connect Button.Click

Creates and opens a connection to a Microsoft Access database secured with user-level security and a workgroup file using the OLE DB .NET data provider. Information about the database is displayed from the properties of the OleDbConnection object.

The C# code is shown in Example 1-4.

Example 1-4. File: AccessSecureForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data.OleDb;

//  . . . 

private void connectButton_Click(object sender, System.EventArgs e)
{
    StringBuilder result = new StringBuilder( );

    // Build the connection string with security information.
    String connectionString =
        ConfigurationSettings.AppSettings["MsAccess_ConnectString"] +
        @"Jet OLEDB:System database=" +
        ConfigurationSettings.AppSettings["MsAccess_SecureMdw_Filename"] +
        ";" + "User ID=" + userIdTextBox.Text + ";" +
        "Password=" + passwordTextBox.Text + ";" +
        Environment.NewLine + Environment.NewLine;

    result.Append(connectionString);

    // Create the connection.
    OleDbConnection conn = new OleDbConnection(connectionString);

    try
    {
        // Attempt to open the connection.
        conn.Open( );

        result.Append(
            "Connection State: " + conn.State + Environment.NewLine +
            "OLE DB Provider: " + conn.Provider +
            Environment.NewLine +
            "Server Version: " + conn.ServerVersion +
            Environment.NewLine);

        conn.Close( );

        result.Append("Connection State: " + conn.State +
            Environment.NewLine);
    }
    catch(System.Data.OleDb.OleDbException ex)
    {
        result.Append("ERROR: " + ex.Message);
    }

    resultTextBox.Text = result.ToString( );
}

Discussion

Microsoft Access user-level security requires an additional file—the workgroup information or MDW file—in addition to the database or MDB file. This file contains the user and group information for the secured database while the actual permissions are stored in the database file.

When you connect to a secured Jet database, the user ID and password are validated against the values in the MDW file. The permissions are obtained from the MDB file. To connect, the location of both the database file and the workgroup file must be supplied.

The OLE DB provider for Microsoft Jet has several provider-specific connection string attributes in addition to those defined by ADO.NET. To open a database secured by Microsoft Access user-level security, use the Jet OLEDB:System Database attribute in the connection string to specify the path and filename of the workgroup information file or system database. This corresponds to the OLE DB property DBPROP_JETOLEDB_SYSDBPATH.