Skip to main content

Repair Configuration Database SQLite3 Files

In the rare instance that you have an issue with Access Server's SQLite3 configuration database files, this troubleshooting topic should help you recover or repair a file.

SQLite3 is fairly robust, but sometimes things happen, like unexpected shutdowns, hardware problems with storage, or an incomplete write due to a lack of disk space. Whatever the cause, when you encounter a problem with the database files being unreadable, the following procedure may help to restore your data, minus perhaps one or two most recent records that are simply unrecoverable.

Example error message:

exception in AuthDelegateProplist: (DatabaseError) database disk image is malformed

Such an error message can indicate that the configuration database files have an issue.

For more information about Access Server configuration files, refer to this topic: Config Database.

Access Server saves all of your network specifications in configuration files. By default, these files are saved in an SQLite3 database on the same server. Optionally, Access Server can store configuration files on a separate database server.

These tutorials show you how to create live backups and convert to a relational database system like MySQL:

If you already have backups, refer to this tutorial to restore Access Server from your backups:

Step 1: Check the environment

You must ensure that your server is stable and up to date and that the hardware isn't exhibiting problems reading and writing data on the hard disk.

To describe the full procedure to diagnose a server system goes beyond what this documentation is intended for, but you should be able to find suitable tools from your server or disk manufacturer, like memory testing programs, disk testing programs, hardware diagnostics, and so on, or basic tools like badblocks and fsck to figure out if there are any problems with the data storage.

Also, remember that you need adequate free disk space. If your disk is full, problems can occur when writing data to the configuration files.

Step 2: Stop Access Server and make backups

A logical next step is to stop the Access Server and make backups.

  1. Connect to your console with root privileges.

  2. Run the following commands:

    service openvpnas stop
    cd /usr/local/openvpn_as/etc/db/
    mkdir backup
    cp *.db ./backup

If anything happens during the database recovery steps, you can simply copy back the files in the backup subfolder and get things working as they were before you started the recovery process. This is a simplistic backup process that requires the Access Server to be stopped. If you want to run live backups, follow the detailed tutorial: How to Back Up Access Server Configuration.

Step 3: Run the recovery process

This process uses SQLite3 to read the contents of the database files, convert them to SQL commands, and dump them straight into a new SQLite3 process to build a new database.

If the original database is undamaged, this will result in a perfect replica of the original database to a new database file.

In the case of a damaged original database file, the SQLite3 program will try to read all the usable information it can and put that into a new database file. Anything it just can't read at all will be skipped. With luck, your damaged file only has one damaged record, and you can then work on restoring whatever's broken after you've completed the procedure.

For example, if a single user account has been damaged, then after this recovery procedure, you can try to delete that one user and add the user again to restore it to normal functionality. This will have to be determined on a case-by-case basis.

The following commands repair four database files. If you only have a problem with a specific file, you can skip the others.

  1. Sign in to the console with root access.

  2. Stop the Access Server service:

    service openvpnas stop
  3. Run these commands to repair the database files:

    cd /usr/local/openvpn_as/etc/db/
    mv config.db config.db.backup
    sqlite3 config.db.backup .dump | sqlite3 config.db
    mv config_local.db config_local.db.backup
    sqlite3 config_local.db.backup .dump | sqlite3 config_local.db
    mv certs.db certs.db.backup
    sqlite3 certs.db.backup .dump | sqlite3 certs.db
    mv userprop.db userprop.db.backup
    sqlite3 userprop.db.backup .dump | sqlite3 userprop.db
    mv log.db log.db.backup
    sqlite3 log.db.backup .dump | sqlite3 log.db
  4. Restart the Access Server service:

    service openvpnas restart
  5. Test your Access Server and see if everything is working.

    • If you encounter problems, you can restore the *.db.old files to their original names to restore them to their original state before you ran the above recovery procedure. If there are still problems, contact us for support.