[RADIATOR] AuthBy SQLTOTP Microsoft SQL Server backend

[Schwarz, S. (ICT)]

Thanks Martin,

All makes sense now. 
The update query actually writes the same timestamp format into the table as the default query with MySQL

As you suggested I performed a couple of bad logins to check what would happen.
The replay attack gets stopped regardless of the query, since it only uses the last_timestep field for that.
But as you guessed, the brute force attack didn't trigger. After updating the query to your suggestion I now see that because of the high bad login count my authentication attempts are blocked as it's detected as a brute force attack.

So the end result is, either of these 2 AuthSelect queries work along with the UpdateQuery.
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
AuthSelect  select secret, active, pin, digits, bad_logins,datediff(s,'1970-01-01 00:00:00',accessed), last_timestep from totpkeys where username=%0

UpdateQuery update totpkeys set accessed=CURRENT_TIMESTAMP, bad_logins=%0, last_timestep=%2 where username=%1

Thanks for your time :).

Kind regards,
Stephan Schwarz

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

On 03.11.18 22:54, Schwarz, S. (ICT) wrote:


Hi Stephan,

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

great!


> 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
The UpdateQuery should be OK while I have some doubts on the AuthSelect as no 'cast' to Epoch takes place



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

ref.pdf says:
Field 5 (last_time_accessed) is the unix timestamp of the last authentication attempt. It is used to detect brute force attacks.
(radiator uses imho internally epoch to check if an AuthRequest is within BadLoginWindow / BadLoginWindow)




> 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

ref.pdf does not require name'd result columns (I assume, DBI
fetchrow_array() or some variant of it is used in the modules. So only the order of results is  important)


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

If it's really working, try to brute force your account with ie (normal requests will work for sure.. this field is required to find anomalies in auth requests):

	- replay attack (re-use the same token multiple times => it should work only the first time; afterwards radiator should complain about a replay
attack)
	- use wrong tokens/passwords for a couple of times in a short time frame. Radiator should log 'MaxBadLogins'

Since ref.pdf is very explicit in requesting a unix time stamp in field 5, I would provide one and datediff is one option to do so.
If I remember correctly, the MaxBadLogins dedection does not work, if there is no unix time stamp as result on field 5 (as mentioned ealier - I'm using a SQlite backend and had to adjust the AuthSelect to 'strftime('%%s',accessed)' for field 5 to provide a unix time stamp to Radiator internals and replay / brute force attacks are mitigated as documented ;-) )


regards
	Martin