<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal">Hello,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal">So instead of installing a 2<sup>nd</sup> 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.<o:p></o:p></p>
<p class="MsoNormal">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)<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">However, after switching out the DB connection info in the AuthBy SQLTOTP clause, I get the following error:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Sat Nov 3 18:24:10 2018: DEBUG: Handling with Radius::AuthSQLTOTP:
<o:p></o:p></p>
<p class="MsoNormal">Sat Nov 3 18:24:10 2018: DEBUG: Radius::AuthSQLTOTP looks for match with sschwarz [sschwarz]<o:p></o:p></p>
<p class="MsoNormal">Sat Nov 3 18:24:10 2018: DEBUG: Connecting to 'dbi:ODBC:Radiator Connection id: -00000'<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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'':
<o:p></o:p></p>
<p class="MsoNormal">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)<o:p></o:p></p>
<p class="MsoNormal">[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000)<o:p></o:p></p>
<p class="MsoNormal">Sat Nov 3 18:24:10 2018: DEBUG: Connecting to 'dbi:ODBC:Radiator Connection id: -00000'<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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'':
<o:p></o:p></p>
<p class="MsoNormal">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)<o:p></o:p></p>
<p class="MsoNormal">[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000)<o:p></o:p></p>
<p class="MsoNormal">Sat Nov 3 18:24:10 2018: DEBUG: Radius::AuthSQLTOTP IGNORE: Database failure: sschwarz [sschwarz]<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I was wondering what I would need to change in order to use a MS SQL database.<o:p></o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Kind regards,<o:p></o:p></p>
<p class="MsoNormal">Stephan Schwarz<o:p></o:p></p>
</div>
</body>
</html>