[RADIATOR] Loading configuration dynamically from SQL database

Remco van Noorloos rvannoorloos at proxsys.nl
Tue Apr 12 08:00:31 CDT 2011


Hi Heikki,

Thanks again for you answer.

That's a possibility indeed. Aren't there any plans to improve this mechanism to make the configuration even more dynamically than it is right now?

Perhaps it is something I can change easily myself in the Perl code?

The Acct-Session-Id attribute is needed and is send by our Cisco routers in an Access-Request to be able to combine multiple Access-Requests based on that session ID.

The snippet below is from the log when I try to 'access' the %{CONNECTION_ID}. Strange thing is that it works in one AuthBy but doesn't work in the next one.

The 'EXEC spPasswdSelect , ''' should include the CONNECTION_ID as first parameter (the second one is indeed Acct-Session-Id, it doesn't matter whether it's empty or not). When looking at the log below the CONNECTION_ID is empty though.

What am I doing wrong?

---

*** Received from 127.0.0.1 port 1739 ....
Code:       Access-Request
Identifier: 141
Authentic:  <229><20>~<138>k<128>?&:<131><246><147><184>/<27><236>
Attributes:
	User-Name = "prox1-anl1 at dsl.proxsys.net"
	Service-Type = Framed-User
	NAS-IP-Address = 203.63.154.1
	NAS-Identifier = "203.63.154.1"
	NAS-Port = 1234
	Called-Station-Id = "123456789"
	Calling-Station-Id = "987654321"
	NAS-Port-Type = Token-Ring
	User-Password = <160><177>P<175>qDe<19>f<169><18><180><159><144><230><13>

Tue Apr 12 14:53:36 2011: DEBUG: Handling request with Handler '', Identifier 'DefaultHandler'
Tue Apr 12 14:53:36 2011: DEBUG:  Deleting session for prox1-anl1 at dsl.proxsys.net, 203.63.154.1, 1234
Tue Apr 12 14:53:36 2011: DEBUG: Handling with Radius::AuthSQL: DETERMINE_AUTH_BACKEND
Tue Apr 12 14:53:36 2011: DEBUG: Handling with Radius::AuthSQL: DETERMINE_AUTH_BACKEND
Tue Apr 12 14:53:36 2011: DEBUG: Query is: 'EXEC spGetAuthenticationSource 'prox1-anl1 at dsl.proxsys.net', 'Token-Ring', 'Framed-User', ''': 
Tue Apr 12 14:53:36 2011: DEBUG: Radius::AuthSQL looks for match with prox1-anl1 at dsl.proxsys.net [prox1-anl1 at dsl.proxsys.net]
Tue Apr 12 14:53:36 2011: DEBUG: Radius::AuthSQL ACCEPT: : prox1-anl1 at dsl.proxsys.net [prox1-anl1 at dsl.proxsys.net]
Tue Apr 12 14:53:36 2011: DEBUG: AuthBy SQL result: ACCEPT, 
Tue Apr 12 14:53:36 2011: DEBUG: Handling with Radius::AuthHANDLER: 
Tue Apr 12 14:53:36 2011: DEBUG: AuthBy HANDLER is redirecting to Handler 'AUTH_USER_realmSQL'
Tue Apr 12 14:53:36 2011: DEBUG: Handling request with Handler '', Identifier 'AUTH_USER_realmSQL'
Tue Apr 12 14:53:36 2011: DEBUG:  Deleting session for prox1-anl1 at dsl.proxsys.net, 203.63.154.1, 1234
Tue Apr 12 14:53:36 2011: DEBUG: Handling with Radius::AuthSQL: 
Tue Apr 12 14:53:36 2011: DEBUG: Handling with Radius::AuthSQL: 
Tue Apr 12 14:53:36 2011: DEBUG: Query is: 'EXEC spPasswdSelect , ''': 
Tue Apr 12 14:53:36 2011: ERR: Execute failed for 'EXEC spPasswdSelect , ''': [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000)
Tue Apr 12 14:53:36 2011: ERR: Execute failed for 'EXEC spPasswdSelect , ''': [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000)
Tue Apr 12 14:53:36 2011: DEBUG: Radius::AuthSQL looks for match with prox1-anl1 at dsl.proxsys.net [prox1-anl1 at dsl.proxsys.net]
Tue Apr 12 14:53:36 2011: DEBUG: Radius::AuthSQL REJECT: No such user: prox1-anl1 at dsl.proxsys.net [prox1-anl1 at dsl.proxsys.net]
Tue Apr 12 14:53:36 2011: DEBUG: Query is: 'EXEC spPasswdSelect , ''': 
Tue Apr 12 14:53:36 2011: ERR: Execute failed for 'EXEC spPasswdSelect , ''': [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000)
Tue Apr 12 14:53:36 2011: ERR: Execute failed for 'EXEC spPasswdSelect , ''': [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000)
Tue Apr 12 14:53:36 2011: DEBUG: AuthBy SQL result: REJECT, No such user
Tue Apr 12 14:53:36 2011: DEBUG: AuthBy HANDLER result: REJECT, No such user
Tue Apr 12 14:53:36 2011: INFO: Access rejected for prox1-anl1 at dsl.proxsys.net: No such user
Tue Apr 12 14:53:36 2011: DEBUG: Packet dump:
*** Sending to 127.0.0.1 port 1739 ....
Code:       Access-Reject
Identifier: 141
Authentic:  e<252><160><164><169>q(<223>lm<221>0<142>p<135><31>
Attributes:
	Reply-Message = "Request Denied"
 
-----Oorspronkelijk bericht-----
Van: Heikki Vatiainen [mailto:hvn at open.com.au] 
Verzonden: dinsdag 12 april 2011 14:43
Aan: Remco van Noorloos
CC: radiator at open.com.au
Onderwerp: Re: [RADIATOR] Loading configuration dynamically from SQL database

On 04/11/2011 05:13 PM, Remco van Noorloos wrote:

> Currently we have 100+ LDAP servers we're authenticating with. So if
> we have to edit the config file in order to make a change that
> wouldn't be manageable for us and is a situation we really like to
> avoid.

That is very understandable.

One way to do this would be to generate automatically all the AuthBys
and then use Include to pull them in Radiator configuration.

> From what I understand the implementation isn't really uniform? Since
> some parameters can be set dynamically and others not?

Most things can be set dynamically so it is uniform in that sense. What
is not uniform is what the lifetime (for the better word) of various
parameters is.

The userid in search parameter varies from request to request, naturally.

AuthDN can be initialised when the first request arrives, but within one
AuthBy LDAP2, the AuthDN stays the same between request so that there is
not a separate bind operation for each request.

Host parameter can be set from a global variable, but that is when
Radiator starts or is reinitialised.

> In addition, when I use the following Handler the same problem
> occurs. In this snippet the 'CONNECTION_ID' is empty, this attribute
> is set in the ' DETERMINE_AUTH_BACKEND' AuthBy as included in my last
> mail.

Try Acct-Session-Id - notice the spelling.

Also, you are using AuthSelect with DETERMINE_AUTH_BACKEND and using
Acct-Session-Id as a part of AuthSelect. Since this select runs by
default for authentication requests, does it have access to
Acct-Session-Id parameter?

You should see from the log what happens. AuthSelect should be formatted
for each request, so %{CONNECTION_ID} should contain the value from the
request.

> <Handler>
>     Identifier AUTH_USER_realmSQL
> 	
> 	#
> 	# Perform SQL authentication
> 	#
>     <AuthBy SQL>
> 		DBSource		dbi:ODBC:DRIVER={SQL Server};SERVER={%{GlobalVar:DB_PMS_SERVER}};DATABASE=%{GlobalVar:DB_PMS_NAME}
> 		DBUsername		%{GlobalVar:DB_PMS_USER}
> 		DBAuth			%{GlobalVar:DB_PMS_PASSWORD}
> 		
> 		AuthSelect 		EXEC spPasswdSelect %{CONNECTION_ID}, %{Quote:%{Acct-Session-ID}}
> 		AuthColumnDef 	0, User-Password, check
> 		AuthColumnDef 	1, CONNECTION_ID, request
>     </AuthBy>
> </Handler>
> 


-- 
Heikki Vatiainen <hvn at open.com.au>

Radiator: the most portable, flexible and configurable RADIUS server
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS,
TTLS, PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP,
DIAMETER etc. Full source on Unix, Windows, MacOSX, Solaris, VMS,
NetWare etc.


More information about the radiator mailing list