Configuration database management and backups
How the configuration is saved
Access Server by default stores configuration in SQLite database files. This has been the case since the inception of the program. It is also capable of storing the information in a MySQL-type database system like Amazon RDS, MySQL, and MariaDB. This is used primarily for the Access Server cluster solution. It is possible to modify the information stored in these databases via the admin web UI interface, sacli command line tool, confdba command line tool, and sqlite/mysql command line utilities. The log database can be queried on the admin web interface or with a separate logdba tool designed for pulling this information out of the database file. Changing settings should normally always be done either in the admin web UI or via the sacli command line tool. The confdba and sqlite/mysql programs should only be used in the event the Access Server has a problem starting up and the admin web UI and sacli command line tools are both unavailable.
In Access Server versions before release 2.6.1:
- Global server configuration: /usr/local/openvpn_as/etc/db/config.db
- Server and client certificates: /usr/local/openvpn_as/etc/db/certs.db
- User and group properties: /usr/local/openvpn_as/etc/db/userprop.db
- Log database: /usr/local/openvpn_as/etc/db/log.db
- Debug and low level settings: /usr/local/openvpn_as/etc/as.conf
These were added since Access Server 2.6.1:
- Local server node configuration: /usr/local/openvpn_as/etc/db/config_local.db
- Cluster configuration: /usr/local/openvpn_as/etc/db/cluster.db
- Cluster configuration: /usr/local/openvpn_as/etc/db/clusterdb.db
- Cluster notification system: /usr/local/openvpn_as/etc/db/notification.db
If you are using local authentication mode, the user password is stored in the user properties database as an sha256 hash. Migrating the Access Server configuration from one server to another would keep the credentials intact. In other authentication modes, the user credentials are not stored in Access Server, but in those external systems. This is important to note when you wish to migrate your configuration and users to another server. In such a case what can happen is that when you migrate the configuration to another server, you may 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 sacli command line utility can be used to configure almost every aspect of the Access Server configuration by altering values in the databases that Access Server uses to store its configuration. The sacli program can be used as long as the Access Server agent program can start. Even in situations where the web interface fails or the OpenVPN daemons fail, the server agent will still run. Sacli uses the internal logic of Access Server to implement changes, and this is the recommended method of changing the configuration of the Access Server via the command line.
The confdba program should be used only when the sacli program cannot be used, for example in the rare situation that the configuration is so damaged that Access Server cannot start. We advise against using confdba unless necessary.
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. In the examples below we show how to pull up the user properties and the server configuration and display it.
To see what the current server configuration is:
To see what all the user and group properties are:
To see the properties of a specific user or group:
./sacli --pfilt <USER_OR_GROUP> UserPropGet
More commands to view and change configuration is available in documentation on our website.
Backing up the OpenVPN Access Server configuration
The backup steps below only apply to the default situation where Access Server stores its configuration in SQLite format. If you changed your database backend, please consult documentation for that database backend to learn how to take a proper backup. The resulting backup files can be restored on another Access Server, almost always even on a higher version of Access Server, as we try to do our best to maintain backwards compatibility.
If at any point the configuration becomes completely lost, then all the currently installed OpenVPN clients will be unable to connect to this server. To be clear, there is unique information stored in the certificates database that cannot be recreated. Each fresh installation of Access Server has its own unique certificates, and if you lose this information and have no backup, then your only recourse is to do a full reinstall of the Access Server and the VPN clients.
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:
which apt > /dev/null 2>&1 && apt -y install sqlite3 which yum > /dev/null 2>&1 && yum -y install sqlite cd /usr/local/openvpn_as/etc/db [ -e config.db ]&&sqlite3 config.db .dump>../../config.db.bak [ -e certs.db ]&&sqlite3 certs.db .dump>../../certs.db.bak [ -e userprop.db ]&&sqlite3 userprop.db .dump>../../userprop.db.bak [ -e log.db ]&&sqlite3 log.db .dump>../../log.db.bak [ -e config_local.db ]&&sqlite3 config_local.db .dump>../../config_local.db.bak [ -e cluster.db ]&&sqlite3 cluster.db .dump>../../cluster.db.bak [ -e clusterdb.db ]&&sqlite3 clusterdb.db .dump>../../clusterdb.db.bak [ -e notification.db ]&&sqlite3 notification.db .dump>../../notification.db.bak cp ../as.conf ../../as.conf.bak
The resulting backup files ending in .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 will be stored in these backup files and can be restored on another installation. With LDAP and RADIUS the password are stored in those external systems and thus there is no need to backup external LDAP/RADIUS credentials on the Access Server itself.
Recovering a server with SQlite3 dump backup files
While creating a backup set can be done while the Access Server is up and running, restoring a backup to a new installation of Access Server must be done with the Access Server service turned off. We assume you have an Access Server installation that you wish to restore a backup set to. Our instructions on restoring a backup include steps to stop the Access Server service, and then restore the backup set, and then start the Access Server service up again.
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 supported way to combine a backup set of information from one server with another production server.
Note: 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 with root privileges to restore the backup:
service openvpnas stop which apt > /dev/null 2>&1 && apt -y install sqlite3 which yum > /dev/null 2>&1 && yum -y install sqlite cd /usr/local/openvpn_as/etc/db [ -e ../../config.db.bak ]&&rm config.db;sqlite3<../../config.db.bak config.db [ -e ../../certs.db.bak ]&&rm certs.db;sqlite3 <../../certs.db.bak certs.db [ -e ../../userprop.db.bak ]&&rm userprop.db;sqlite3 <../../userprop.db.bak userprop.db [ -e ../../log.db.bak ]&&rm log.db;sqlite3 <../../log.db.bak log.db [ -e ../../config_local.db.bak ]&&rm config_local.db;sqlite3 <../../config_local.db.bak config_local.db [ -e ../../cluster.db.bak ]&&rm cluster.db;sqlite3 <../../cluster.db.bak cluster.db [ -e ../../clusterdb.db.bak ]&&rm clusterdb.db;sqlite3 <../../clusterdb.db.bak clusterdb.db [ -e ../../notification.db.bak ]&&rm notification.db;sqlite3 <../../notification.db.bak notification.db [ -e ../../as.conf.bak ]&&cp ../../as.conf.bak ../as.conf service openvpnas start
This restores the configuration backup. There are some things that could cause Access Server to fail to load properly:
- The configuration could have instructions to listen to a specific network interface that doesn’t exist on the new system.
- The configuration could have instructions to run more OpenVPN daemons than there are CPU cores for on the new system.
If the configuration is set to listen to a specific IP, the web interface may not 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.
The configuration database also contains some settings 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. It may run if the amount is set too high, but it may be unstable if set incorrectly.
Recovering damaged database configuration files
We have a special troubleshooting page for the situation where you are using the standard SQLite3 database files for the Access Server configuration on a single node, and the configuration databases have become damaged somehow. The troubleshooting page is here: repairing configuration database SQLite3 files.
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 may not be able to 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.
The Access Server has in the Admin UI under Tools a tool for database conversion to MySQL-type databases. This converts them all. The required libraries to make the connection are installed automatically when Access Server is installed from our software repository.
Manually change database backend to MySQL/RDS
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:
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
If your username or password contains some strange characters, make sure to add quotes around it.
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)
Make sure the web certificates are stored in the certificates database:
./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):
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 warning is in order here:
do not do this unless you are sure you wish to wipe all settings of your OpenVPN Access Server.
It’s worth noting here that active fixed software license activation keys will remain in place on the server. Since these are single-activation, unlike subscriptions, it may be important for you to know that this action of wiping configuration does not wipe those activated keys.
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 an activation key, you can just press enter and add it later in the web interface if you wish.
To wipe all configuration settings, certificates, and user/group properties:
do not do this unless you are sure you wish to wipe all settings of your OpenVPN Access Server.