(RADIATOR) Error in AddressAllocator SQL

William Hernandez whr at essnet.com
Wed Jan 21 07:29:46 CST 2004


Hello Andrew,

Yes, there is a problem in FreeTDS which is why I have to use the
older:
DBD-Sybase-0.94
Freetds-0.53

The curious thing is that the problem  occurs only in the
AddressAllocator SQL clause. My AuthBy SQL clauses which also
access the same database all work.

Regards,
William


-----Original Message-----
From: owner-radiator at open.com.au
[mailto:owner-radiator at open.com.au] On Behalf Of Andrew Stevenson
Sent: Tuesday, January 20, 2004 7:16 PM
To: Hugh Irvine
Cc: William Hernandez; radiator at open.com.au
Subject: Re: (RADIATOR) Error in AddressAllocator SQL


On Tue, 20 Jan 2004, Hugh Irvine wrote:

> I think you have a problem with your configuration file - it
appears 
> the AddressAllocator is being called in a loop.

I seem to be incurring the same problem. I'm using freetds 0.61
against MSSQL. I notice William is running FreeTDS also and I
found http://article.gmane.org/gmane.comp.db.tds.freetds/1721
which makes me wonder if its a FreeTDS problem.

> It is really _much_ easier if I can see the configuration file
and a 
> complete trace 4 debug.

Mine is below - slightly trimmed for brevity as the address
allocator gets called 20 times producing almost identical log
messages.

Tue Jan 20 18:27:48 2004: DEBUG: Packet dump:
*** Received from 10.0.0.1 port 47058 ....
Code:       Access-Request
Identifier: 10
Authentic:  blah
Attributes:
        Framed-Protocol = PPP
        NAS-Port-Type = Virtual
        NAS-Port = 12
        Calling-Station-Id = "292645069"
        Called-Station-Id = "452320198336659"
        Service-Type = Framed
        NAS-IP-Address = 10.0.0.2
        NAS-Identifier = "blah.blah.com.au"
        User-Password = "Z<129><131>q<11>C<183>{<192><148>5<164>"
        User-Name = "andrews at blah"
        Proxy-State =
E60F14F586A9BD69B0A35A6B2A0C42CE520979F562E444CB2

Tue Jan 20 18:27:48 2004: DEBUG: Handling request with Handler
'Request-Type=Access-Request' Tue Jan 20 18:27:48 2004: DEBUG:
Deleting session for andrews at blah, 10.0.0.2, 12
Tue Jan 20 18:27:48 2004: DEBUG: do query is: 'DELETE FROM
blah_dial_current_sessions WHERE        nas_ip = '10.0.0.2' AND
nas_port = '12'':

Tue Jan 20 18:27:48 2004: DEBUG: Handling with Radius::AuthSQL
Tue Jan 20 18:27:48 2004: DEBUG: Handling with Radius::AuthSQL:
Tue Jan 20 18:27:48 2004: DEBUG: Query is: 'SELECT password,
attributes, max_sessions FROM blah_dial_users WHERE username =
'andrews' AND realm = 'blah' AND '452320198336659' LIKE '%' +
allowed_called_station_id AND active_flag = 1':

Tue Jan 20 18:27:48 2004: DEBUG: Radius::AuthSQL looks for match
with andrews at blah Tue Jan 20 18:27:48 2004: DEBUG: Query is:
'SELECT nas_ip, nas_port, session_id FROM
blah_dial_current_sessions WHERE username = 'andrews' AND realm =
'blah' AND '452320198336659' LIKE '%' + called_station_id':

Tue Jan 20 18:27:48 2004: DEBUG: Radius::AuthSQL ACCEPT:
Tue Jan 20 18:27:48 2004: DEBUG: Handling with
Radius::AuthDYNADDRESS Tue Jan 20 18:27:48 2004: DEBUG: Query is:
'SELECT TOP 1 last_changed, yiaddr, netmask, dns FROM
blah_dial_address_pool WHERE pool = 'pool0' AND inuse_flag = 0
ORDER BY last_changed':

Tue Jan 20 18:27:48 2004: DEBUG: do query is: 'UPDATE
blah_dial_address_pool SET inuse_flag = 1, last_changed =
1074583668, username = 'andrews', realm = 'blah',
called_station_id = '452320198336659' WHERE yiaddr =
'203.111.65.49'':

Tue Jan 20 18:27:48 2004: DEBUG: Query is: 'SELECT TOP 1
last_changed, yiaddr, netmask,dns FROM blah_dial_address_pool
WHERE pool = 'pool0' AND inuse_flag = 0 ORDER BYlast_changed':

Tue Jan 20 18:27:48 2004: DEBUG: do query is: 'UPDATE
blah_dial_address_pool SET inuse_flag = 1, last_changed =
1074583668, username = 'andrews', realm = 'blah',
called_station_id = '452320198336659' WHERE yiaddr =
'203.111.65.50'':

Tue Jan 20 18:27:48 2004: DEBUG: Query is: 'SELECT TOP 1
last_changed, yiaddr, netmask,dns FROM blah_dial_address_pool
WHERE pool = 'pool0' AND inuse_flag = 0 ORDER BYlast_changed':

Tue Jan 20 18:27:48 2004: DEBUG: do query is: 'UPDATE
blah_dial_address_pool SET inuse_flag = 1, last_changed =
1074583668, username = 'andrews', realm = 'blah',
called_station_id = '452320198336659' WHERE yiaddr =
'203.111.65.51'':

Tue Jan 20 18:27:48 2004: DEBUG: Query is: 'SELECT TOP 1
last_changed, yiaddr, netmask,dns FROM blah_dial_address_pool
WHERE pool = 'pool0' AND inuse_flag = 0 ORDER BYlast_changed':

Tue Jan 20 18:27:48 2004: DEBUG: do query is: 'UPDATE
blah_dial_address_pool SET inuse_flag = 1, last_changed =
1074583668, username = 'andrews', realm = 'blah',
called_station_id = '452320198336659' WHERE yiaddr =
'203.111.65.52'':


.
.
.
and so on for quite a while
.
.
.

Tue Jan 20 18:27:49 2004: DEBUG: Query is: 'SELECT TOP 1
last_changed, yiaddr, netmask,dns FROM blah_dial_address_pool
WHERE pool = 'pool0' AND inuse_flag = 0 ORDER BY last_changed':

Tue Jan 20 18:27:49 2004: DEBUG: do query is: 'UPDATE
blah_dial_address_pool SET inuse_flag = 1, last_changed =
1074583669, username = 'andrews', realm = 'blah',
called_station_id = '452320198336659' WHERE yiaddr =
'203.111.65.68'':

Tue Jan 20 18:27:49 2004: INFO: Access rejected for andrews at blah:
Too many simultaneous address requests Tue Jan 20 18:27:49 2004:
WARNING: No such attribute PoolHint Tue Jan 20 18:27:49 2004:
DEBUG: Packet dump:
*** Sending to 10.0.0.1 port 47058 ....
Code:       Access-Reject
Identifier: 10
Authentic:  blah
Attributes:
        Ascend-Client-Primary-DNS = 203.111.0.10
        Ascend-Client-Secondary-DNS = 202.53.36.190
        PoolHint = 'pool0'
        Reply-Message = "Request Denied"
        Proxy-State =
E60F14F586A9BD69B0A35A6B2A0C42CE520979F562E444CB2


Relevant parts of the config....

<AddressAllocator SQL>
    Identifier  InternetDial
    DBSource    dbi:Sybase:server=BLAH1;database=Radius2
    DBUsername  XXXXXX
    DBAuth      XXXXXX
    DBSource    dbi:Sybase:server=BLAH2;database=Radius2
    DBUsername  XXXXXX
    DBAuth      XXXXXX

    DefaultLeasePeriod  1209600
    #DefaultLeasePeriod 0

    FindQuery                           \
        SELECT TOP 1                    \
            last_changed,               \
            yiaddr,                     \
            netmask,                    \
            dns                         \
        FROM                            \
            blah_dial_address_pool \
        WHERE                           \
            pool = %0 AND               \
            inuse_flag = 0              \
        ORDER BY                        \
            last_changed

    AllocateQuery                                       \
        UPDATE                                          \
            blah_dial_address_pool                 \
        SET                                             \
            inuse_flag = 1,                             \
            last_changed = %0,                          \
            username = '%U',                            \
            realm = '%R',                               \
            called_station_id = '%{Called-Station-Id}'  \
        WHERE                                           \
            yiaddr = '%3'

    CheckPoolQuery                                      \
        SELECT                                          \
            inuse_flag                                  \
        FROM                                            \
            blah_dial_address_pool                 \
        WHERE                                           \
            yiaddr = '%0'

    AddAddressQuery                                     \
        INSERT INTO comindico_dial_address_pool (       \
            inuse_flag,                                 \
            last_changed,                               \
            pool,                                       \
            yiaddr,                                     \
            netmask,                                    \
            dns                                         \
        ) VALUES (                                      \
            0,                                          \
            '%t',                                       \
            '%0',                                       \
            '%1',                                       \
            '%2',                                       \
            '%3'                                        \
        )

    DeallocateQuery                                     \
        UPDATE                                          \
            comindico_dial_address_pool                 \
        SET                                             \
            inuse_flag = 1,                             \
            last_changed = '%t',                        \
        WHERE                                           \
            yiaddr = '%0'

    # this is done by an IS stored procedure
    ReclaimQuery                                        \
        SELECT 0

    <AddressPool pool0>
        DNSServer 203.111.0.10
        Range 203.111.65.0/24
        Range 203.111.67.0/24
    </AddressPool>

</AddressAllocator>

<Handler Request-Type=Access-Request>
    # record accounting info in file in case IS loose it
    AcctLogFileName %L/details-%Y%m%d

    # keep going through the clauses so we get to the dynamic
address
    # allocation clause
    AuthByPolicy ContinueWhileAccept

    # how to determine if we should let this user in
    <AuthBy SQL>
        # where to find the DB (tried in order)
        DBSource    dbi:Sybase:server=BLAH1;database=Radius2
        DBUsername  XXXXXX
        DBAuth      XXXXXX
        DBSource    dbi:Sybase:server=BLAH2;database=Radius2
        DBUsername  XXXXXX
        DBAuth      XXXXXX

        # don't look for a default user entry (there isn't one to
find)
        NoDefault

        # the SQL query to find the user's details
        AuthSelect                                      \
            SELECT                                      \
                password,                               \
                attributes,                             \
                max_sessions                            \
            FROM blah_dial_users WHERE             \
                username = '%U' AND                     \
                realm = '%R' AND                        \
                '%{Called-Station-Id}' LIKE '%' +
allowed_called_station_id AND \
                active_flag = 1

        # what to do with the details we have found
        AuthColumnDef   0, User-Password, check
        AuthColumnDef   1, GENERIC, reply
        AuthColumnDef   2, Simultaneous-Use, check

        AddToReplyIfNotExist    Ascend-Client-Primary-DNS =
203.111.0.10, \
                                Ascend-Client-Secondary-DNS =
202.53.36.190, \
                                PoolHint = 'pool0'

    </AuthBy>

    <AuthBy DYNADDRESS>
        AddressAllocator    InternetDial

        MapAttribute    yiaddr, Framed-IP-Address
    </AuthBy>

    StripFromReply  PoolHint
</Handler>

Thanks,

Andrew
===
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.

===
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