Complete guide on how to set up FreeRADIUS 3.X with MySQL backend database

EAP-TTLS and MySQL TLS database communication

802.1X Authentication Authorization Accounting

Alpine Linux lightweight Docker container

Today we are going to explain how to set up a FreeRADIUS 3 server for Authentication, Authorization and Accounting (AAA) along with a MySQL database for credentials storage and accounting logs accessed only through encrypted TLS connections. We will show how to set up FreeRADIUS with the secure EAP-TTLS (Tunneled TLS) communication.

What is FreeRADIUS

FreeRADIUS is the most popular and most widely deployed open source RADIUS server. It supports all the most common client authentication protocols and its fast and scalable. It supports also Two Factor Authentication

FreeRADIUS can be used for WiFi client authentication as well as with any other system that require client authentication as for example OpenVPN server.

Each user will have a credential in the form <username>@<domain> and a respective password.

For this tutorial let’s assume we have the acme.com domain and a [email protected] user.

How does RADIUS works?

1. A client will connect to a Network Access Server (NAS) first. This could be a router providing Wireless access. The client will not have any connectivity, the router is allowing only RADIUS traffic.
2. The router acting as a NAS will receive a Access Request message from the client and will forward it to the RADIUS server.
3. The RADIUS Server will check the credentials and send back a response that could be Access-Accept or Access-Reject or a Access-Challenge.
4. The NAS will forward the response to the client, and if the authentication succeed, it will then open the communication to the internal network. Ref: How does RADIUS work? by Cisco

EAP-TTLS and PAP

We will setup FreeRADIUS with EAP-TTLS. This means each client connecting to the NAS will first establish a secure TLS tunnel. This is called First Phase. In the Second Phase the client will send his credentials in cleartext* with Password Authentication Protocol (PAP) but since those are sent over the TLS channel, none can read it and none can eavesdrop which user is trying to authenticate to the NAS.

*The password is not actually sent in cleartext but it is rather hashed with the key shared between the NAS and the RADIUS Server. Still is not considered secure but since it is sent over an encrypted channel, that makes the mitigation.

MySQL initialization

MySQL is used to keep track of clients login attempts, connection times and other accounting features and it will store the user credentials.

First of all you need a MySQL Database running, then you have to create the tables needed for FreeRADIUS.

Here is the FreeRADIUS SQL schema:

#
# Table structure for table 'radacct'
#

CREATE TABLE radacct (
  radacctid bigint(21) NOT NULL auto_increment,
  acctsessionid varchar(64) NOT NULL default '',
  acctuniqueid varchar(32) NOT NULL default '',
  username varchar(64) NOT NULL default '',
  realm varchar(64) default '',
  nasipaddress varchar(15) NOT NULL default '',
  nasportid varchar(15) default NULL,
  nasporttype varchar(32) default NULL,
  acctstarttime datetime NULL default NULL,
  acctupdatetime datetime NULL default NULL,
  acctstoptime datetime NULL default NULL,
  acctinterval int(12) default NULL,
  acctsessiontime int(12) unsigned default NULL,
  acctauthentic varchar(32) default NULL,
  connectinfo_start varchar(50) default NULL,
  connectinfo_stop varchar(50) default NULL,
  acctinputoctets bigint(20) default NULL,
  acctoutputoctets bigint(20) default NULL,
  calledstationid varchar(50) NOT NULL default '',
  callingstationid varchar(50) NOT NULL default '',
  acctterminatecause varchar(32) NOT NULL default '',
  servicetype varchar(32) default NULL,
  framedprotocol varchar(32) default NULL,
  framedipaddress varchar(15) NOT NULL default '',
  PRIMARY KEY (radacctid),
  UNIQUE KEY acctuniqueid (acctuniqueid),
  KEY username (username),
  KEY framedipaddress (framedipaddress),
  KEY acctsessionid (acctsessionid),
  KEY acctsessiontime (acctsessiontime),
  KEY acctstarttime (acctstarttime),
  KEY acctinterval (acctinterval),
  KEY acctstoptime (acctstoptime),
  KEY nasipaddress (nasipaddress)
) ENGINE = INNODB;

#
# Table structure for table 'radcheck'
#

CREATE TABLE radcheck (
  id int(11) unsigned NOT NULL auto_increment,
  username varchar(64) NOT NULL default '',
  attribute varchar(64)  NOT NULL default '',
  op char(2) NOT NULL DEFAULT '==',
  value varchar(253) NOT NULL default '',
  PRIMARY KEY  (id),
  KEY username (username(32))
);

#
# Table structure for table 'radgroupcheck'
#

CREATE TABLE radgroupcheck (
  id int(11) unsigned NOT NULL auto_increment,
  groupname varchar(64) NOT NULL default '',
  attribute varchar(64)  NOT NULL default '',
  op char(2) NOT NULL DEFAULT '==',
  value varchar(253)  NOT NULL default '',
  PRIMARY KEY  (id),
  KEY groupname (groupname(32))
);

#
# Table structure for table 'radgroupreply'
#

CREATE TABLE radgroupreply (
  id int(11) unsigned NOT NULL auto_increment,
  groupname varchar(64) NOT NULL default '',
  attribute varchar(64)  NOT NULL default '',
  op char(2) NOT NULL DEFAULT '=',
  value varchar(253)  NOT NULL default '',
  PRIMARY KEY  (id),
  KEY groupname (groupname(32))
);

#
# Table structure for table 'radreply'
#

CREATE TABLE radreply (
  id int(11) unsigned NOT NULL auto_increment,
  username varchar(64) NOT NULL default '',
  attribute varchar(64) NOT NULL default '',
  op char(2) NOT NULL DEFAULT '=',
  value varchar(253) NOT NULL default '',
  PRIMARY KEY  (id),
  KEY username (username(32))
);


#
# Table structure for table 'radusergroup'
#

CREATE TABLE radusergroup (
  username varchar(64) NOT NULL default '',
  groupname varchar(64) NOT NULL default '',
  priority int(11) NOT NULL default '1',
  KEY username (username(32))
);

#
# Table structure for table 'radpostauth'
#
CREATE TABLE radpostauth (
  id int(11) NOT NULL auto_increment,
  username varchar(64) NOT NULL default '',
  pass varchar(64) NOT NULL default '',
  reply varchar(32) NOT NULL default '',
  authdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (id)
) ENGINE = INNODB;

#
# Table structure for table 'nas'
#
CREATE TABLE nas (
  id int(10) NOT NULL auto_increment,
  nasname varchar(128) NOT NULL,
  shortname varchar(32),
  type varchar(30) DEFAULT 'other',
  ports int(5),
  secret varchar(60) DEFAULT 'secret' NOT NULL,
  server varchar(64),
  community varchar(50),
  description varchar(200) DEFAULT 'RADIUS Client',
  PRIMARY KEY (id),
  KEY nasname (nasname)
);

To execute just run:

mysql -u root -prootpass radius < schema.sql

assuming you already created the radius database.

And then we are going to create a user “radius” with the right privileges.

Here we create the user radius with password donotuseme (if you want to restrict connections from a specific IP address only, you specify the IP instead of *):

#
#  Create default administrator for RADIUS
#
CREATE USER 'radius'@'*';
SET PASSWORD FOR 'radius'@'*' = 'donotuseme';

ALTER USER 'radius'@'*' IDENTIFIED WITH mysql_native_password BY 'donotuseme';

# The server can read any table in SQL
GRANT SELECT ON radius.* TO 'radius'@'*';

# The server can write to the accounting and post-auth logging table.
#
#  i.e.
GRANT ALL on radius.radacct TO 'radius'@'*';
GRANT ALL on radius.radpostauth TO 'radius'@'*';


#
# Insert a valid FreeRADIUS User credential
#
INSERT into radius.radcheck (username,attribute,op,value) values("[email protected]", "SSHA2-256-Password", ":=", "ag6J2U52nmn7gkQM2h4eXEYQnHON7W9DyyGKxUSiAsFzYWx0c2FsdHNhbHQ=");

In the second part of the SQL code we are going to insert a valid RADIUS user credential for a valid REALM (@acme.com we will get to this later) and generate a SHA256 salted password compatible with FreeRADIUS.

To do so I created a Python script here. Just change “test” to the password you want to hash, and use a random salt. (I set the salt size limit to 12 charaters)

For example the password ‘test’ becomes ag6J2U52nmn7gkQM2h4eXEYQnHON7W9DyyGKx- USiAsFzYWx0c2FsdHNhbHQ= and we are going to save it as SSHA2-256-Password in the database.

MySQL TLS connection setup

Now we are setting up MySQL to encrypt each communication with TLS 1.2 and rejecting every cleartext connection.

This is my /etc/my.cnf:

# This group is read both both by the client and the server
# use it for options that affect everything
[client-server]

# This group is read by the server
[mysqld]
ssl-cipher=TLSv1.2
ssl-ca=/mysql-certs/ca.crt
ssl-cert=/mysql-certs/mysql.acme.com.crt
ssl-key=/mysql-certs/mysql.acme.com.pem
require_secure_transport=ON

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

datadir=/var/lib/mysql
bind-address=0.0.0.0
log-error=/var/log/mysql/error.log
skip-log-bin
log-output=FILE
general-log=1
general_log_file=/var/log/mysql/general.log

port=3306
user=mysql
socket=/run/mysqld/mysqld.sock
pid-file=/run/mysqld/mysqld.pid
default_authentication_plugin=mysql_native_password

[client]
ssl-cipher=TLSv1.2
ssl-ca=/mysql-certs/ca.crt

Off course make sure to copy your certificates under /mysql-certs.

The require_secure_transport=ON directive makes sure to reject every unencrypted connection request.

And we are done with the MySQL part. If you want I wrote a Docker Container with this complete setup. At the end of the tutorial the link.

FreeRadius 3 configuration

First install the FreeRADIUS packages required: (alpine linux)

apk add freeradius openssl freeradius-mysql freeradius-eap

then the files we are going to modify are:

• /etc/raddb/clients.conf
• /etc/raddb/sites-available/default
• /etc/raddb/sites-available/inner-tunnel
• /etc/raddb/mods-available/sql
• /etc/raddb/mods-available/eap

NOTE: It’s super easy to break the server with wrong configuration, so each time you modify something, make sure the server is starting correctly.

To run radius in debug mode: radiusd -X

/etc/raddb/client.conf configuration file

Here you need to specify the clients allowed to send requests to the RADIUS server. Most of the times we want to configure a router as NAS or Network Access Server so that WiFi clients connecting will communicate to the RADIUS server via the router.

Add these lines at the end of the file:

• RADIUS_SECRET is a shared password that will be set also on the router.
• ipaddr is the IP subnet of the router.

  client router_nas {
        ipaddr = 192.168.10.0/24
        secret = RADIUS_SECRET
  }
  

/etc/raddb/default configuration file

Under the authorize {} section:

Uncomment these lines

eap {
               ok = return
               updated = return
    }

and

sql

Under authenticate {} section make sure to uncomment:

eap

Under the accounting {} section uncomment the sql line:

sql

Under the session {} uncomment the sql line.

Under the post-auth {} section uncomment the sql line.

Under the post-auth {} section, under the Post-Auth-Type REJECT {} uncomment the sql line.

/etc/raddb/sites-available/inner-tunnel

This is a virtual server that handles only inner tunnel requests for EAP-TTLS and PEAP types.

Under authorize {} make sure both sql and eap {..} are uncommented.

Under authenticate {} make sure eap is uncommented.

Under session {} make sure sql is uncommented.

Under post-auth {} make sure sql is uncommented as well as in the Post-Auth-Type REJECT {} subsection.

/etc/raddb/mods-available/sql

This is the configuration file for the SQL module.

sql {
        
        driver = "rlm_sql_mysql"

        mysql {
               # If any of the files below are set, TLS encryption is enabled
               tls {
                       ca_file = "/etc/raddb/ca.crt"
                       # If you want to use client certificates for MySQL TLS
                       certificate_file = "/etc/raddb/rad_client.crt"
                       private_key_file = "/etc/raddb/rad_client.pem"
               }

               # If yes, (or auto and libmysqlclient reports warnings are
               # available), will retrieve and log additional warnings from
               # the server if an error has occured. Defaults to 'auto'
               warnings = yes
        }
        dialect = "mysql"

        # Connection info:
        server = "RADIUS_SERVER_IP"
        port = 3306
        login = "radius"
        password = "RADIUS_USR_PASSWORD"
    
        # Database table configuration for everything except Oracle
        radius_db = "radius"
        
        read_clients = "yes"
        sql_user_name = "%{User-Name}"

        
        # If you want both stop and start records logged to the
        # same SQL table, leave this as is.  If you want them in
        # different tables, put the start table in acct_table1
        # and stop table in acct_table2
        acct_table1 = "radacct"
        acct_table2 = "radacct"

        # Allow for storing data after authentication
        postauth_table = "radpostauth"

        # Tables containing 'check' items
        authcheck_table = "radcheck"
        groupcheck_table = "radgroupcheck"

        # Tables containing 'reply' items
        authreply_table = "radreply"
        groupreply_table = "radgroupreply"

        # Table to keep group info
        usergroup_table = "radusergroup"

        # Remove stale session if checkrad does not see a double login
        delete_stale_sessions = yes

        pool {
                start = ${thread[pool].start_servers}

                #  Minimum number of connections to keep open
                min = ${thread[pool].min_spare_servers}

                #  Maximum number of connections
                max = ${thread[pool].max_servers}

                #  Spare connections to be left idle
                spare = ${thread[pool].max_spare_servers}

                #  Number of uses before the connection is closed
                uses = 0

                #  The number of seconds to wait after the server tries
                #  to open a connection, and fails.  During this time,
                #  no new connections will be opened.
                retry_delay = 30

                # The lifetime (in seconds) of the connection
                lifetime = 0

                #  idle timeout (in seconds).  A connection which is
                #  unused for this length of time will be closed.
                idle_timeout = 60
        }

        # Table to keep radius client info
        client_table = "nas"

        # This entry should be used for the default instance (sql {})
        # of the SQL module.
        group_attribute = "SQL-Group"

        # Read database-specific queries
        $INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf
}

To enable the SQL module make sure /etc/raddb/mods-enabled/sql is existing, otherwise:

$ ln -s /etc/raddb/mods-available/sql /etc/raddb/mods-enabled/sql

/etc/raddb/mods-available/eap

Among the other options, the ones that need to be changed are:

eap {
    ...

    default_eap_type = ttls

    ...

    tls-config tls-common {
        private_key_file = /etc/raddb/radius.acme.com.key
        certificate_file = /etc/raddb/radius.acme.com.crt
        ca_file = /etc/raddb/ca.crt

        #  For DH cipher suites to work, you have to
        #  run OpenSSL to create the DH file first:
        #       openssl dhparam -out certs/dh 2048
        dh_file = /etc/raddb/dh.pem
        
        random_file = /dev/urandom

        cipher_list = "HIGH"
        cipher_server_preference = yes
        tls_min_version = "1.2"
        ecdh_curve = "secp384r1"
    }

    ...

    ttls {
        # make sure these lines are the same

        tls = tls-common

        virtual_server = "inner-tunnel"

    }

}

Off course copy the respective certificates to /etc/raddb/

To enable the EAP module make sure /etc/raddb/mods-enabled/eap is existing, otherwise:

$ ln -s /etc/raddb/mods-available/eap /etc/raddb/mods-enabled/eap

NOTE Assuming you have the current latest OPENSSL version installed. If FreeRADIUS is giving some security error related to openssl you need to add allow_vulnerable_openssl = "CVE-2016-6309" to the /etc/raddb/radiusd.conf under the security {} section.

We have now finished with the settings. As said above, after each file configuration change, restart the FreeRADIUS server and check that everything is running without errors.

Testing

You can run FreeRADIUS in debug mode and start to test it.

$ radiusd -X

You can test the basic PAP authentication (without EAP-TTLS) with:

$ radtest test@acme.com test 192.168.5.12 0 testing123 -x

Assuming:

• 192.168.5.12 is the FreeRADIUS IP address
• testing123 is the shared password
• [email protected] is a user stored in the database

Now you can try to configure the Router to use EAP for WiFi client authentication. Always look at the logs in the FreeRADIUS server and try to understand what is going on.

Testing EAP-TTLS

wpa-supplicant is supplying the eapol_test program to test RADIUS EAP.

Create a file, eapol-tls.conf for example:

network={
     ssid="SSID_OF_THE_WIFI_NETWORK"
     key_mgmt=WPA-EAP
     eap=TTLS
     identity="[email protected]"
     anonymous_identity="anonymous"
     password="INSERT_THE_USER_PASSWORD"
     phase2="auth=PAP"

     eapol_flags=3
}

And run it with:

$ eapol_test -a192.168.5.12 -p1812 -stest -ceapol-tls.conf -r0 -stesting123

Where for example:

• 192.168.5.12 is the FreeRADIUS server IP address
• 1812 is the default port
• test is the password
• eapol-tls.conf is the config file for the test
• testing123 is the shared key

If everything has been set up correctly you should be able to see Access-Accept.

Congratulations! Now you can continue setting up the Router with your new FreeRADIUS server!

GitHub Repository

Here is the link to the Github freeRADIUS + MySQL repository.

Please feel free to make any comment! If anything is unclear, just write in the comment and I will update the post!

Thanks for reading!

Last modified: 26 January 2022