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 [EMAIL PROTECTED] ----- Original Message ----- From: "Mike McCauley" <[EMAIL PROTECTED]> To: "Tim Jung" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> 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 > > [EMAIL PROTECTED] -------- 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, -@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, -@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--, --@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 [EMAIL PROTECTED] To unsubscribe, email '[EMAIL PROTECTED]' with 'unsubscribe radiator' in the body of the message.