sql failover

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

sql failover

Richard J Palmer
Hi there...

This may be a simple question but I am a little stuck.

We are using mssql as a backend for a radius server - this generally
works fine.

A new mirror server was recently installed and I am trying to create
redundancy within freeradius - However I have a problem to which I am
not sure of the answer

I have created sql1 and sql2 One pointing to the primary server - the
second to the backup

the config loads OK etc however with the current mirror setup only one
of the two SQL servers will allow the radius databases to be accessed.
The other when connected gives 'Database 'radius' cannot be opened. It
is in the middle of a restore.' At this point the radius server stops
loading rather than using the working SQL connection.

IF I use the odbc driver I can set the radius database to be blank AND
set the table names to be radius..<table name> BUT If I leave the
database name blank in the freetds driver I get 'rlm_sql_freetds:
Server msg from "<Server Name>": severity(102), number(15), origin(1),
layer(1), procedure "none": Incorrect syntax near ';'.'  Which I
assume is from when it is trying to select the database.

Can you confirm if there is a way to work with the current SQL setup ?

Thanks!

Richard Palmer
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|

Re: sql failover

Alan DeKok-2
On May 14, 2017, at 8:38 PM, Richard J Palmer <[hidden email]> wrote:
> This may be a simple question but I am a little stuck.
>
> We are using mssql as a backend for a radius server - this generally works fine.
>
> A new mirror server was recently installed and I am trying to create redundancy within freeradius - However I have a problem to which I am not sure of the answer
>
> I have created sql1 and sql2 One pointing to the primary server - the second to the backup
>
> the config loads OK etc however with the current mirror setup only one of the two SQL servers will allow the radius databases to be accessed. The other when connected gives 'Database 'radius' cannot be opened. It is in the middle of a restore.' At this point the radius server stops loading rather than using the working SQL connection.

  You can set "start = 0" in the SQL module configuration.  This is documented in the comments in the SQL module, for the "start" configuration.

  But it's generally a bad idea to have your system lie to FreeRADIUS.  If you want fail-over, do it in FreeRADIUS.  *Don't* do it somewhere else, and then also try to do it in FreeRADIUS.  That's a recipe for disaster.

  The two systems can get out of sync, and then the "redundant" solution will end up causing problems instead of solving them.

> IF I use the odbc driver I can set the radius database to be blank AND set the table names to be radius..<table name> BUT If I leave the database name blank in the freetds driver I get 'rlm_sql_freetds: Server msg from "<Server Name>": severity(102), number(15), origin(1), layer(1), procedure "none": Incorrect syntax near ';'.'  Which I assume is from when it is trying to select the database.

  Sounds like and ODBC syntax error to me.

> Can you confirm if there is a way to work with the current SQL setup ?

  Let FreeRADIUS do fail-over if you want it to do fail-over.

  Extra layers between FR and SQL just causes problems 99% of the time.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|

Re: sql failover

Richard J Palmer
In reply to this post by Richard J Palmer

Hi

I think this is where I am going wrong.

The aim is I want FR to handle the failover - We should be using the
primary SQL server at all times UNLESS it fails.

MS SQL is set as a mirrored pair (nothing to do with FR) the 'Live'
Server will answer queries - the backup will allow connections BUT the
queries will fail.
IF the primary fails the backup automatically allows queries and the
primary will not

From https://wiki.freeradius.org/config/Fail-over
I have set


redundant {
    # try module sql1
          sql1
          # if that's down, try module sql2
          sql2
          # otherwise drop the request as
          # it's been "handled" by the "always"
          # module (see doc/rlm_always)
          handled
    }

Within the authenticate / authorise / accounting / postauth sections

The issue is the startup of the modules that is causing the issue
given that the Database on sql2 is not available unless it is the
'live' server

With regards to

>
>>
>> IF I use the odbc driver I can set the radius database to be blank AND
>> set the table names to be radius..<table name> BUT If I leave the
>> database name blank in the freetds driver I get 'rlm_sql_freetds:
>> Server msg from "<Server Name>": severity(102), number(15), origin(1),
>> layer(1), procedure "none": Incorrect syntax near ';'.' Which I assume
>> is from when it is trying to select the database.
>
>   Sounds like and ODBC syntax error to me.
This is because the database is not available on the backup server -
so the select db to radius fails. Using the unixodbc driver I can
leave the database name to blank - and edit the table names to eg
radius..radacct for example - but with the freetds driver if I leave
the database name blank It seems to be trying the select anyway which
fails - as the db name is blank. For now I have changed to using
unixodbc.

It is totally possible I have the wrong end of the stick somewhere -
If I have misunderstood / read this I am always happy for some
pointers.

Richard



> --- Original message ---
> Subject: Re: sql failover
> From: Alan DeKok <[hidden email]>
> To: FreeRadius users mailing list
> <[hidden email]>
> Date: Monday, 15/05/2017 1:45 PM
>
> On May 14, 2017, at 8:38 PM, Richard J Palmer <[hidden email]>
> wrote:
>>
>> This may be a simple question but I am a little stuck.
>>
>> We are using mssql as a backend for a radius server - this generally
>> works fine.
>>
>> A new mirror server was recently installed and I am trying to create
>> redundancy within freeradius - However I have a problem to which I am
>> not sure of the answer
>>
>> I have created sql1 and sql2 One pointing to the primary server - the
>> second to the backup
>>
>> the config loads OK etc however with the current mirror setup only one
>> of the two SQL servers will allow the radius databases to be accessed.
>> The other when connected gives 'Database 'radius' cannot be opened. It
>> is in the middle of a restore.' At this point the radius server stops
>> loading rather than using the working SQL connection.
>
>    You can set "start = 0" in the SQL module configuration.  This is
> documented in the comments in the SQL module, for the "start"
> configuration.
>
>    But it's generally a bad idea to have your system lie to
> FreeRADIUS.  If you want fail-over, do it in FreeRADIUS.  *Don't* do
> it somewhere else, and then also try to do it in FreeRADIUS.  That's a
> recipe for disaster.
>
>    The two systems can get out of sync, and then the "redundant"
> solution will end up causing problems instead of solving them.
>
>>
>> IF I use the odbc driver I can set the radius database to be blank AND
>> set the table names to be radius..<table name> BUT If I leave the
>> database name blank in the freetds driver I get 'rlm_sql_freetds:
>> Server msg from "<Server Name>": severity(102), number(15), origin(1),
>> layer(1), procedure "none": Incorrect syntax near ';'.'  Which I
>> assume is from when it is trying to select the database.
>
>    Sounds like and ODBC syntax error to me.
>
>>
>> Can you confirm if there is a way to work with the current SQL setup ?
>
>    Let FreeRADIUS do fail-over if you want it to do fail-over.
>
>    Extra layers between FR and SQL just causes problems 99% of the
> time.
>
>    Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|

Re: sql failover

Alan DeKok-2
On May 15, 2017, at 9:13 AM, Richard J Palmer <[hidden email]> wrote:
> I think this is where I am going wrong.
>
> The aim is I want FR to handle the failover - We should be using the primary SQL server at all times UNLESS it fails.

  Which means that the secondary has to be available *instantly* when the primary fails.

  A secondary fail-over system will take time to notice, and then FreeRADIUS will think that *both* servers are down.

> MS SQL is set as a mirrored pair (nothing to do with FR) the 'Live' Server will answer queries - the backup will allow connections BUT the queries will fail.
> IF the primary fails the backup automatically allows queries and the primary will not

  As I said, doing that will just cause problems.

  If you're going to handle the fail-over at the SQL level, then the SQL level should handle fail-over.

  Configure an SQL proxy with fail-over, and then have FreeRADIUS point to the proxy.

  Two sets of fail-over is a recipe for disaster.

> The issue is the startup of the modules that is causing the issue given that the Database on sql2 is not available unless it is the 'live' server

  As I said, there is a fix for that.

  As I also said, doing two sets of fail-over is bad.  Don't do it.  It will CREATE problems, not solve them.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html