(RADIATOR) Re: Response from a stored procedure in MSSQL

[Bulent Gulcan]

Hello Hugh,

We are using stored procedure only for authentication. We run two radius
instances, one for auth and one for acct.
Our Radius auth config file and trace 4 debug are below.

Thanks for your help, regards.


----------radius-auth.cfg----------------

# radius-auth.cfg
LogDir          /var/log/radius
DbDir           /etc/radiator
Trace  4
AuthPort 1645
AcctPort

<Client 127.0.0.1>
        Secret  mysecret
        DupInterval 0
</Client>
<Client 203.63.154.1>
        Secret  abc123
        DupInterval 0
</Client>

<Realm DEFAULT>
<AuthBy SQL>
       DBSource        dbi:Sybase:server=xxx;database=yyy
       DBUsername    dbuser
       DBAuth           dbpass
       DefaultSimultaneousUse 1

AuthSelect exec GetSubscriber '%n'

        AuthColumnDef   0,User-Password,check
        AuthColumnDef   1,ValidFrom,check
        AuthColumnDef   2,Expiration,check
        AuthColumnDef   3,GENERIC,check
        AuthColumnDef   4,GENERIC,reply
       AuthColumnDef   5,Session-Timeout,reply

        NoDefault
</AuthBy>

<SessionDatabase SQL>
        Identifier SDB1
        DBSource        dbi:Sybase:server=xxx;database=yyy
       DBUsername     dbuser
       DBAuth         dbpass

        DeleteQuery delete from session_db \
          where username='%n' and \
          nas_id='%N' and nas_port=%{NAS-Port}

        CountQuery select nas_id, nas_port, session_id from session_db where
username='%n'
</SessionDatabase>

</Realm>
--------------------Config end-------------

:
-----------------Trace 4 debug -------------------------------
Fri Apr 16 17:39:27 2004: DEBUG: Packet dump:
*** Received from 127.0.0.1 port 32817 ....
Code:       Access-Request
Identifier: 206
Authentic:  1234567890123456
Attributes:
        User-Name = "user1"
        Service-Type = Framed
        NAS-IP-Address = 203.63.154.1
        NAS-Port = 1234
        Called-Station-Id = "123456789"
        Calling-Station-Id = "987654321"
        NAS-Port-Type = Async
        User-Password =
"<136><252>n<159><222>+<4><246><188>8<9><160><216>}x<153>"

Fri Apr 16 17:39:27 2004: DEBUG: Handling request with Handler
'Realm=DEFAULT'
Fri Apr 16 17:39:27 2004: DEBUG: SDB1 Deleting session for user1,
203.63.154.1, 1234
Fri Apr 16 17:39:27 2004: DEBUG: do query is: 'delete from session_db where
username='user1' and nas_id='203.63.154.1' and nas_port=1234':

Fri Apr 16 17:39:27 2004: DEBUG: Handling with Radius::AuthSQL
Fri Apr 16 17:39:27 2004: DEBUG: Handling with Radius::AuthSQL:
Fri Apr 16 17:39:27 2004: DEBUG: Query is: 'exec GetSubscriber 'user1'':

--------------------Trace 4 End-------------------------------------------


----- Original Message -----
From: "Hugh Irvine" <hugh at open.com.au>
To: "Bulent Gulcan" <bulent.gulcan at is.net.tr>
Cc: <radiator at open.com.au>; <radius-support at open.com.au>
Sent: Saturday, April 17, 2004 2:16 AM
Subject: Re: Response from a stored procedure in MSSQL


>
> Hello Bulent -
>
> I will need to see a copy of your configuration file and a trace 4
> debug from Radiator showing what is happening.
>
> regards
>
> Hugh
>
>
> On 16 Apr 2004, at 23:21, Bulent Gulcan wrote:
>
> > Hello All,
> >
> > We are trying to authenticate from a stored procedure in MSSql server.
> > In our radius.cfg file the <AuthBy> clause is below.
> >
> > <AuthBy SQL>
> > ....
> > AuthSelect exec GetSubscriber %0
> >
> >         AuthColumnDef   0,User-Password,check
> >         AuthColumnDef   1,ValidFrom,check
> >         AuthColumnDef   2,Expiration,check
> >         AuthColumnDef   3,GENERIC,check
> >         AuthColumnDef   4,GENERIC,reply
> >         AuthColumnDef   5,Session-Timeout,reply
> > ....
> > </AuthBy>
> >
> > At MSSql server side, the stored procedure GetSubscriber works fine
> > and we
> > can get a result set.
> >
> > But at radiator side, we can not get a response from MsSql server for
> > the
> > stored procedure :
> > ----------
> > radpwtst -user username -password passwd -service_type Framed
> > -iterations 1
> > sending Access-Request...
> > No reply
> > sending Accounting-Request Start...
> > OK
> > sending Accounting-Request Stop...
> > OK
> > -----------.
> >
> > Do we have to add some config parameter into <AuthBy> clause to get the
> > result set of stored procedure ?
> >
> > Could you please help us ?
> >
> > Thanks for your help, regards.
> >
> > Bulent Gulcan
> > Is Net A.S.
> > Turkey
> >
> >
>
> NB: 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.