From richard@merula.net Mon May 15 02:38:12 2017
From: Richard J Palmer <richard@merula.net>
To: freeradius-users@lists.freeradius.org
Subject: sql failover
Date: Mon, 15 May 2017 01:38:02 +0100
Message-ID: <5918f86a.4c4.d90.568e@merula.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============4714949444465577823=="

--===============4714949444465577823==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit

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

--===============4714949444465577823==--


From aland@deployingradius.com Mon May 15 14:43:52 2017
From: Alan DeKok <aland@deployingradius.com>
To: freeradius-users@lists.freeradius.org
Subject: Re: sql failover
Date: Mon, 15 May 2017 08:43:49 -0400
Message-ID: <78C9564A-D763-4BDC-A98F-3B2C8F26BE05@deployingradius.com>
In-Reply-To: <5918f86a.4c4.d90.568e@merula.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============4945260877754579004=="

--===============4945260877754579004==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

On May 14, 2017, at 8:38 PM, Richard J Palmer <richard@merula.net> wrote:
> This may be a simple question but I am a little stuck.
>=20
> We are using mssql as a backend for a radius server - this generally works =
fine.
>=20
> A new mirror server was recently installed and I am trying to create redund=
ancy within freeradius - However I have a problem to which I am not sure of t=
he answer
>=20
> I have created sql1 and sql2 One pointing to the primary server - the secon=
d to the backup
>=20
> the config loads OK etc however with the current mirror setup only one of t=
he 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 usi=
ng the working SQL connection.

  You can set "start =3D 0" in the SQL module configuration.  This is documen=
ted 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 yo=
u want fail-over, do it in FreeRADIUS.  *Don't* do it somewhere else, and the=
n 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 b=
lank in the freetds driver I get 'rlm_sql_freetds: Server msg from "<Server N=
ame>": severity(102), number(15), origin(1), layer(1), procedure "none": Inco=
rrect syntax near ';'.'  Which I assume is from when it is trying to select t=
he 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.



--===============4945260877754579004==--