Configuration database management and backups

How the configuration is saved

All of the configuration of the OpenVPN Access Server is by default stored in these 5 files:

  • /usr/local/openvpn_as/etc/db/config.db
  • /usr/local/openvpn_as/etc/db/certs.db
  • /usr/local/openvpn_as/etc/db/userprop.db
  • /usr/local/openvpn_as/etc/db/log.db
  • /usr/local/openvpn_as/etc/as.conf

Note that it is possible to change the authentication backend to a database server like MariaDB or MySQL, and that if you have done this, then the above of course doesn't apply to you.

The as.conf file is a simple text file that is the same on all OpenVPN Access Server installations unless you have altered something here, like for example if you disabled client certificate authentication. The .db files are of the SQLite3 database type. It is possible to switch to another database backend if this is required for whatever reason. A database backend like for example MySQL or MariaDB is suitable for this purpose. By default though the SQLite3 database type is what Access Server uses to store all of its configuration settings, certificates, user specific properties, and a log database that contains entries about who logged in when, how long they were connected, and how much bandwidth they used. The log database can be queried with a separate logdba tool designed for pulling this information out of the database file.

If you are using local authentication mode, the user password are also stored in these configuration files. If you use PAM authentication mode, the user specific properties like auto-login privilege and static IP address and such are stored in the configuration files, but the password for the user is stored in the operating system. That is something that is not part of the OpenVPN Access Server configuration, but lies outside of it. Worst case what happens if you backup and restore this configuration to a new installation of Access Server on another server is that you need to set passwords for these users again if you use PAM authentication. If you use LDAP or RADIUS, then the passwords are stored in there and will remain the same.

Viewing the current server configuration

The tools sacli and confdba can be used to view and manipulate the configuration database file and the user properties file. In general we advise the sacli tool as it has some additional logic that tries to ensure that the configuration remains sane, while confdba just edits the values in the database without bothering to run any sanity checks whatsoever. The sacli tool also has some functions to work with the certificates stored in the certificate database file. The logdba tool is used to query and wipe the log database file which keeps track of when users logged in, how long they were online, what errors were reported when users tried to connect, and how much data they've used.

To see what the current server configuration is:

./sacli ConfigQuery

To see what all the user and group properties are:

./sacli UserPropGet

To see the properties of a specific user or group:

./sacli --pfilt <USER_OR_GROUP> UserPropGet

Manually editing the configuration in a text editor

If you want to, you can dump the configuration of the server, or the user properties, into a text file. You can then edit this file and save any changes you made, and then import the file back into the Access Server configuration database file or user properties database file. We don't recommend doing this because a typo means your configuration could be ruined. But if you want to, you can use this method. The format used when exporting and importing information in this way is the JSON format.

Dump server configuration to text file:

./confdba -s >config.txt

Import server configuration from text file back to the configuration database:

./confdba -lf config.txt

Dump user and group configuration to text file:

./confdba -us >config.txt

Import user and group configuration from text file back to the configuration database:

./confdba -ulf config.txt

Backing up the OpenVPN Access Server configuration

We recommend that you set up an automatic backup system that saves the entire configuration of your Access Server regularly, preferably via an automated task. If you follow the steps laid out here you'll end up with SQLite3 DB dump files that you can use to restore your server. If you are using another database backend then please see that database backend documentation for instructions on how to make backups on that database backend. The default is that all configuration is stored in SQLite3 database files.

If at any point the configuration becomes lost, then all the currently installed OpenVPN clients are unable to connect to the server. Even reinstalling the server with the same user names and passwords will then simply not have any effect. Every installation of OpenVPN Access Server comes with a unique private key and public key, which are used internally in the certificate management system built into the Access Server to generate unique client certificates. The certificates for one installation of OpenVPN Access Server are not compatible with that of another installation. So in the event of a server crash, if you have a backup of all of the configuration files, you can restore this and get your clients connected again without requiring them all to reinstall their clients or connection profiles.

It is possible to stop the OpenVPN Access Server service and then copy the 5 files to a safe location, and to then start the OpenVPN Access Server service again. But that adds an interruption you may not want. Therefore instead we recommend you use the steps described below, which let the service stay online while you make a backup.

With the commands below you can make a backup of all these files while the OpenVPN Access Server is live. That means you do not need to stop the Access Server service to make a backup file; it can continue running while a backup is made. This way you can easily create an automated task, like a cron job, to handle the backup task unattended. It goes without saying of course that if anyone gets a hold of these backup files, the security of your VPN system is compromised; it contains all the settings and certificates. So please do pay attention to where you store these backups and how you store them.

Use the commands below with root privileges to make a backup:

cd /usr/local/openvpn_as/
./bin/sqlite3 ./etc/db/config.db .dump > ./config.db.bak
./bin/sqlite3 ./etc/db/certs.db .dump > ./certs.db.bak
./bin/sqlite3 ./etc/db/userprop.db .dump > ./userprop.db.bak
./bin/sqlite3 ./etc/db/log.db .dump > ./log.db.bak
cp ./etc/as.conf ./as.conf.bak

The resulting 5 configuration files (config.db.bak, certs.db.bak, userprop.db, log.db.bak, and as.conf.bak) can be found in the /usr/local/openvpn_as/ directory now and contain everything unique about this OpenVPN Access Server installation. It's worth noting that with PAM authentication system the passwords are stored in the operating system and these are not backed up with these commands, while with local authentication mode they are stored in these backup files. And with LDAP and RADIUS the password are stored in those systems and thus are not involved.

Recovering a server with SQlite3 dump backup files

While making a backup can be done live, restoring a backup must never be done live. This can lead to a damaged configuration, after which you'll have to restore the backup again to fix that. So let's assume for the moment that you have just suffered an unfortunate problem with your server and you had to reinstall on the same or different hardware, or on a new setup entirely, and you now wish to restore the configuration backups you have so wisely created and have available. If you have backups created using the SQlite3 .dump command as is demonstrated in the backing up the OpenVPN Access Server configuration section, then you can use the following commands to restore a configuration to a freshly installed OpenVPN Access Server installation. Please note that if you follow these steps, the current configuration of the OpenVPN Access Server will be wiped out completely and will be replaced with the contents of the backup files instead. There is no way to "combine" a backup from one server with another production server. We are assuming in the commands below that the backup files are in the /usr/local/openvpn_as/ directory, but you can adjust the commands as necessary of course.

Use the commands below to wipe current configuration and restore the backup files you provide:

service openvpnas stop
cd /usr/local/openvpn_as/
rm ./etc/db/config.db
rm ./etc/db/certs.db
rm ./etc/db/userprop.db
rm ./etc/db/log.db
rm ./etc/as.conf
./bin/sqlite3 <./config.db.bak ./etc/db/config.db
./bin/sqlite3 <./certs.db.bak ./etc/db/certs.db
./bin/sqlite3 <./userprop.db.bak ./etc/db/userprop.db
./bin/sqlite3 <./log.db.bak ./etc/db/log.db
cp ./as.conf.bak ./etc/as.conf
service openvpnas start

This restores the configuration backup. There are a few caveats to note here:

If you restore a configuration backup to another server, it's possible that you had your system configured a specific way that doesn't work on the new server installation anymore. Perhaps the IP address was different on your old server, and perhaps you had chosen to set the Access Server to only listen to a very specific IP address. If that address is then not present on your new installation, the web interface won't respond because it's set to listen to an address or interface that doesn't exist. To resolve this check the section on how to reset the interface and port for the web services to listen on to default settings. Once you have access to the Admin UI again you can reconfigure it to whatever settings you wish. Alternatively you can use the command line tools to configure the web server settings manually.

And finally there is on more thing to check. The configuration database also contains the setting on how many TCP daemons and UDP daemons to launch. If this is set higher than the amount of available CPU cores, the Access Server program may become unstable. So if you have restored this configuration on a different server, and the amount of CPU cores is different from the server the configuration backup came from, you should adjust this as described on the Server Network Settings page in the Admin UI, or use our reset commands for the OpenVPN daemons here.

Change database backend to MySQL or Amazon RDS

OpenVPN Access Server does not have to use the SQLite3 database files it uses by default. It can also use a database backend such as an MySQL or MariaDB server, or Amazon RDS. You can for example keep the logging database in a MySQL database while storing configuration, certificates and user properties on local SQLite3 databases. Any combination of storing locally or remotely in a database backend is possible. It should be worth noting however that there is no caching happening on the Access Server side of things. This means that while the connection between the Access Server and the remote database backend is interrupted you cannot connect to the OpenVPN Access Server. If you are going to implement this we recommend that the database server is either running locally on the system that the Access Server itself is on (and maybe use database replication), or that you run the connection between the Access Server and the database server on a reliable internal network and not over a far reaching Internet connection.

Using a shared database for the certificates it is for example possible for multiple OpenVPN Access Server nodes to accept the same certificates for incoming client connections. This does however require that each server has a copy of the same server keys stored in the config.db file. But you should not have servers share the configuration database itself if you want them to have different settings like different global VPN subnets. You could for example place an initial copy of the config.db SQLite3 database file on a new Access Server node, and then set it up to use the local SQLite3 config database, but use the shared user properties database on the MySQL database backend, so that it can share those client certificates with other Access Servers. For authentication you could use local authentication and share the user properties database, or you could use for example LDAP or RADIUS as an external shared credentials and authentication system and keep user properties separate per server by sticking with local userprop.db SQLite3 database files for each server. Running multiple Access Server nodes has some interesting possibilities but also some pitfalls. In this page however we are concentrating on the procedure of converting the database files to an RDS cluster on Amazon. The procedure is pretty much the same for MySQL and MariaDB as well; it all uses MySQL compatible protocol.

You will need at minimum mysql-client package installed on your system, and in some cases libmysqlclient-dev as well.

In this example we've setup an RDS cluster on Amazon AWS and our connection address is auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com:3306. As usual for all our command line tools documentation we are assuming you are logged on as root user and are in the /usr/local/openvpn_as/scripts/ folder. We also always assume the default port 3306. If another port is used you can however configure it in the .my.cnf file below.

Open /etc/.my.cnf in the nano text editor:

nano /etc/.my.cnf

Add this text, and adjust the user name and password to the ones you've configured:

[client]
user=<MYSQL_USER_NAME>
password=<MYSQL_PASSWORD>
port=3306

Press ctrl+x, then press y, and then press enter, to save and exit the file.

Set file permissions so only root can access it:

chmod go-rwx /etc/.my.cnf

Add a symbolic link so the root user can use the mysql command line tool without entering credentials:

ln -s /etc/.my.cnf /root/.my.cnf

Next, connect to the RDS instance with MySQL command line tool:

mysql -h auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com

And use the MySQL command line prompt to create the databases:

mysql> create database as_certs;
Query OK, 1 row affected (0.01 sec)
mysql> create database as_config;
Query OK, 1 row affected (0.01 sec)
mysql> create database as_log;
Query OK, 1 row affected (0.01 sec)
mysql> create database as_userprop;
Query OK, 1 row affected (0.01 sec)

Optional: import the self-signed certificate stored in the Access Server file system into the configuration database if no valid SSL certificate exists yet in the configuration database and you want to use this self-signed certificate:

./sacli --import GetActiveWebCerts

Stop the Access Server service before converting the databases to the database backend:

service openvpnas stop

Convert the databases you want to convert using the commands below (you don't have to convert them all):

export SERVER=auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com
./dbcvt -t config -s sqlite:////usr/local/openvpn_as/etc/db/config.db -d mysql://$SERVER/as_config
./dbcvt -t certs -s sqlite:////usr/local/openvpn_as/etc/db/certs.db -d mysql://$SERVER/as_certs
./dbcvt -t user_prop -s sqlite:////usr/local/openvpn_as/etc/db/userprop.db -d mysql://$SERVER/as_userprop
./dbcvt -t log -s sqlite:////usr/local/openvpn_as/etc/db/log.db -d mysql://$SERVER/as_log
unset SERVER

if during this step you see an error message like:

error opening DB mysql://server/database: libmysq lclient.so.20: cannot open shared object file: No such file or directory

Then you may need to install the libmysqlclient-dev package on your system, and try again.

Modify the as.conf file to tell it where to look for each database (you don't have to move them all to the database backend):

nano /usr/local/openvpn_as/etc/as.conf

Look for the lines starting with config_db, user_prop_db, certs_db, and log_db. Adjust them accordingly:

config_db=mysql://auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com/as_config
user_prop_db=mysql://auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com/as_userprop
log_db=mysql://auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com/as_log
certs_db=mysql://auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com/as_certs

Press ctrl+x, then press y, and then press enter, to save and exit the file.

Finally, restart the OpenVPN Access Server:

service openvpnas start

The OpenVPN Access Server should now come back online and function with the configured database backend options instead. You can confirm by for example moving the SQLite3 database files you are no longer using out of the /etc/db folder to another location and restarting the Access Server service. If it comes back up fine then obviously it is not using those files anymore. If you are having problems getting the Access Server to start you can change your settings back or take a close look at the /var/log/openvpnas.log file to determine what is going on exactly. Usually any error messages are clearly visible there.

Wiping the configuration of the Access Server entirely

Obviously a big fat warning is in place here:
do not do this unless you are sure you wish to wipe all settings of your OpenVPN Access Server.

Now that that is out of the way, it's worth noting here that active license keys will remain in place on the server. The OpenVPN Access Server comes with an installation wizard that is used to set up the initial configuration, but it can also be used to wipe all configuration of the OpenVPN Access Server program, and start with a clean slate. It will ask you all the installation steps again, which you can either accept the default of or alter at will. We recommend choosing the defaults and then adjusting these settings later (unless you're setting up a failover server - then make sure to choose that this server will be a failover system). If you are asked for a license key you can just press enter. License keys can be added later if necessary.

To wipe all configuration settings, certificates, user properties:

ovpn-init --force

do not do this unless you are sure you wish to wipe all settings of your OpenVPN Access Server.