Configuration Database Management and Backups

How Access Server configuration is saved

Access Server can store configuration in SQLite database files or a MySQL-type database.

By default, Access Server stores configuration in SQLite database files.

Access Server can also store configuration in MySQL-type database systems such as Amazon RDS, MySQL, and MariaDB. If you set up a cluster of Access Servers, all of your Access Servers will connect to one database system to share configuration. You may even set this up with a cluster of database servers to create a truly fault-tolerant system. Refer to Setting up an OpenVPN Access Server cluster for details.

It is possible to modify the information stored in these databases with the Admin Web UI, using the sacli command-line tool, the confdba command-line tool, or sqlite/mysql command-line utilities.

The default locations of the configuration files are as follows, depending on your version of Access Server.

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

You can query the log database in the Admin Web UI or a separate logdba tool designed to pull this information out of the database file. We recommend using the Admin Web UI or sacli command-line tool for changing settings. It would be best if you only use the confdba and sqlite/mysql in the event the Access Server has a problem starting up, and both the Admin Web UI and sacli command-line tool are unavailable.

How to view the current server configuration

The following commands use the sacli command line utility. They must be executed as root user in the /usr/local/openvpn_as/scripts/ directory.

List the current server configuration:

./sacli ConfigQuery

List the user and group properties:

./sacli UserPropGet

List the properties of a specific user or group:

./sacli --pfilt <USER_OR_GROUP> UserPropGet

With sacli, you can configure most of the Access Server configuration by altering values in the databases. You can use the sacli tool as long as the Access Server agent program can start. The server agent should still run if you encounter an issue where your web interface fails, or the OpenVPN daemons fail. The sacli tool uses the internal logic of Access Server to implement changes, the recommended method of changing Access Server’s configuration via the command line.

Access Server also has the confdba and logdba tools:

Confdba is a backup tool for when you can’t use the sacli tool, such as rare situations where a broken configuration keeps Access Server from starting. We advise against using confdba unless necessary.

Logdba allows you to query and wipe the log database file, which keeps track of when users log in, how long they are online, any error messages reported when users try to connect, and how much data they use. Refer to Access Server command-line interface tools for more details and additional links to command-line actions.

Configuration for authentication modes

Depending on your authentication mode, the configuration is stored differently.

Local

For local authentication mode, Access Server by default stores user and group properties in the /usr/local/openvpn_as/etc/db/userprop.db file.

For information about how Access Server stores user passwords, refer to “Local authentication commands” on Authentication options and command line configuration.

LDAP, RADIUS, or SAML

For LDAP, RADIUS, or SAML authentication modes, user credentials aren’t stored in Access Server but in the external systems.

PAM

For PAM authentication mode, Access Server stores the passwords in the operating system. When creating backups of the configuration files, be aware that the commands below won’t back up these passwords. If you migrate your server to a new server, you must set user passwords again for PAM authentication.

How to back up Access Server configuration

The following commands apply to the Access Server configuration stored in SQLite files. If you are using a different database backend, these are not applicable.

Use the commands below to backup all configuration files while Access Server is live. You don’t need to stop the Access Server service to make backup files; it can continue running.

We recommend creating an automated task, like a cron job, to handle the backup task unattended. Important security note: If anyone gets a hold of these backup files, the security of your VPN system is compromised; the backup files contain all the settings and certificates. Store the backups securely and in a protected location.

Run these commands with root privileges to make a backup of Access Server configuration files:

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

After running these commands, the backup files, ending in .bak, can be found in the /usr/local/openvpn_as/ directory. The files contain everything unique about your Access Server installation.

You can restore the resulting backup files to another Access Server, including a higher version of Access Server, as we try to do our best to maintain backward compatibility.

If the configuration becomes completely lost at any point, then all currently installed OpenVPN clients will be unable to connect to this server. Unique information stored in the certificates database cannot be recreated. Each installation of Access Server has unique certificates, and if you lose this information without a backup, you must do a complete reinstall of Access Server and the VPN clients.

Recovering a server with SQlite3 dump backup files

While creating backups can be done with Access Server 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, restore the backup set, and start the Access Server service again.

Note that if you follow these steps, the current configuration of Access Server is wiped out entirely and replaced with the contents of the backup files. You cannot combine a backup set of information from one server with another production server.

We assume that the backup files are located in the /usr/local/openvpn_as/ directory for these commands. Adjust the commands as necessary for the location of your files.

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 -f config.db;sqlite3<../../config.db.bak config.db)
[ -e ../../certs.db.bak ]&&(rm -f certs.db;sqlite3 <../../certs.db.bak certs.db)
[ -e ../../userprop.db.bak ]&&(rm -f userprop.db;sqlite3 <../../userprop.db.bak userprop.db)
[ -e ../../log.db.bak ]&&(rm -f log.db;sqlite3 <../../log.db.bak log.db)
[ -e ../../config_local.db.bak ]&&(rm -f config_local.db;sqlite3 <../../config_local.db.bak config_local.db)
[ -e ../../cluster.db.bak ]&&(rm -f cluster.db;sqlite3 <../../cluster.db.bak cluster.db)
[ -e ../../clusterdb.db.bak ]&&(rm -f clusterdb.db;sqlite3 <../../clusterdb.db.bak clusterdb.db)
[ -e ../../notification.db.bak ]&&(rm -f notification.db;sqlite3 <../../notification.db.bak notification.db)
[ -e ../../as.conf.bak ]&&cp ../../as.conf.bak ../as.conf
chmod 0600 /usr/local/openvpn_as/etc/db/*.db
chmod 0600 /usr/local/openvpn_as/etc/as.conf
service openvpnas start

These commands restore the configuration backup. Some things 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, refer to Reset the interface and port for the web services to listen on to default settings. Once you have access to the Admin Web UI again, you can reconfigure your settings. Alternatively, you can use the command-line tools to configure the web server settings manually.

The configuration database also contains settings on how many TCP daemons and UDP daemons to launch. If this is set higher than the amount of available CPU cores, Access Server 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, adjust this on the Network Settings page in the Admin Web 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 Access Server configuration on a single node, and the configuration databases have become damaged. Refer to repairing configuration database SQLite3 files.

Manually edit Access Server configuration using ConfigReplace

Access Server 2.10.1 and newer supports a command, ConfigReplace, which allows you to upload configuration changes in one file, and Access Server imports those changes to the correct configuration files. The process is as follows:

1. Download the configuration to a single file.
2. Edit that file.
3. Replace the configuration with changes from the file.
4. Access Server updates the appropriate configuration files.

Run the following commands from the /usr/local/openvpn_as/scripts/ directory with root privileges.

1. Download the configuration to a single file (where config.txt is the name of the file):
   sacli ConfigQuery > config.txt
2. Edit the file, config.txt, with your changes and save it. For example, you might change auth.module.type to pam or vpn.client.routing.reroute_gw to false.
3. Replace the configuration:
   sacli --value_file=config.txt ConfigReplace
sacli start
4. Verify your configuration changes.

Alternatively, if you want to apply the configuration changes to a specific profile, use these commands for the third step where you replace the configuration (where new_profile is the name of the configuration profile you want changed):

sacli --value_file=config.txt --prof=new_profile ConfigReplace
sacli start

Change database backend to MySQL or Amazon RDS

You can configure Access Server to use a MySQL-type database backend such as MySQL, MariaDB, or Amazon RDS.

To convert to a MySQL-type server:

1. Sign in to your Admin Web UI.
2. Click Tools > DB Convert.
3. Enter the connection information for your database server into the appropriate fields and click Convert DB.

This tool converts all SQLite database files to MySQL and stores them on the database server. The required libraries to make the connection are installed automatically when Access Server is installed from our software repository.

Note: No caching happens on the Access Server side of things. This means that if the connection between Access Server and the remote database backend is interrupted, you may not be able to connect to Access Server. If you implement this, we recommend that the database server runs locally on the same system as Access Server (and maybe use database replication), or run the connection between Access Server and the database server on a reliable internal network and not over a far-reaching internet connection.

Access Server database compatibility

We've tested and confirmed that the latest Access Server version works on these operating systems with the following relational database management system (RDBMS) versions:

Note: For MariaDB: we recommend using MariaDB 10.5.8 or newer. A known issue exists in MariaDB 10.4.3, due to a bug in the MariaDB code that causes connectivity issues. If you plan to use a version of MariaDB server that is newer than available in repositories on the host where Access Server is installed, you may need to update the MariaDB connector. For more detail refer to the official MariaDB connector documentation.

Manually change database backend to MySQL/RDS

The steps in this section provide manual instructions for converting database files. You may want to store some or all configuration files in a MySQL-type database. Some use case examples for setting this up include:

• Storing the logging database in a MySQL database but keeping the other configuration, certificates, and user properties in local SQLite3 files.
• Storing configuration files in a cluster database setup to protect against data loss.
• Any combination of storing locally or remotely.

In this example, we've set up an RDS cluster on Amazon AWS, and our connection address is auroratest-cluster.cluster-ctqs9e0kxora.us-east-1.rds.amazonaws.com:3306. For the following command-line tools, log on as root user and run them in the /usr/local/openvpn_as/scripts/ directory. Our example uses the default port 3306. If another port is used, ensure you 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 username and password to the ones you've configured:

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

If your username or password contains strange characters, add quotes around them.

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

Connect to the RDS instance with the mysql command line tool:

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

Use the 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

You may need to install the libmysqlclient-dev package on your system if you encounter an error message similar to this:

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

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 Access Server service:

service openvpnas start

Access Server should come back online and function with the configured database backend options. You can test by moving the SQLite3 database files your Access Server no longer uses out of the /etc/db folder to another location and restarting the Access Server service. It should come back up without issue. If you are having problems getting 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 visible there.

Set up SSL connection to MySQL

With Access Server 2.9.0 and newer, you can make an encrypted connection to MySQL or AWS RDS servers. You can configure one of three available modes for your database connection:

1. DISABLED: No SSL
2. PREFERRED: Create an SSL connection, but fallback to insecure if SSL is unsupported or can’t be established — this is the default behavior.
3. REQUIRED: Database connection is dropped if the certificate check fails.

If SSL mode isn’t set, Access Server will use ‘PREFERRED’ by default. Refer to the section below for your database server.

Configure MySQL SSL connection to self-hosted MySQL or MariaDB server

You must generate your own client and server certificates on the MySQL server side to use SSL mode. Depending on the Linux OS of your database server, the server and client certificate generations can be included in the installation script. Refer to documentation from the OS vendor of MySQL server package maintainer for more details to create these certificates.

Once you have the CA bundle, client certificate, and client private key files, we recommend naming the files for their use. In our example, we refer to them as mysql-as-CAbundle.crt, mysql-as-client.crt, and mysql-as-client.key.

Upload your client certificate and CA bundle files to Access Server in this directory: /etc/ssl/certs/.

Upload your client private key to Access Server in this directory: /etc/ssl/private/.

Grant read rights to the file for Access Server:

chown openvpn_as:openvpn_as /etc/ssl/certs/mysql-as-CAbundle.crt
chown openvpn_as:openvpn_as /etc/ssl/certs/mysql-as-client.crt
chown openvpn_as:openvpn_as /etc/ssl/private/mysql-as-client.key
chmod 644 /etc/ssl/certs/mysql-as-CAbundle.crt
chmod 644 /etc/ssl/certs/mysql-as-client.crt
chmod 640 /etc/ssl/private/mysql-as-client.key

You can configure Access Server to use these files for establishing a connection to the database server and define SSL mode. Run these commands with root privileges in the /usr/local/openvpn_as/scripts/ directory and:

./sacli --key "mysql.ssl_ca_cert" --value '/etc/ssl/certs/mysql-as-CAbundle.crt' ConfigPut
./sacli --key "mysql.ssl_auth_cert" --value '/etc/ssl/certs/mysql-as-client.crt' ConfigPut
./sacli --key "mysql.ssl_auth_key" --value '/etc/ssl/private/mysql-as-client.key' ConfigPut
./sacli --key "mysql.ssl_mode" --value "PREFERRED" ConfigPut
service openvpnas restart

Note: Restarting the openvpnas service also forces all VPN connections to reconnect.

Configure MySQL SSL connection to AWS RDS

You must download the Amazon RDS certificate bundle file for your AWS region: Using SSL/TLS to encrypt a connection to a DB instance. AWS doesn’t provide client certificate or private key files, but uploading only the certificate bundle file is enough to establish a secure connection to an RDS MySQL database instance.

Note: AWS rotates certificate bundle files. Refer to Amazon’s documentation for details: Rotating your SSL/TLS certificate.

Upload the AWS RDS CA bundle file to Access Server in this directory: /etc/ssl/certs/.

Grant Access Server read rights to the file — using the correct .pem file name for the AWS bundle you uploaded:

chown openvpn_as:openvpn_as /etc/ssl/certs/us-east-1-bundle.pem
chmod 644 /etc/ssl/certs/us-east-1-bundle.pem

You can configure Access Server to use these files for establishing a connection to the database server and define SSL mode. Run these commands with root privileges in the /usr/local/openvpn_as/scripts/ directory and:

./sacli --key "mysql.ssl_ca_cert" --value '/etc/ssl/certs/us-east-1-bundle.pem' ConfigPut
./sacli --key "mysql.ssl_mode" --value "PREFERRED" ConfigPut
service openvpnas restart

Make sure to replace ‘us-east-1-bundle.pem’ with the name of the AWS RDS CA bundle for your region.

Note: Restarting the openvpnas service also forces all VPN connections to reconnect.

Wipe the configuration of the Access Server entirely

Warning: This command permanently deletes all of your Access Server settings.

Wipe all configuration settings, certificates, and user/group properties:

ovpn-init --force

This command wipes all settings. Do not run it unless you are sure this is what you want.

Active fixed software license activation keys 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 doesn’t wipe activated keys.

Access Server comes with an installation wizard to set up the initial configuration. After running the above command, you start with a clean slate for Access Server. The installation wizard runs again upon connecting to your server’s terminal. We recommend accepting the default settings, then adjusting those later using the Admin Web UI, unless you're setting up a failover server — then make sure to choose that this server will be a failover system. When asked for an activation key, press enter and you can add it later in the Admin Web UI.