[RADIATOR] Radiator SQL Access

[Carlos Parada]

Hi all,

I'm trying to use an SQL database to store and handle some data, namely the SQL IP allocation service.
However, I'm facing the following problem "Could not connect to SQL database with DBI->connect" (see confs and logs).
I have tried to use other functionalities with an SQL database, such as accounting, and the problem is the same.
I have created a small perl script (see below) using the same access data and it works perfectly.
I've tried to sniff some traffic using the tethereal application and nothing can be seen.
Any tips?


Conf:
<AddressAllocator SQL>
        Identifier dhcp_sql
        DBSource "dbi:Pg:dbname=aaa;host=10.112.25.65;port=5432"
        DBUsername aaa
        DBAuth aaa
        DefaultLeasePeriod 86400        # 1 day
        LeaseReclaimInterval 86400      # 1 day
        FindQuery SELECT TIME_STAMP, YIADDR, SUBNETMASK, DNSSERVER from RADPOOL \
WHERE POOL='%0' AND STATE=0 ORDER BY TIME_STAMP LIMIT 1;
        AllocateQuery UPDATE RADPOOL SET STATE=1,TIME_STAMP=%0,\
EXPIRY=%1, USERNAME=%2 WHERE YIADDR='%3' AND TIME_STAMP = %4;
        CheckPoolQuery SELECT STATE FROM RADPOOL WHERE YIADDR='%0' LIMIT 1 
...
</AddressAllocator>
 

Logs:
Mon Feb  1 10:29:38 2010: DEBUG: Checking address 192.1.1.1
Mon Feb  1 10:29:38 2010: DEBUG: Query is: 'SELECT STATE FROM RADPOOL WHERE YIADDR='192.1.1.1' LIMIT 1': 
Mon Feb  1 10:29:38 2010: ERR: Could not connect to SQL database with DBI->connect "dbi:Pg:dbname=aaa;host=10.112.25.65;port=5432", aaa, aaa:  
Mon Feb  1 10:29:38 2010: ERR: Could not connect to any SQL database. Request is ignored. Backing off for 600 seconds
Mon Feb  1 10:29:38 2010: DEBUG: Checking address 192.1.2.0


Script:
use DBI;

my $datasrc = 'dbi:Pg:dbname=aaa;host=10.112.25.65;port=5432';
my $username = 'aaa';
my $password = 'aaa';

my $dbh = DBI->connect($datasrc, $username, $password);
if ( !defined $dbh )
{
        print "Could not connect to database: $DBI::errstr\n";
        exit;
} else {
        print "Connected to database\n";
        exit;
}



Regards,
  
Carlos Parada 

-----Original Message-----
From: radiator-bounces at open.com.au [mailto:radiator-bounces at open.com.au] On Behalf Of Hugh Irvine
Sent: domingo, 31 de Janeiro de 2010 21:40
To: Lay_C
Cc: radiator at open.com.au
Subject: Re: [RADIATOR] Logging to MSSQL-DB with SessionDatabase SQL


Hello -

I will need to see the complete configuration file and a trace 4 debug including the startup messages showing what is happening.

What hardware/software platform are you running on? what version of Radiator? what version of Perl?

regards

Hugh


On 31 Jan 2010, at 21:18, Lay_C wrote:

> 
> Hello,
> 
> I have the task to log all connections to a database. It's only one table
> with the following columns:
> SessionID, Username, IPv4Address, IPv6Address, AcctTerminateCause,
> SessionStart, SessionStop, AcctInputPackets, AcctOutputPackets
> 
> I can connect to my dsn via isql, but I think it doesn't work from the
> radius.cfg
> 
> The important excerpt looks like this:
> 
> <SessionDatabase SQL>
>        Identifier SQLLog
>        DBSource   dbi:ODBC:RADIUS
>        DBUsername username
>        DBAuth     password
>        AddQuery   insert into Session (SessionID, Username, IPv4Address,
> IPv6Address, AcctTerminateCause, SessionStart, SessionStop,
> AcctInputPackets, AcctOutputPackets) values (%{Acct-Session-ID}, %u,
> %{Framed-IP-Address}, '0', 'running', now(), -1, '0', '0')
>        DeleteQuery UPDATE Session SET SessionStop = now(), AcctInputPackets
> = %{Acct-Input-Packets},AcctOutputPackets = %{Acct-Output-Packets} WHERE
> Username = %u AND SessionID = %{Acct-Session-ID}
> </SessionDatabase>
> 
> My Idea was that this way I can update the Rows when the Session is closed.
> But when testing the server with "radpwtst -trace -nostop -chap -user
> Username -password Password -framed_ip_address 123.123.123.123 -secret
> testtest" It doesn't write to the database.
> I set trace in the radius.cfg to 5 but I can't see any error.
> 
> -- 
> View this message in context: http://old.nabble.com/Logging-to-MSSQL-DB-with-SessionDatabase-SQL-tp27361270p27361270.html
> Sent from the Radiator - General mailing list archive at Nabble.com.
> 
> _______________________________________________
> radiator mailing list
> radiator at open.com.au
> http://www.open.com.au/mailman/listinfo/radiator



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.
Includes support for reliable RADIUS transport (RadSec),
and DIAMETER translation agent.
-
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.



_______________________________________________
radiator mailing list
radiator at open.com.au
http://www.open.com.au/mailman/listinfo/radiator