[RADIATOR] AuthBy SQLTOTP Microsoft SQL Server backend

[Schwarz, S. (ICT)]

Hi Martin,

Thanks for the example.
I actually used the SQL Server Migration Assistant in order to help me set up the table on the MS SQL. So the columns where already configured with the correct types.

I've managed to get it working with the following queries:
AuthSelect select secret, active, pin, digits, bad_logins,accessed, last_timestep from totpkeys where username=%0
UpdateQuery update totpkeys set accessed=CURRENT_TIMESTAMP, bad_logins=%0, last_timestep=%2 where username=%1

One thing I don't quite understand in your example, is what is the use of "datediff(s,'1970-01-01 00:00:00',accessed)"? I believe it converts the pretty date time format back to unix time, but how is that value actually used in the AuthSQLTOTP?

I've received another reply, with another query example and I think the end result should be as following then (since with your example, the column for accessed has no name, not sure if that were to matter):
AuthSelect  select secret, active, pin, digits, bad_logins,datediff(s,'1970-01-01 00:00:00',accessed) as accessed, last_timestep from totpkeys where username=%0
UpdateQuery update totpkeys set accessed=CURRENT_TIMESTAMP, bad_logins=%0, last_timestep=%2 where username=%1

I'd just like to understand what the purpose is of the unix time in the authselect query, when the regular date time format already seems to work. My mind is much more at ease when I know why something works, instead of seeing something work but not understanding it :).

Kind regards,
Stephan Schwarz


-----Original Message-----
From: Martin Mersberger <gremlin at portal-to-web.de> 
Sent: Saturday, November 3, 2018 10:26 PM
To: Schwarz, S. (ICT) <S.Schwarz at lumc.nl>; radiator at lists.open.com.au
Subject: Re: [RADIATOR] AuthBy SQLTOTP Microsoft SQL Server backend

Hi Stephan,


> 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)

in short:

mssql does not know the (mysql specific) unix_timestamp function. it should work, if you modify the query to do


AuthSelect select secret, active, pin, digits, bad_logins,
datediff(s,'1970-01-01 00:00:00',accessed), last_timestep from totpkeys where username=%0

maybe, UpdateQuery needs an update, too..

I had the very same situation with a sqlite backend ;-)


PS: accessed should be a date type column.



cheers
	Martin


check [1]
https://stackoverflow.com/questions/8837225/unix-timestamp-in-sql-server