Recently, we have had multiple customer cases in the need of offline TOTP (time-based one-time password) implementations. Both private enterprises and public institutions working on different fields have discovered an increasing need for offline multi-factor authentication to protect their critical infrastructure. These use cases include for example power companies, transport infrastructure and other use cases that are used in private networks, and where secure authentication is essential at all times.
Many of these customers use Windows Server and Microsoft SQL server in their implementations, so we wanted to share how Radiator AAA Server Software can be used with them when implementing an offline TOTP solution. And as clarified below, given the flexibility of Radiator, other platforms can be used as well—do not hesitate to contact us with your own specific use case in your own infrastructure.
How it is done
We have had a working example in the Radiator goodies directory that can be leveraged to individual needs:
totp.cfg and totp.sql
Sample configuration file for Radiator, showing how to authenticate using TOTP (RFC 6238) one-time-passwords. The sample MySQL database schema provides test users, with and without a PIN.
generate-totp.pl
Supporting script for generating secret values for TOTP and printing them in different text formats and as QR code images.
The existing example is using SQL definitions specific to MySQL and MariaDB database servers. As Radiator is flexible, the same functionality can be achieved on any supported OS and with any database. Here we show how to set up a similar system with Windows Server 2012 to 2022 and Microsoft SQL Server 2012 to 2022, with Radiator AAA Server Software (current version 4.26). The new Windows-specific configuration shown here will also be included in the goodies of the oncoming release of Radiator soon.
To start with, we expect the system with Windows Server is already installed and hardened as needed. Also, installing Microsoft SQL Server and Microsoft SQL Server Management Studio is out of scope of this post. You can try this TOTP setup out also on a standard desktop Windows version and free SQL Server Express (https://www.microsoft.com/en-us/sql-server/sql-server-downloads).
After the prerequisites are met, the next step is to download and install the rest of the needed software packages:
ODBC Driver for SQL Server
https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server
Radiator AAA Server Software, Radiator Windows MSI installer
https://radiatorsoftware.com/products/radiator/
After creating a new ODBC data source (be sure to select a 64-bit driver on 64-bit environments), you can test if the DSN is available to Radiator by running a small test script. Start the command shell with correct environment settings by selecting Radiator configuration from the Start menu and then clicking Perl command line. Save and run the following script on the server. The script lists all DSNs it finds, and if you see the newly created DSN, everything is OK.
# List available data sources
#
# Example run:
# C:\> perl list_datasources.pl
# - dbi:ODBC:<datasourcename1>
# - dbi:ODBC:<datasourcename2>
use strict;
use DBI;
my @dsns = DBI->data_sources('ODBC');
foreach my $d (@dsns)
{
print "- $d\n";
}
If you want to generate TOTP secrets with generate-totp.pl you also need to install the following new modules. The command cpanm makes it easy if you're connected to the internet:
cpanm MIME::Base32
and
cpanm Imager::QRCode
You can also download the modules manually (check for the latest versions), for example:
https://cpan.metacpan.org/authors/id/R/RE/REHSACK/MIME-Base32-1.303.tar.gz
https://cpan.metacpan.org/authors/id/K/KU/KURIHARA/Imager-QRCode-0.035.tar.gz
and install them from local files like:
cpanm MIME-Base32-1.303.tar.gz
cpanm Imager-QRCode-0.035.tar.gz
Create the database and grant the needed privileges to the user (SELECT and UPDATE). Here's the table definition with SQL Server specific field types. The definition is stripped from comments for brevity, and some fields are optional. Please see
goodies for full details.
CREATE TABLE totpkeys
(
id INT NOT NULL IDENTITY(1,1),
active BIT DEFAULT 0,
created DATETIME NOT NULL,
accessed DATETIME NOT NULL,
username VARCHAR(100) UNIQUE NOT NULL,
tokenId TEXT,
pin TEXT,
secret VARCHAR(130) UNIQUE NOT NULL,
digits INT DEFAULT 6,
bad_logins INT DEFAULT 0,
last_timestep INT,
algorithm TEXT NOT NULL,
timestep INT DEFAULT 30,
timestep_origin INT DEFAULT 0,
PRIMARY KEY (id)
);
Insert some example data ( we use 6-digit codes for broader compatibility, and here's only some of the records):
INSERT INTO totpkeys VALUES (1, GETUTCDATE(), GETUTCDATE(), 'mikem', NULL, NULL,
'3132333435363738393031323334353637383930', 6, 0, NULL, 'SHA1', 30, 0);
INSERT INTO totpkeys VALUES (1, GETUTCDATE(), GETUTCDATE(), 'mikem512', NULL, NULL,
'31323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334',
6, 0, NULL, 'SHA512', 30, 0);
INSERT INTO totpkeys VALUES (1, GETUTCDATE(), GETUTCDATE(), 'fred', NULL, 'fred',
'1111111111111111111111111111111111111111', 6, 0, NULL, 'SHA1', 30, 0);
Then we make modifications to the example TOTP configuration. Change the DBSource name (DSN) and credentials as needed in the
<AuthBy SQLTOTP> block. Also,
AuthSelect and
UpdateQuery are modified with SQL Server syntax:
<AuthBy SQLTOTP>
DBSource dbi:ODBC:totp
DBUsername totp
DBAuth fred
AuthSelect SELECT secret, active, pin, digits, bad_logins, DATEDIFF(s, '1970-01-01', accessed), \
last_timestep, algorithm, timestep, timestep_origin FROM totpkeys WHERE username=?
AuthSelectParam %0
UpdateQuery UPDATE totpkeys SET accessed=GETUTCDATE(), bad_logins=?, last_timestep=? WHERE username=?
UpdateQueryParam %0
UpdateQueryParam %2
UpdateQueryParam %1
</AuthBy>
Update the configuration otherwise as needed (ie. make sure paths are correct to your setup, etc.), and set
Trace 4 to see the interesting information during testing.
(Re)start the Radiator server process to make sure the new configuration will be used, and then you can try your new setup.
Importing the keys to your TOTP application can be done with the help of
generate-totp.pl script. If you use the predefined examples, you can get the QR codes by running it like
C:\Radiator>perl generate-totp.pl -accountname "mikem" -issuer "Organisation" \
-algorithm SHA1 -hex_secret "3132333435363738393031323334353637383930" -digits 6 \
-image_format gif -qrcode_file \temp\mikem.gif
TOTP key to insert into Radiator database: 3132333435363738393031323334353637383930
TOTP key in BASE32 for client: GEZD GNBV GY3T QOJQ GEZD GNBV GY3T QOJQ
Writing QR code file \temp\mikem.gif
otpauth://totp/Radiator:mikem?secret=GEZDGNBVGY3TQOJQGEZDGNBVGY3TQOJQ&issuer=Radiator&
algorithm=SHA1&digits=8&period=30
You can also create new keys by leaving out the -hex_secret parameter and insert the generated hex string into the database.
Then use your preferred method to display the generated QR code image (MS Paint or web browser are fine) and scan the key into your TOTP application. Microsoft and Google have their authenticators available for mobile devices, and Apple's mobile devices have the feature built-in. There's also a free alternative FreeOTP with an open source codebase: https://github.com/freeotp.
After getting your authenticator app set up you're ready for your first TOTP authentication using radpwtst on command line. Replace the password with your time-based response:
perl radpwtst -noacct -user mikem -password 751352
Or if static PIN is used, here PIN "fred" is prefixed to TOTP one-time-password:
perl radpwtst -noacct -user fred -password fred755224
If everything goes as expected you'll see the Access-Accept response on radpwtst's output, and also on the Radiator server's log. And if something fails, the log can be used to pin-point the problem.
This example is just basic password authentication (PAP). You can now change and expand the configuration to enable more elaborate TOTP RADIUS authentication to your devices or software as needed.
Would you like to know more?
If you want to know more how offline TOTP can be implemented for your use case and solution with Radiator, please do not hesitate to contact us. We can always be reached via email at info@radiatorsoftware.com
