It is extremely rare, but we do get the occasional question from a customer on how to recover or repair an SQLite3 configuration database file. SQLite3 is fairly robust, but sometimes things happen like unexpected shutdowns or hardware problems with storage, or perhaps an incomplete write due to 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.
When you see for example this error:
exception in AuthDelegateProplist: (DatabaseError) database disk image is malformed
This could indicate that the configuration database files have an issue.
By default OpenVPN Access Server uses SQLite3 database files. It is also possible to convert it to MySQL or a similar RDS based system. We have a document to describe how to make live backups of the OpenVPN Access Server, and how to convert to a Relational Database System like MySQL. This is all described on the configuration database management and backups page. If you are using SQLite3 (the default) we would recommend you implement the backup procedure mentioned on that page. If you have backups you should use those instead of trying to repair the damaged database. If your Access Server has been adjusted to use another database system than SQLite3, then the instructions here will not work for you, but then you will also not be seeing the error message shown above.
Check the environment
You need to make sure that your server is stable, up-to-date, and that the hardware is not exhibiting problems with 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 keep in mind that you need adequate free disk space. If your disk is full, problems can occur when writing data to the configuration files.
Stop Access Server and make backups
A logical step is to stop the Access Server and make backups.
Use the following commands on the command line interface, while logged on with root privileges:
service openvpnas stop cd /usr/local/openvpn_as/etc/db/ mkdir backup cp *.db ./backup
Now 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 needs the Access Server to be stopped. If you want to run live backups, see the documentation on our website elsewhere.
Run recovery process
This process uses SQLite3 to read the contents of the database files, convert it to SQL commands, and dumps it straight into a new SQLite3 process to build up 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 cannot read at all will be skipped. With luck your damaged file only has one damaged record and you can then work on restoring whatever is broken after you have 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 all 4 database files. If you have only a problem with a specific file, you may skip the others. It is divided with divider lines to indicate the portions for each database file that you can repair.
Stop the Access Server service with this command as root user:
service openvpnas stop
Then 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
Now restart the Access Server service:
service openvpnas restart
Now test your Access Server and see if everything is working alright. If you encounter problems, you can restore the *.db.old files to their original names to get it back to how it was before you ran the above recovery procedure. If there are still problems you should contact us for support.