[RADIATOR] AuthBy SQLTOTP Microsoft SQL Server backend

[Schwarz, S. (ICT)]

Hello,

I'm using the "AuthBy SQLTOTP" for over a year now, with the samples provided in the goodies folder. However the DB backend is a local MySQL database.
For HA purposes, I have 2 identical RADIUS servers, however this MySQL database is only located on 1 of them. So for all the TOTP authentication, I can only use 1 specific RADIUS server.
So instead of installing a 2nd copy of MySQL on the other server and somehow sync the data between the 2 databases (otherwise we'd have to update 2 databases when we want to edit the totp entries), I rather connect to our centrally managed MS SQL database as a backend.
Since I already have a Database on the MS SQL server for logging purposes (AuthLog SQL and Log SQL), I figured I'd recreate the totpkeys table and data on it. (Also makes it easy to confirm that the DBSource is properly configured)

However, after switching out the DB connection info in the AuthBy SQLTOTP clause, I get the following error:

Sat Nov  3 18:24:10 2018: DEBUG: Handling with Radius::AuthSQLTOTP:
Sat Nov  3 18:24:10 2018: DEBUG: Radius::AuthSQLTOTP looks for match with sschwarz [sschwarz]
Sat Nov  3 18:24:10 2018: DEBUG: Connecting to 'dbi:ODBC:Radiator Connection id: -00000'

Sat Nov  3 18:24:10 2018: DEBUG: Query to 'dbi:ODBC:Radiator Connection id: -00000': 'select secret, active, pin, digits, bad_logins, unix_timestamp(accessed), last_timestep from totpkeys where username='sschwarz'':
Sat Nov  3 18:24:10 2018: ERR: Execute failed for 'select secret, active, pin, digits, bad_logins, unix_timestamp(accessed), last_timestep from totpkeys where username='sschwarz'': [Microsoft][ODBC SQL Server Driver][SQL Server]'unix_timestamp' is not a recognized built-in function name. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000)
Sat Nov  3 18:24:10 2018: DEBUG: Connecting to 'dbi:ODBC:Radiator Connection id: -00000'

Sat Nov  3 18:24:10 2018: DEBUG: Query to 'dbi:ODBC:Radiator Connection id: -00000': 'select secret, active, pin, digits, bad_logins, unix_timestamp(accessed), last_timestep from totpkeys where username='sschwarz'':
Sat Nov  3 18:24:10 2018: ERR: Execute failed for 'select secret, active, pin, digits, bad_logins, unix_timestamp(accessed), last_timestep from totpkeys where username='sschwarz'': [Microsoft][ODBC SQL Server Driver][SQL Server]'unix_timestamp' is not a recognized built-in function name. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000)
Sat Nov  3 18:24:10 2018: DEBUG: Radius::AuthSQLTOTP IGNORE: Database failure: sschwarz [sschwarz]

I was wondering what I would need to change in order to use a MS SQL database.
The preferred solution is to use the MS SQL instead of having 2 different databases (TOTP and Logging) and having to maintain 2 instances of MySQL and keep that data in sync.

Kind regards,
Stephan Schwarz
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.open.com.au/pipermail/radiator/attachments/20181103/c7adc54e/attachment.html>