[RADIATOR] AcctInsertQuery for Authby RADIUS
Michael
ringo at vianet.ca
Fri Feb 18 21:53:19 CST 2011
hmm.. ya, it looks like the AuthBy SQL is being called, but not executing the
insert statment.
i seem to remember running into this before. I remember something about how
radiator didn't execute the insert statement unless a AccountingTable value was
configured, or maybe AcctColumnDef.
So, try adding this even though it's not needed since your sql insert statment
has the table name:
AccountingTable radiator_insert_accounting
..and also try configuring an AcctColumnDef:
AcctColumnDef invalid,invalid
This AcctColumnDef of course wont work, but just see what it does in your debug.
Michael
On Sat, 19 Feb 2011, Jeffrey Lee wrote:
> here's the debug log... i execute radpwtst -user jeff at abc -password ****
>
>
>
> Sat Feb 19 11:09:47 2011: DEBUG: Packet dump:
> *** Received from 127.0.0.1 port 56818 ....
> Code: Access-Request
> Identifier: 2
> Authentic: <149>G<148><203>z<228>]<232><150><158><219><252><31><128>WP
> Attributes:
> User-Name = "jeff at abc"
> 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 = Async
> User-Password = <22>]<179><134><136><216><235>Y<253><238>+<30><161><249>
> a<235>
>
> Sat Feb 19 11:09:47 2011: DEBUG: Handling request with Handler 'Realm=abc', Iden
> tifier ''
> Sat Feb 19 11:09:47 2011: DEBUG: Rewrote user name to jeff
> Sat Feb 19 11:09:47 2011: DEBUG: Deleting session for jeff at abc, 203.63.154.1, 1
> 234
> Sat Feb 19 11:09:47 2011: DEBUG: Handling with Radius::AuthSQL:
> Sat Feb 19 11:09:47 2011: DEBUG: AuthBy SQL result: IGNORE, Ignored due to Ignor
> eAuthentication
> Sat Feb 19 11:09:47 2011: DEBUG: Handling with Radius::AuthRADIUS
> Sat Feb 19 11:09:47 2011: DEBUG: AuthBy RADIUS creates new local socket '0.0.0.0
> :0' for sending requests
> Sat Feb 19 11:09:47 2011: DEBUG: Packet dump:
> *** Sending to 192.168.10.103 port 1812 ....
> Code: Access-Request
> Identifier: 1
> Authentic: <149>G<148><203>z<228>]<232><150><158><219><252><31><128>WP
> Attributes:
> User-Name = "jeff"
> 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 = Async
> User-Password = <22>]<179><134><136><216><235>Y<253><238>+<30><161><249>
> a<235>
>
> Sat Feb 19 11:09:47 2011: DEBUG: AuthBy RADIUS result: IGNORE,
> Sat Feb 19 11:09:47 2011: DEBUG: Received reply in AuthRADIUS for req 1 from 192
> .168.10.103:1812
> Sat Feb 19 11:09:47 2011: DEBUG: Packet dump:
> *** Received from 192.168.10.103 port 1812 ....
> Code: Access-Accept
> Identifier: 1
> Authentic: 1<234><130><212>=p<140><200><128><199><228><139>c<1><<148>
> Attributes:
> Class = "<233><183>d=9<191><185>]<23><236>"Gl<249>"Z"
>
> Sat Feb 19 11:09:47 2011: DEBUG: Access accepted for jeff
> Sat Feb 19 11:09:47 2011: DEBUG: do query is: 'INSERT INTO SuccessfulRequests(Re
> alm, UserName, Password, NASIPAddress, ReplyMessage, CallerID) values('', 'jeff'
> , 'meyf', '203.63.154.1', '', '987654321')':
> Sat Feb 19 11:09:48 2011: DEBUG: Packet dump:
> *** Sending to 127.0.0.1 port 56818 ....
> Code: Access-Accept
> Identifier: 2
> Authentic: "R<245><11><194>%<230><147>T<153><171><31><251><175>K<200>
> Attributes:
> Class = "<233><183>d=9<191><185>]<23><236>"Gl<249>"Z"
>
> Sat Feb 19 11:09:48 2011: DEBUG: Packet dump:
> *** Received from 127.0.0.1 port 56818 ....
> Code: Accounting-Request
> Identifier: 3
> Authentic: <194><140><153><14>v<154><210> <227><204>;v7<148>(<172>
> Attributes:
> User-Name = "jeff at abc"
> Service-Type = Framed-User
> NAS-IP-Address = 203.63.154.1
> NAS-Identifier = "203.63.154.1"
> NAS-Port = 1234
> NAS-Port-Type = Async
> Acct-Session-Id = "00001234"
> Acct-Status-Type = Start
> Called-Station-Id = "123456789"
> Calling-Station-Id = "987654321"
> Acct-Delay-Time = 0
> Class = "<233><183>d=9<191><185>]<23><236>"Gl<249>"Z"
>
> Sat Feb 19 11:09:48 2011: DEBUG: Handling request with Handler 'Realm=abc', Iden
> tifier ''
> Sat Feb 19 11:09:48 2011: DEBUG: Rewrote user name to jeff
> Sat Feb 19 11:09:48 2011: DEBUG: Adding session for jeff at abc, 203.63.154.1, 123
> 4
> Sat Feb 19 11:09:48 2011: DEBUG: Handling with Radius::AuthSQL:
> Sat Feb 19 11:09:48 2011: DEBUG: Handling accounting with Radius::AuthSQL
> Sat Feb 19 11:09:48 2011: DEBUG: AuthBy SQL result: ACCEPT,
> Sat Feb 19 11:09:48 2011: DEBUG: Accounting accepted
> Sat Feb 19 11:09:48 2011: DEBUG: Packet dump:
> *** Sending to 127.0.0.1 port 56818 ....
> Code: Accounting-Response
> Identifier: 3
> Authentic: <5><215><174><207>cco+<206><29>T<227><170><221><214><141>
> Attributes:
>
> Sat Feb 19 11:09:48 2011: DEBUG: Packet dump:
> *** Received from 127.0.0.1 port 56818 ....
> Code: Accounting-Request
> Identifier: 4
> Authentic: K=<18>v<202><162><138>^<216>6#<9><211>5<176><7>
> Attributes:
> User-Name = "jeff at abc"
> Service-Type = Framed-User
> NAS-IP-Address = 203.63.154.1
> NAS-Identifier = "203.63.154.1"
> NAS-Port = 1234
> NAS-Port-Type = Async
> Acct-Session-Id = "00001234"
> Acct-Status-Type = Stop
> Called-Station-Id = "123456789"
> Calling-Station-Id = "987654321"
> Acct-Delay-Time = 0
> Acct-Session-Time = 1000
> Acct-Input-Octets = 20000
> Acct-Output-Octets = 30000
> Class = "<233><183>d=9<191><185>]<23><236>"Gl<249>"Z"
>
> Sat Feb 19 11:09:48 2011: DEBUG: Handling request with Handler 'Realm=abc', Iden
> tifier ''
> Sat Feb 19 11:09:48 2011: DEBUG: Rewrote user name to jeff
> Sat Feb 19 11:09:48 2011: DEBUG: Deleting session for jeff at abc, 203.63.154.1, 1
> 234
> Sat Feb 19 11:09:48 2011: DEBUG: Handling with Radius::AuthSQL:
> Sat Feb 19 11:09:48 2011: DEBUG: Handling accounting with Radius::AuthSQL
> Sat Feb 19 11:09:48 2011: DEBUG: AuthBy SQL result: ACCEPT,
> Sat Feb 19 11:09:48 2011: DEBUG: Accounting accepted
> Sat Feb 19 11:09:48 2011: DEBUG: Packet dump:
> *** Sending to 127.0.0.1 port 56818 ....
> Code: Accounting-Response
> Identifier: 4
> Authentic: <27><234><181><129><170><206>.y<146><166>WW<218><250><188><190>
> Attributes:
>
>
>
>
>
> There's another problem I see.... when I duplicated the <Handler>
> config and rename the realm to def, then I test authentication
> again.... the realm=def could not be found. this is another issue...
>
>
>
>
>
> On Sat, Feb 19, 2011 at 2:26 AM, Michael <ringo at vianet.ca> wrote:
>> was there even an accounting record sent? there's not enough debug here to
>> show what type of request this was, and it's only one, if there was an auth
>> request and a accounting request, there should be at least two requests in
>> your debug.
>>
>> I think you'd need to show the list more debug.
>>
>> Michael
>>
>>
>> On 11-02-17 08:25 PM, Jeffrey Lee wrote:
>>>
>>> see below for the<Handler> config
>>>
>>> i have 2 RADIUS server setup here.... RADIUS A is this radiator,
>>> RADIUS B is another RADIUS for testing proxy-realms.
>>> On RADIUS A, here's the output on screen:
>>>
>>> Fri Feb 18 12:18:18 2011: DEBUG: Handling request with Handler
>>> 'Realm=ABC', Identifier ''
>>> Fri Feb 18 12:18:18 2011: DEBUG: Rewrote user name to jeff
>>> Fri Feb 18 12:18:18 2011: DEBUG: Adding session for jeff at ABC,
>>> 203.63.154.1,1234
>>> Fri Feb 18 12:18:18 2011: DEBUG: Handling with Radius::AuthSQL:
>>> Fri Feb 18 12:18:18 2011: DEBUG: Handling accounting with Radius::AuthSQL
>>> Fri Feb 18 12:18:18 2011: DEBUG: AuthBy SQL result: ACCEPT,
>>> Fri Feb 18 12:18:18 2011: DEBUG: Accounting accepted
>>>
>>> >> it seems as though the AcctInsertQuery was called but there's
>>> no accounting record captured. if the stored procedure generated an
>>> error, will radiator capture and display the error message on screen?
>>>
>>>
>>> On RADIUS B, it seems that the proxy-forwarded authentication requests
>>> is received but not the accounting requests.
>>>
>>>
>>> On the database (configured for radiator), there are no accounting
>>> records captured, and the sessiondatabase is not triggered.
>>>
>>>
>>> ... what is wrong with the config?
>>>
>>>
>>>
>>>
>>> #########################################################################################################
>>>
>>> <Handler Realm=ABC>
>>> AcctLogFileName %D/detail
>>> AuthByPolicy ContinueWhileIgnore
>>> RewriteUsername s/^([^@]+).*/$1/
>>> MaxSessions 1
>>>
>>> <AuthBy SQL>
>>> IgnoreAuthentication
>>> HandleAcctStatusTypes Start,Stop,Alive
>>>
>>> # i've created a database called radiator with username and
>>> password
>>> as radiator
>>> DBAuth radiator
>>> DBSource dbi:ODBC:radiator
>>> DBUsername radiator
>>>
>>> # i'm using a sql server stored procedure to capture the
>>> accounting records
>>> AcctInsertQuery exec radiator_insert_accounting
>>> '%{User-Name}',
>>> '%{Acct-Session-Id}', '%{Acct-Session-Time}', '%{Acct-Input-Octets}',
>>> '%{Acct-Output-Octets}', '%{Framed-IP-Address}',
>>> '%{Calling-Station-Id}', '%{Called-Station-Id}', '%{NAS-Identifier}',
>>> '%{NAS-IP-Address}', '%{NAS-Port}', '', '%{Acct-Status-Type}',
>>> '%{Acct-Terminate-Cause}', '%R'
>>> </AuthBy>
>>>
>>> <AuthBy RADIUS>
>>> AcctPort 1813
>>> AuthPort 1812
>>> CacheOnNoReply 1
>>> CachePasswordExpiry 86400
>>> LocalAddress 0.0.0.0
>>> MaxFailedGraceTime 0
>>> MaxFailedRequests 1
>>> OutPort 0
>>> PasswordPrompt password
>>> Retries 3
>>> RetryTimeout 5
>>> Secret mysecret
>>>
>>> <Host 192.168.10.103>
>>> AcctPort 1813
>>> AuthPort 1812
>>> BogoMips 1
>>> LocalAddress 0.0.0.0
>>> MaxFailedGraceTime 0
>>> MaxFailedRequests 1
>>> OutPort 0
>>> Retries 3
>>> RetryTimeout 15
>>> Secret mysecret
>>> </Host>
>>> </AuthBy>
>>>
>>> # all success/failed requests logs are captured
>>> <AuthLog SQL>
>>> DBAuth radiator
>>> DBSource dbi:ODBC:radiator
>>> DBUsername radiator
>>>
>>> LogSuccess 1
>>> SuccessQuery insert into radpostauth (user,pass,reply)
>>> values(%2,%3,'Access-Accept')
>>>
>>> LogFailure 1
>>> FailureQuery insert into radpostauth (user,pass,reply)
>>> values(%2,%3,'Access-Reject')
>>> </AuthLog>
>>> </Handler>
>>>
>>>
>>> #########################################################################################################
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Thu, Feb 17, 2011 at 10:49 AM, Hugh Irvine<hugh at open.com.au> wrote:
>>>>
>>>> Hello Jeff -
>>>>
>>>> You should not mix Handlers and Realms in the same configuration file, as
>>>> Realms are always evaluated before Handlers.
>>>>
>>>> If you are going to change from Realms to Handlers, I suggest you use
>>>> separate Handlers for authentication and accounting:
>>>>
>>>> …..
>>>>
>>>> # accounting
>>>>
>>>> <Handler Request-Type = Accounting-Request, User-Name = /..../>
>>>>
>>>> ……
>>>>
>>>> </Handler>
>>>>
>>>> # authentication
>>>>
>>>> <Handler User-Name = /.../>
>>>>
>>>> …..
>>>>
>>>> </Handler>
>>>>
>>>> …..
>>>>
>>>> FYI - I also suggest you use "User-Name = /.../" instead of "Realm =
>>>> /..../" as you then have greater control with the regexp.
>>>>
>>>> regards
>>>>
>>>> Hugh
>>>>
>>>>
>>>> On 17 Feb 2011, at 10:03, Jeffrey Lee wrote:
>>>>
>>>>> hi Christian, thanks for the suggestion. You're right, your suggestion
>>>>> is the simplest to implement, neat and easy to maintain.
>>>>> whilst, the method suggested by Michael and Remo allows the AuthBy to
>>>>> be reused by other realms that need the same processing policy.
>>>>>
>>>>> if i have a handler/realm tag that uses regex?
>>>>> for example, i have<Realm ^abc(def)?\//i> (which should process any
>>>>> incoming requests with abc/user or abcdef/user, how will this appear
>>>>> in the handler tag? will it be<Handler realm=^abc(def)?\//i> , or
>>>>> this is not possible and it must be for specific matches?
>>>>>
>>>>>
>>>>>
>>>>> On Thu, Feb 17, 2011 at 8:39 AM, Christian Kratzer<ck at cksoft.de> wrote:
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> On Wed, 16 Feb 2011, Jeffrey Lee wrote:
>>>>>>
>>>>>>> I tried adding<AuthBy SQL> after<AuthBy RADIUS> but as soon as
>>>>>>> <AuthBy RADIUS> is executed,<AuthBy SQL> will not be executed.
>>>>>>
>>>>>> <AuthBy RADIUS> will always return an ignore as it dispatches the
>>>>>> radius request and processes the answer asyncronously.
>>>>>>
>>>>>>> Can you actually place<AuthBy SQL> within a<AuthBy RADIUS>?
>>>>>>
>>>>>> You can place both AuthBy below each other inside the handler
>>>>>> and set the appropriate AuthByPolicy (Manual Section 5.24.1) to do
>>>>>> what you want.
>>>>>>
>>>>>> You will not need an AuthBy GROUP for a simple case as a Handler
>>>>>> already
>>>>>> implements the same functionality as an AuthBy GROUP.
>>>>>>
>>>>>>> What I'm trying to achieve is to log the RADIUS accounting records
>>>>>>> locally (start, stop& alive) for realms that need to be authenticated
>>>>>>> by another RADIUS server. How can I achieve that?
>>>>>>
>>>>>> something like this should do the trick:
>>>>>>
>>>>>> <Handler Realm=foo>
>>>>>> AuthByPolicy ContinueWhileIgnore
>>>>>>
>>>>>> <AuthBy RADIUS>
>>>>>> ...
>>>>>> </AuthBy>
>>>>>>
>>>>>> <AuthBy SQL>
>>>>>> IgnoreAuthentication
>>>>>> ...
>>>>>> </AuthBy>
>>>>>>
>>>>>> </Handler>
>>>>>>
>>>>>> The<AuthBy RADIUS> will always proxy your requests and will return
>>>>>> ignore.
>>>>>>
>>>>>> The AuthBy SQL will be called but will only handle accounting as you
>>>>>> have
>>>>>> configured IgnoreAuthentication.
>>>>>>
>>>>>> There are many possible variations but I think above is the simplest.
>>>>>>
>>>>>> Greetings
>>>>>> Christian
>>>>>>
>>>>>> --
>>>>>> Christian Kratzer CK Software GmbH
>>>>>> Email: ck at cksoft.de Wildberger Weg 24/2
>>>>>> Phone: +49 7032 893 997 - 0 D-71126 Gaeufelden
>>>>>> Fax: +49 7032 893 997 - 9 HRB 245288, Amtsgericht
>>>>>> Stuttgart
>>>>>> Web: http://www.cksoft.de/ Geschaeftsfuehrer: Christian
>>>>>> Kratzer
>>>>>>
>>>>> _______________________________________________
>>>>> radiator mailing list
>>>>> radiator at open.com.au
>>>>> http://www.open.com.au/mailman/listinfo/radiator
>>>>
>>>>
>>> _______________________________________________
>>> radiator mailing list
>>> radiator at open.com.au
>>> http://www.open.com.au/mailman/listinfo/radiator
>>>
>>>
>>
>
More information about the radiator
mailing list