(RADIATOR) Rodopi & Radiator

Fri Feb 7 23:16:28 CST 2003

Hi All,

Im pleased to report that this problem was due to an incompatibility 
introduced in the latest freetds 0.61 snapshot. When Tim rolled back to the 
stable 0.60 release, it worked fine.

Cheers, and thanks to Tim for his patience.

On Fri, 7 Feb 2003 12:50 pm, Tim Jung wrote:
> 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

-- 
Mike McCauley                               mikem at open.com.au
Open System Consultants Pty. Ltd            Unix, Perl, Motif, C++, WWW
24 Bateman St Hampton, VIC 3188 Australia   http://www.open.com.au
Phone +61 3 9598-0985                       Fax   +61 3 9598-0955

Radiator: the most portable, flexible and configurable RADIUS server 
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald, 
Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS, 
TTLS, PEAP etc on Unix, Windows, MacOS etc.

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