CREATE TABLE [dbo].[radcheck] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (64) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[Attribute] [varchar] (32) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[op] [char] (2) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[Value] [varchar] (253) COLLATE Cyrillic_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[radgroupcheck] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[GroupName] [varchar] (64) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[Attribute] [varchar] (32) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[op] [char] (2) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[Value] [varchar] (253) COLLATE Cyrillic_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[radgroupreply] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[GroupName] [varchar] (64) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[Attribute] [varchar] (32) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[op] [char] (2) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[Value] [varchar] (253) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[prio] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[radreply] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (64) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[Attribute] [varchar] (32) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[op] [char] (2) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[Value] [varchar] (253) COLLATE Cyrillic_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[usergroup] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (64) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[GroupName] [varchar] (64) COLLATE Cyrillic_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[radacct] WITH NOCHECK ADD
CONSTRAINT [PK_radacct] PRIMARY KEY CLUSTERED
(
[RadAcctId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[radcheck] WITH NOCHECK ADD
CONSTRAINT [PK_radcheck] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[radgroupcheck] WITH NOCHECK ADD
CONSTRAINT [PK_radgroupcheck] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[radgroupreply] WITH NOCHECK ADD
CONSTRAINT [PK_radgroupreply] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[radreply] WITH NOCHECK ADD
CONSTRAINT [PK_radreply] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[radacct] WITH NOCHECK ADD
CONSTRAINT [DF__radacct__AcctSes__76CBA758] DEFAULT ('') FOR [AcctSessionId],
CONSTRAINT [DF__radacct__AcctUni__77BFCB91] DEFAULT ('') FOR [AcctUniqueId],
CONSTRAINT [DF__radacct__UserNam__78B3EFCA] DEFAULT ('') FOR [UserName],
CONSTRAINT [DF__radacct__Realm__79A81403] DEFAULT ('') FOR [Realm],
CONSTRAINT [DF__radacct__NASIPAd__7A9C383C] DEFAULT ('') FOR [NASIPAddress],
CONSTRAINT [DF__radacct__CalledS__0519C6AF] DEFAULT ('') FOR [CalledStationId],
CONSTRAINT [DF__radacct__Calling__060DEAE8] DEFAULT ('') FOR [CallingStationId],
CONSTRAINT [DF__radacct__AcctTer__07020F21] DEFAULT ('') FOR [AcctTerminateCause]
GO
ALTER TABLE [dbo].[radcheck] WITH NOCHECK ADD
CONSTRAINT [DF__radcheck__UserNa__2E1BDC42] DEFAULT ('') FOR [UserName],
CONSTRAINT [DF__radcheck__Attrib__2F10007B] DEFAULT ('') FOR [Attribute],
CONSTRAINT [DF__radcheck__op__300424B4] DEFAULT ('==') FOR [op],
CONSTRAINT [DF__radcheck__Value__30F848ED] DEFAULT ('') FOR [Value]
GO
ALTER TABLE [dbo].[radgroupcheck] WITH NOCHECK ADD
CONSTRAINT [DF__radgroupc__Group__32E0915F] DEFAULT ('') FOR [GroupName],
CONSTRAINT [DF__radgroupc__Attri__33D4B598] DEFAULT ('') FOR [Attribute],
CONSTRAINT [DF__radgroupchec__op__34C8D9D1] DEFAULT ('==') FOR [op],
CONSTRAINT [DF__radgroupc__Value__35BCFE0A] DEFAULT ('') FOR [Value]
GO
ALTER TABLE [dbo].[radgroupreply] WITH NOCHECK ADD
CONSTRAINT [DF__radgroupr__Group__37A5467C] DEFAULT ('') FOR [GroupName],
CONSTRAINT [DF__radgroupr__Attri__38996AB5] DEFAULT ('') FOR [Attribute],
CONSTRAINT [DF__radgrouprepl__op__398D8EEE] DEFAULT ('=') FOR [op],
CONSTRAINT [DF__radgroupr__Value__3A81B327] DEFAULT ('') FOR [Value],
CONSTRAINT [DF__radgroupre__prio__3B75D760] DEFAULT ('0') FOR [prio]
GO
ALTER TABLE [dbo].[radreply] WITH NOCHECK ADD
CONSTRAINT [DF__radreply__UserNa__3D5E1FD2] DEFAULT ('') FOR [UserName],
CONSTRAINT [DF__radreply__Attrib__3E52440B] DEFAULT ('') FOR [Attribute],
CONSTRAINT [DF__radreply__op__3F466844] DEFAULT ('=') FOR [op],
CONSTRAINT [DF__radreply__Value__403A8C7D] DEFAULT ('') FOR [Value]
GO
ALTER TABLE [dbo].[usergroup] WITH NOCHECK ADD
CONSTRAINT [DF__usergroup__UserN__4222D4EF] DEFAULT ('') FOR [UserName],
CONSTRAINT [DF__usergroup__Group__4316F928] DEFAULT ('') FOR [GroupName]
GO
CREATE INDEX [IX_radacct] ON [dbo].[radacct]([UserName], [FramedIPAddress], [AcctSessionId], [AcctUniqueId], [AcctStartTime], [AcctStopTime], [NASIPAddress]) ON [PRIMARY]
GO
CREATE INDEX [IX_radcheck] ON [dbo].[radcheck]([UserName]) ON [PRIMARY]
GO
CREATE INDEX [IX_radgroupcheck] ON [dbo].[radgroupcheck]([GroupName]) ON [PRIMARY]
GO
CREATE INDEX [IX_radgroupreply] ON [dbo].[radgroupreply]([GroupName]) ON [PRIMARY]
GO
CREATE INDEX [IX_radreply] ON [dbo].[radreply]([UserName]) ON [PRIMARY]
GO
8. наполняем тестовыми данными
INSERT INTO radcheck VALUES ('test123', 'User-Password', ':=', '12345');
INSERT INTO radgroupcheck VALUES ('group', 'Auth-Type', '=', 'PAP');
INSERT INTO radgroupreply VALUES ('testgroup', 'Framed-IP-Address', '=', '10.0.1.0', 0);
INSERT INTO radgroupreply VALUES ( 'testgroup', 'Framed-IP-Netmask', '=', '255.255.255.0', 0);
INSERT INTO radgroupreply VALUES ( 'group', 'Framed-IP-Address', '=', '192.168.0.0', 0);
INSERT INTO radgroupreply VALUES ( 'group', 'Framed-Compression', '=', 'no', 0);
INSERT INTO radgroupreply VALUES ( 'group', 'Session-Timeout', '=', '0', 0);
INSERT INTO radgroupreply VALUES ('group', 'Idle-Timeout', '=', '0', 0);
INSERT INTO radgroupreply VALUES ('group', 'Port-Limit', '=', '0', 0);
INSERT INTO radgroupreply VALUES ('group', 'Framed-IP-Netmask', '=', '255.255.255.0', 0);
INSERT INTO radreply VALUES ('test123', 'Framed-IP-Address', '=', '10.0.77.17');
INSERT INTO radreply VALUES ('test123', 'Framed-Compression', '=', 'no');
INSERT INTO usergroup VALUES ('test123', 'group');
9 проверяем unixobdc
cd /usr/local/bin
isql -v billing radius radius
select * from radreply
должно быть видно то, что запихнули в таблицу
10. компилим радиус freeradius
./configure --with-unixodbc-dir=/usr/local
make
make install
11 правим radiusd.conf
ищем и правим если нужно следущие строки
$INCLUDE ${confdir}/mssql.conf
authorize {
preprocess
chap
eap
mschap
sql
}
authenticate {
Auth-Type PAP {
pap
}
Auth-Type CHAP {
chap
}
Auth-Type MS-CHAP {
mschap
}
eap
}
accounting {
acct_unique
sql
}
session {
radutmp
# sql
}
12. mssql.conf
ищем и правим если нужно следущие строки
driver = "rlm_sql_unixodbc"
# Connect info
server = "billing"
login = "radius"
password = "radius"
после этого скачиваем тестового клиента (например NTradPing,
http://www.mastersoft-group.com/download) и пробуем ! должно заработать.
не забудте поставить Галку "CHAP"
Vova
