(RADIATOR) Rodopi & Radiator
Tim Jung
tjung at igateway.net
Thu Feb 6 19:50:30 CST 2003
Yes I can go in to "Enterprise Manager" for SQL 7.0 and pull up the stored
procedures and look at the procedure for both "Interface_VircomUsers" and
"Interface_VircomDetails". I see all the SQL commands, and what inputs are
needed for each of the procedures, etc.
I did try setting the "Timeout" to "Timeout 5" inside the <AuthBy RODOPI>
section and it didn't appear that it worked at all. It didn't timeout after
5 seconds and it didn't take my second request when I did another test
RADIUS request 10 seconds later.
I have included copies of both stored procedures below.
I am still stuck if anyone has any suggestions.
Tim Jung
System Admin
Internet Gateway
tjung at igateway.net
----- Original Message -----
From: "Mike McCauley" <mikem at open.com.au>
To: "Tim Jung" <tjung at igateway.net>; <radiator at open.com.au>
Cc: <hugh at open.com.au>; <dbirkbeck at ikano.com>
Sent: Thursday, February 06, 2003 6:49 PM
Subject: Re: (RADIATOR) Rodopi & Radiator
> Hello Tim,
>
> Thanks for the summary.
>
>
> On Fri, 7 Feb 2003 11:08 am, Tim Jung wrote:
> > Ok I called a buddy of mine who is more of an Microsoft dude that I am.
He
> > is an MSCE and MSCD. We confirmed that SQL is getting the command but
it
> > appears that either MS-SQL is waiting for some additional command or
> > something before it starts the actual query, since it shows the query
in
> > lists of tasks but it never ends or starts or whatever.
>
> OK, so the acess and permissions issue seems to be solved?
>
>
> >
> > Also I found out that Radiator does one of the strangest/poorest things
I
> > have ever seen in working with databases. Radiator depends on the
default
> > database that the login/account happens to be set to use. So when I
kept
> > getting unknown or missing stored procedure for the new "linux" account
and
> > the "sa" and all the other non-"rodopi" accounts it because those
accounts
> > didn't have their default database set to "AbacBill". When I set the
> > default database on the "linux" account to "AbacBill" it too then
started
> > to hang and cause Radiator to appear to lock up.
> >
> > Also all users who have any "public" access to the "AbacBill" database
have
> > permissions to execute the stored procedures of "Interface_VircomUsers"
and
> > "Interface_VircomDetails" which are the only 2 procedures that Radiator
> > uses in Rodopi. Also by default any owner of a database automatically
has
> > FULL access to the database the account owns. There would never be a
> > permissions/security issue with a database and that database owner.
> >
> > I have confirmed that it appears that the DBD::Sybase stuff and FreeTDS
> > stuff is working. It does send the login and password to the MS-SQL
server
> > correctly. It does send the request for the stored procedure to the
MS-SQL
> > server, but nothing appears to be happening after that for whatever
reason.
> > I show that MS-SQL does lock the database "AbacBill" and shows it got
the
> > command, but nothing happens. It also appears that Radiator doesn't
> > time-out or detect a problem thus appears to hang forever.
>
>
> Radiator should detect such a hung server.
> The default timeout is 60 seconds, but you can change this with the
Timeout
> parameter in your AuthBy RODOPI clause.
>
> >
> > If someone wants to look at either the Windows 2000 server or the Linux
box
> > let me know. I have terminal services setup on Windows 2000 and SSH on
the
> > Linux box.
>
> I think I would like to at least check the linux box, and use dbish to
test
> the behaviour of the SQL connection.
>
> I dont understand why the stored procedure would hang forever unless it
was
> trying to gain access to some resource that never became available. Are
you
> able to see the code of the Rodopi stored procedure?
>
> Cheers.
>
> >
> > So at this point I have no clue what to do to make this work. All I can
say
> > is that at this point it doesn't work for me basically.
> >
> > Tim Jung
> > System Admin
> > Internet Gateway
> > tjung at igateway.net
-------- Interface_VircomDetails --------------------
Create Procedure Interface_VircomDetails
@AcctSessionId varchar(64) = NULL,
@LogDateTime datetime = NULL,
@UserName varchar(128) = NULL,
@NasIPAddress varchar(16) = NULL,
@NasPortID numeric(18, 0) = NULL,
@ServiceType varchar(32) = NULL,
@FramedProtocol varchar(16) = NULL,
@FramedAddress varchar(16) = NULL,
@CallingStationID varchar(32) = NULL,
@NasIdentifier varchar(32) = NULL,
@AcctStatusType varchar(16) = NULL,
@AcctDelayTime numeric(18, 0) = NULL,
@AcctInputOctets numeric(18, 0) = NULL,
@AcctOutputOctets numeric(18, 0) = NULL,
@AcctSessionTime numeric(18, 0) = NULL,
@AcctInputPackets numeric(18, 0) = NULL,
@AcctOutputPackets numeric(18, 0) = NULL,
@AcctTerminateCause varchar (32) = NULL,
@NasPortType varchar(16) = NULL,
@ConnectInfo numeric(18, 0) = NULL,
@ClientPortDNIS varchar(128) = NULL
As
declare
@History smallint,
@TermCauseCode smallint
exec Radius_AcctTermCauseCode @AcctTerminateCause, @TermCauseCode output
select @History = UsageOnlineHistory
from SetupData2
if @AcctStatusType = 'Stop'
AND @AcctSessionID is NOT NULL
AND @UserName is NOT NULL
AND @LogDateTime is NOT NULL
AND isnull(@AcctSessionTime, 0) > 0
AND isnull(@AcctInputOctets, 0) + isnull(@AcctOutputOctets, 0) > 0
if ( datediff(day, dateadd(second, - at AcctSessionTime, @LogDateTime),
getdate() ) <= @History )
if NOT EXISTS ( select * from UsageData_OnlineHours
where AcctSessionId = @AcctSessionID AND UserName = @UserName )
begin
insert into UsageData_OnlineHours
( AcctSessionId,
UserName,
SessionDate,
AcctSessionTime,
ClientPortDNIS,
AcctInOutOctets,
AcctInOctets,
NasIPAddress,
FramedIPAddress,
CallingStationID,
AcctTerminateCause
)
values (@AcctSessionId,
@UserName,
dateadd(second, - at AcctSessionTime, @LogDateTime),
@AcctSessionTime,
isnull(@ClientPortDNIS, ''),
isnull(@AcctInputOctets, 0) + isnull(@AcctOutputOctets, 0),
isnull(@AcctInputOctets, 0),
isnull(@NasIPAddress, ''),
isnull(@FramedAddress, ''),
isnull(@CallingStationID, ''),
@TermCauseCode
)
return 0
end
return -1
GO
-----------------------
Interface_VircomUsers ------------------------------
CREATE Procedure Interface_VircomUsers
@UserName varchar(255) = NULL
As
set nocount on
declare
@ObjectID int,
@PlanID int,
@PlanInfoID int,
@LoginID int,
@TemplateLoginID int,
@RadiusProfileInfoID int,
@DefaultLoginID int,
@Password varchar(255),
@PasswordMask varchar(255),
@SessionTimeout int -- max dialin time, secs
declare
@ret int
set @ObjectID = NULL
set @Password = NULL
set @TemplateLoginID = NULL
select @RadiusProfileInfoID = RadiusProfileInfoID from SetupData
select @DefaultLoginID = RadiusDefaultLoginID from SetupData2
select @PasswordMask = ltrim(rtrim(PasswordMask)) from SetupData5
select @ObjectID = O.ObjectID,
@PlanID = O.PlanID,
@PlanInfoID = P.PlanInfoID,
@TemplateLoginID = I.TemplateLoginID
from ObjectValues as V
inner join Objects as O on O.ObjectID = V.ObjectID AND (O.DetailID = 1 OR
O.DetailID >= 1000) -- Dialin account OR Possible prepaid dialin ???
inner join Plans as P on P.PlanID = O.PlanID
inner join PlanInfo as I on I.PlanInfoId = P.PlanInfoID
inner join Providers as PR on PR.ProviderID = P.ProviderID
where V.Value_LPSTR = @UserName AND V.ItemID = 1
AND P.Closed = 0
AND not exists (
select *
from PlansSuspendState
where PlanID = P.PlanID
)
AND ( (PR.UseIn & 0x01) = 1 )
if ( @ObjectID is not NULL ) begin
if exists ( select *
from PlanUsageInfo as PUI
inner join UsageInfo as UI on UI.UsageInfoID = PUI.UsageInfoID
where PUI.PlanInfoID = @PlanInfoID
AND UI.UnitTypeID = 100
AND UI.Type = 1 ) begin
-- This is prepaid account
select @SessionTimeout = sum(UnitsAmount) * 3600
from PlansUsageInfoCredit as PUIC
inner join UsageInfo UI on UI.UsageInfoID = PUIC.UsageInfoID
where PUIC.PlanID = @PlanID
AND UI.UnitTypeID = 100 -- Online Hours
AND PUIC.EndTime is NULL
if ( isnull(@SessionTimeout, 0) <= 0 ) begin
-- The account's prepaid blocks are exhausted
goto lblAuthFailed
end
end
end else begin
-- Try to find corresponding prepaid card
/* declare
@PrepaidObjectID int
select @PrepaidObjectID = PO.PrepaidObjectID
from PrepaidObjects as PO
inner join PrepaidObjectValues as UN on UN.PrepaidObjectID =
PO.PrepaidObjectID AND UN.ItemID = 1 -- UserName
where UN.Value_LPSTR = @UserName
AND State = 0 -- not activated pp card
if ( @PrepaidObjectID is not NULL ) begin
-- Activate the prepaid card
-- declare @ret int
exec @ret = Maint_PP_Activate
@PrepaidObjectID = @PrepaidObjectID,
@NewObjectID = @ObjectID output
if ( @@ERROR <> 0 or @ret <> 0 ) begin
-- Prepaid card activation failed
goto lblAuthFailed
end
select @PlanID = PO.PlanID,
@PlanInfoID = I.PlanInfoID,
@TemplateLoginID = I.TemplateLoginID
from PrepaidObjects as PO
inner join PrepaidBlocks as PB on PO.PrepaidBlockID = PB.PrepaidBlockID
inner join PlanInfo as I on PB.PlanInfoID = I.PlanInfoID
where PO.PrepaidObjectID = @PrepaidObjectID
end else begin
-- User not found
goto lblAuthFailed
end
*/
exec PrepaidCard_Find
@nItemID =1, -- UserName
@sValue = @UserName,
@nObjectID = @ObjectID output
if ( @ObjectID is NULL )
goto lblAuthFailed
select @PlanID = P.PlanID,
@PlanInfoID = I.PlanInfoID,
@TemplateLoginID = I.TemplateLoginID
from Objects O
inner join Plans as P on P.PlanID = O.PlanID
inner join PlanInfo as I on P.PlanInfoID = I.PlanInfoID
where O.ObjectID = @ObjectID
end
-- Get the password
select @Password = Value_LPSTR
from ObjectValues
where ObjectID = @ObjectID AND ItemID = 4
-- Password Mask
if ( @PasswordMask is NOT NULL ) -- isnull(@PasswordMask, '') <> ''
select @Password = @PasswordMask
-- Get the correct LoginID for the attribute's values
if not exists ( select * from RadiusTemplates where ObjectID = @ObjectID )
select @LoginID = isnull(@TemplateLoginID, @DefaultLoginID)
else
select @LoginID = TemplateLoginID from RadiusTemplates where ObjectID =
@ObjectID
if exists (select * from ObjectValues where ObjectID = @ObjectID AND ItemID
= 24)
begin
-- Prepaid card authentication
exec @ret = PrepaidCard_Validate
@nObjectID = @ObjectID
if(@ret<>0)
-- Authentication failed
goto lblAuthFailed
declare
@CreditMoney money--,
-- at ExpTime smalldatetime
-- Get remaining credit
select @CreditMoney = Value_Decimal
from ObjectValues
where ObjectID = @ObjectID AND ItemID = 24
/* -- Get expiration time
select @ExpTime = cast(Value_LPSTR as smalldatetime)
from ObjectValues
where ObjectID = @ObjectID AND ItemID = 67
if ( @CreditMoney <= 0 or @ExpTime <= getdate() ) begin
-- Account is either expired or exhausted
exec Maint_PP_Close @ObjectID = @ObjectID
-- Authentication failed
goto lblAuthFailed
end
*/
exec Maint_PP_CalcCreditTime
@CreditMoney = @CreditMoney,
@PlanInfoID = @PlanInfoID,
@CreditTime = @SessionTimeout output
end
-- Create authentication recordset
select convert(varchar(255), 'Password') as AttributeName,
convert(varchar(255), @Password) as AttributeValue,
1 as CheckItem,
0 as OrderID
UNION
select convert(varchar(255), 'Session-Timeout') as AttributeName,
convert(varchar(255), @SessionTimeout) as AttributeValue,
0 as CheckItem,
1 as OrderID
where isnull(@SessionTimeout, 0) > 0
UNION
select distinct
A.AttributeName as AttributeName,
T.AttributeValue as AttributeValue,
convert(int, isnull(A.CheckItem, 0)) as CheckItem,
1 - isnull(A.CheckItem, 0) as OrderID
from RadiusTemplateAttr as T
inner join RadiusAttributes as A on T.AttributeID = A.AttributeID
where T.TemplateLoginID = @LoginID
and ( (A.AttributeName <> 'Session-Timeout') or (isnull(@SessionTimeout,
0) <= 0) )
order by OrderID, AttributeName
return 0
lblAuthFailed:
select convert(varchar(255), '') as AttributeName,
convert(varchar(255), '') as AttributeValue,
0 as CheckItem,
0 as OrderID
where 1 = 0
return -1
GO
===
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