(RADIATOR) AutBySQL - Authencitation and Accounting Problem
Hugh Irvine
hugh at open.com.au
Thu Jun 9 03:02:44 CDT 2005
Hello Kadir -
You should do something like this:
<Realm DEFAULT>
SessionDatabase SDB1
MaxSessions 1
RewriteUsername s/^([^@]+).*/$1/
# define overall AuthByPolicy
AuthByPolicy ContinueAlways
# This autby sql for accounting logs
<AuthBy SQL>
HandleAcctStatusTypes Start,Alive,Stop
DBSource dbi:ODBC:DSN=xxxx
DBUsername
DBAuth
# disable authentication
AuthSelect
AcctSQLStatement EXEC Sp_INS_AccountingLog\
'%n',\
'%m.%d.%Y %H:%M:%S',\
'%{Acct-Status-Type}',\
'%{Acct-Delay-Time}',\
'%{Acct-Input-Octets}',\
'%{Acct-Output-Octets}',\
'%{Acct-Session-Id}',\
'%{Acct-Session-Time}',\
'%{Acct-Terminate-Cause}',\
'%{NAS-IP-Address}',\
'%{NAS-Port}',\
'%{Framed-IP-Address}',\
'%{Called-Station-Id}',\
'%{Calling-Station-Id}',\
'213.243.1.36:1813','%{Connect-Info}'\
'%{Ascend-Xmit-Rate}'
</AuthBy>
# define AuthBy GROUP for authentication
# with AuthByPolicy ContinueWhileAccept
<AuthBy GROUP>
AuthByPolicy ContinueWhileAccept
#This authbysql authenticate user
<AuthBy SQL>
DBSource dbi:mysql:hostname=localhost:RADIUS
DBUsername xxxx
DBAuth xxxx
AuthSelect select PASSWORD from users where
USERNAME='%n'
</AuthBy>
<AuthBy DYNADDRESS>
AddressAllocator myallocator
PoolHint pool1
MapAttribute yiaddr, Framed-IP-Address
MapAttribute subnetmask, Framed-IP-Netmask
MapAttribute dnsserver, DNSServer
</AuthBy>
</AuthBy>
</Realm>
Please read the relevant sections of the Radiator 3.13 reference
manual ("doc/ref.html").
regards
Hugh
On 9 Jun 2005, at 17:54, Kadir Saruhan wrote:
> Hi, I’m using following configuration file. There is three
> AuthBySQL statements. First one for logging accounting requests to
> sql and second one for authenticating user and third one for
> allocating ip address. But when i look debug(you can see following
> of config file) i’ve seen error that it handles access requests two
> times in First and second AuthBySQl statements. But i want that it
> hadles authenticate request only second AuthBySQL statement. How
> can i solve this?
>
> Thanks.
>
> Config File:
>
> LogDir .
>
> DbDir .
>
> Foreground
>
> Trace 4
>
> LogFile /var/log/radiuss/tcellgprs/tcell-gprs--%Y-%m-%d.log
>
> LogStdout
>
> AuthPort 1812
>
> AcctPort 1813
>
> <Client DEFAULT>
>
> Secret xxxx
>
> </Client>
>
>
> <AddressAllocator SQL>
>
> Identifier myallocator
>
> DBSource dbi:mysql:RADIUS
>
> DBUsername xxxx
>
> DBAuth xxxx
>
> <AddressPool pool1>
>
> Subnetmask xxxx
>
> DNSServer xxxx
>
> Range xxxxx
>
> </AddressPool>
>
> </AddressAllocator>
>
>
> <Realm DEFAULT>
>
> SessionDatabase SDB1
>
> MaxSessions 1
>
> RewriteUsername s/^([^@]+).*/$1/
>
>
> # This autby sql for accounting logs
>
> <AuthBy SQL>
>
> HandleAcctStatusTypes Start,Alive,Stop
>
> DBSource dbi:ODBC:DSN=xxxx
>
> DBUsername
>
> DBAuth
>
> AcctSQLStatement EXEC Sp_INS_AccountingLog\
>
> '%n',\
>
> '%m.%d.%Y %H:%M:%S',\
>
> '%{Acct-Status-Type}',\
>
> '%{Acct-Delay-Time}',\
>
> '%{Acct-Input-Octets}',\
>
> '%{Acct-Output-Octets}',\
>
> '%{Acct-Session-Id}',\
>
> '%{Acct-Session-Time}',\
>
> '%{Acct-Terminate-Cause}',\
>
> '%{NAS-IP-Address}',\
>
> '%{NAS-Port}',\
>
> '%{Framed-IP-Address}',\
>
> '%{Called-Station-Id}',\
>
> '%{Calling-Station-Id}',\
>
> '213.243.1.36:1813','%{Connect-Info}'\
>
> '%{Ascend-Xmit-Rate}'
>
> </AuthBy>
>
> AuthByPolicy ContinueAlways
>
>
> #This authbysql authenticate user
>
> <AuthBy SQL>
>
> DBSource dbi:mysql:hostname=localhost:RADIUS
>
> DBUsername xxxx
>
> DBAuth xxxx
>
> AuthSelect select PASSWORD from users where USERNAME='%n'
>
> </AuthBy>
>
>
> <AuthBy DYNADDRESS>
>
> AddressAllocator myallocator
>
> PoolHint pool1
>
> MapAttribute yiaddr, Framed-IP-Address
>
> MapAttribute subnetmask, Framed-IP-Netmask
>
> MapAttribute dnsserver, DNSServer
>
> </AuthBy>
>
> </Realm>
>
>
>
> <SessionDatabase SQL>
>
> Identifier SDB1
>
> FailureBackoffTime 10
>
> DBSource dbi:mysql:RADIUS
>
> DBUsername xxxx
>
> DBAuth xxxx
>
> </SessionDatabase>
>
>
>
>
> Trace 4 Debug:
>
> Code: Access-Request
>
> Identifier: 69
>
> Authentic: <228>\<9><240>z<209><135>jO<247><137><27>)rj<3>
>
> Attributes:
>
> User-Name = "leo"
>
> User-Password = "<0><30>m<179>Ij<229><153>E<152>\<26>#<196>]
> <131>"
>
> Called-Station-Id = "xxxxx"
>
> Calling-Station-Id = "xxxxx"
>
> Framed-Protocol = GPRS-PDP-Context
>
> NAS-Port-Type = Virtual
>
> NAS-Port = 60000
>
> Service-Type = Framed-User
>
> NAS-IP-Address = xxxxxx
>
> Thu Jun 9 10:20:31 2005: DEBUG: Handling request with Handler
> 'Realm=DEFAULT'
>
> Thu Jun 9 10:20:31 2005: DEBUG: Rewrote user name to leo
>
> Thu Jun 9 10:20:31 2005: DEBUG: SDB1 Deleting session for leo,
> 83.66.0.254, 60000
>
> Thu Jun 9 10:20:31 2005: DEBUG: do query is: 'delete from
> RADONLINE where NASIDENTIFIER='83.66.0.254' and NASPORT=060000':
>
> Thu Jun 9 10:20:31 2005: DEBUG: Handling with Radius::AuthSQL
>
> Thu Jun 9 10:20:31 2005: DEBUG: Handling with Radius::AuthSQL:
>
> Thu Jun 9 10:20:32 2005: DEBUG: Query is: 'select PASSWORD from
> SUBSCRIBERS where USERNAME='leo'':
>
> DBD::ODBC::st execute failed: [unixODBC][Microsoft][ODBC SQL Server
> Driver][SQL Server]Invalid object name 'SUBSCRIBERS'. (SQL-S0002)
>
> [unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement
> (s) could not be prepared. (SQL-37000)(DBD: st_execute/SQLExecute
> err=-1) at Radius/SqlDb.pm line 187.
>
> Thu Jun 9 10:20:32 2005: ERR: Execute failed for 'select PASSWORD
> from SUBSCRIBERS where USERNAME='leo'': [unixODBC][Microsoft][ODBC
> SQL Server Driver][SQL Server]Invalid object name 'SUBSCRIBERS'.
> (SQL-S0002)
>
> [unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement
> (s) could not be prepared. (SQL-37000)(DBD: st_execute/SQLExecute
> err=-1)
>
> DBD::ODBC::st execute failed: [unixODBC][Microsoft][ODBC SQL Server
> Driver][SQL Server]Invalid object name 'SUBSCRIBERS'. (SQL-S0002)
>
> [unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement
> (s) could not be prepared. (SQL-37000)(DBD: st_execute/SQLExecute
> err=-1) at Radius/SqlDb.pm line 187.
>
> Thu Jun 9 10:20:32 2005: ERR: Execute failed for 'select PASSWORD
> from SUBSCRIBERS where USERNAME='leo'': [unixODBC][Microsoft][ODBC
> SQL Server Driver][SQL Server]Invalid object name 'SUBSCRIBERS'.
> (SQL-S0002)
>
> [unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement
> (s) could not be prepared. (SQL-37000)(DBD: st_execute/SQLExecute
> err=-1)
>
> Thu Jun 9 10:20:32 2005: DEBUG: Radius::AuthSQL looks for match
> with leo
>
> Thu Jun 9 10:20:33 2005: DEBUG: Query is: 'select PASSWORD from
> SUBSCRIBERS where USERNAME='DEFAULT'':
>
> DBD::ODBC::st execute failed: [unixODBC][Microsoft][ODBC SQL Server
> Driver][SQL Server]Invalid object name 'SUBSCRIBERS'. (SQL-S0002)
>
> [unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement
> (s) could not be prepared. (SQL-37000)(DBD: st_execute/SQLExecute
> err=-1) at Radius/SqlDb.pm line 187.
>
> Thu Jun 9 10:20:33 2005: ERR: Execute failed for 'select PASSWORD
> from SUBSCRIBERS where USERNAME='DEFAULT'': [unixODBC][Microsoft]
> [ODBC SQL Server Driver][SQL Server]Invalid object name
> 'SUBSCRIBERS'. (SQL-S0002)
>
> [unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement
> (s) could not be prepared. (SQL-37000)(DBD: st_execute/SQLExecute
> err=-1)
>
> DBD::ODBC::st execute failed: [unixODBC][Microsoft][ODBC SQL Server
> Driver][SQL Server]Invalid object name 'SUBSCRIBERS'. (SQL-S0002)
>
> [unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement
> (s) could not be prepared. (SQL-37000)(DBD: st_execute/SQLExecute
> err=-1) at Radius/SqlDb.pm line 187.
>
> Thu Jun 9 10:20:34 2005: ERR: Execute failed for 'select PASSWORD
> from SUBSCRIBERS where USERNAME='DEFAULT'': [unixODBC][Microsoft]
> [ODBC SQL Server Driver][SQL Server]Invalid object name
> 'SUBSCRIBERS'. (SQL-S0002)
>
> [unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement
> (s) could not be prepared. (SQL-37000)(DBD: st_execute/SQLExecute
> err=-1)
>
> Thu Jun 9 10:20:34 2005: DEBUG: Handling with Radius::AuthSQL
>
> Thu Jun 9 10:20:34 2005: DEBUG: Handling with Radius::AuthSQL:
>
> Thu Jun 9 10:20:34 2005: DEBUG: Query is: 'select PASSWORD from
> users where USERNAME='leo'':
>
> Thu Jun 9 10:20:34 2005: DEBUG: Radius::AuthSQL looks for match
> with leo
>
> Thu Jun 9 10:20:34 2005: DEBUG: Radius::AuthSQL ACCEPT:
>
> Thu Jun 9 10:20:34 2005: DEBUG: Handling with Radius::AuthDYNADDRESS
>
> Thu Jun 9 10:20:34 2005: DEBUG: Query is: 'select TIME_STAMP,
> YIADDR, SUBNETMASK, DNSSERVER from RADPOOL
>
> where POOL='pool1' and STATE=0 order by TIME_STAMP':
>
> Thu Jun 9 10:20:34 2005: DEBUG: do query is: 'update RADPOOL set
> STATE=1,
>
> TIME_STAMP=1118301634,
>
> EXPIRY=1118388034, USERNAME='leo' where YIADDR='83.66.0.45' and
> TIME_STAMP =1118056570':
>
> Thu Jun 9 10:20:34 2005: DEBUG: Access accepted for leo
>
> Thu Jun 9 10:20:34 2005: WARNING: No such attribute DNSServer
>
> Thu Jun 9 10:20:34 2005: DEBUG: Packet dump:
>
> *** Sending to 83.66.0.254 port 1645 ....
>
> Code: Access-Accept
>
> Identifier: 69
>
> Authentic: <228>\<9><240>z<209><135>jO<247><137><27>)rj<3>
>
> Attributes:
>
> DNSServer = 213.243.1.42
>
> Framed-IP-Netmask = 255.255.255.0
>
> Framed-IP-Address = 83.66.0.45
>
> Thu Jun 9 10:20:34 2005: DEBUG: Packet dump:
>
> *** Received from 83.66.0.254 port 1646 ....
>
>
>
>
> Kadir Saruhan
>
> DOL System Administrator
>
> ksaruhan at e-kolay.com
>
> Tel: +90 212 498 35 16
>
> Fax:+90 212 677 05 92
> www.e-kolay.net
>
>
>
>
>
>
>
>
> <2.gif>
NB:
Have you read the reference manual ("doc/ref.html")?
Have you searched the mailing list archive (www.open.com.au/archives/
radiator)?
Have you had a quick look on Google (www.google.com)?
Have you included a copy of your configuration file (no secrets),
together with a trace 4 debug showing what is happening?
--
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
-
CATool: Private Certificate Authority for Unix and Unix-like systems.
--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.
More information about the radiator
mailing list