FreeRadius + MSSQL ( unixodbc+freetds ) HowTo (radius sql win freetds mssql)
Ключевые слова: radius , sql , win , freetds , mssql , (найти похожие документы )
From: Konstantin <light_lord at hotmail.com>
Newsgroups: email
Date: Mon, 19 Apr 2004 14:31:37 +0000 (UTC)
Subject: FreeRadius + MSSQL ( unixodbc+freetds ) HowTo
FreeRadius + MSSQL ( unixodbc+freetds ) HowTo
для начала скачаем :
unixODBC-2.2.8.tar.gz
freetds-stable.tgz (ver 0.62)
freeradius-0.9.3.tar.gz
указанные версии использовались при составлении инструкции.
- Заранее установим MSSQL Server 2000 на компьютер с ip 192.168.0.1
- Заранее создаем базу данных radius
- Заранее создаем юзера в MSSQL с проверкой пароля через MSSQL
логин=radius, pass=radius
даем этому юзеру права dbowner на созданную базу
- Заранее установим Linux Slackware 9.1 на компьютер с ip 192.168.0.2
- Заранее установим любую ОС (лучше винду) на комп 192.168.0.3 (образцовый клиент радиус)
Скопируем все что скачали на радиус сервер и сделаем :
1. install unixodbc
./configure
make
make install
для успешной компиляции понадобятся библиотеки QT, которые стоит указать
при инсталяции Шлаки.
2. install freedts
./configure --with-tdsver=8.0 --with-unixodbc=/usr/local
make
make install
добавляем в файл
/usr/local/etc/freetds.conf
строки :
[billing]
host = 192.168.0.1
port = 1433
tds version = 8.0
проверяем, видно ли MSSQL
cd /usr/local/bin
./tsql -S billing -U radius
select * from syscolumns
go
должны увидеть кучу данных.
3. создаем файл tds.driver.template
содержимое
[TDS]
Description = v0.62 with protocol v8.0
Driver = /usr/local/lib/libtdsodbc.so
копируем его в /usr/local/etc/
4. устанавливаем driver
/usr/local/bin/odbcinst -i -d -f /usr/local/etc/tds.driver.template
5. создаем файл tds.datasource.template
содержимое
[billing]
Driver = TDS
Description = Radius database
Trace = No
Servername = billing
Database = radius
UID = radius
PWD = radius
6. устанавливаем odbc LINK
/usr/local/bin/odbcinst -i -s -f /usr/local/etc/tds.datasource.template
7. создаем таблицы для MSSQL
CREATE TABLE [dbo].[radacct] (
[RadAcctId] [bigint] IDENTITY (1, 1) NOT NULL ,
[AcctSessionId] [varchar] (32) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[AcctUniqueId] [varchar] (32) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[UserName] [varchar] (64) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[Realm] [varchar] (64) COLLATE Cyrillic_General_CI_AS NULL ,
[NASIPAddress] [varchar] (15) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[NASPortId] [int] NULL ,
[NASPortType] [varchar] (32) COLLATE Cyrillic_General_CI_AS NULL ,
[AcctStartTime] [datetime] NOT NULL ,
[AcctStopTime] [datetime] NOT NULL ,
[AcctSessionTime] [int] NULL ,
[AcctAuthentic] [varchar] (32) COLLATE Cyrillic_General_CI_AS NULL ,
[ConnectInfo_start] [varchar] (32) COLLATE Cyrillic_General_CI_AS NULL ,
[ConnectInfo_stop] [varchar] (32) COLLATE Cyrillic_General_CI_AS NULL ,
[AcctInputOctets] [bigint] NULL ,
[AcctOutputOctets] [bigint] NULL ,
[CalledStationId] [varchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[CallingStationId] [varchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[AcctTerminateCause] [varchar] (32) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[ServiceType] [varchar] (32) COLLATE Cyrillic_General_CI_AS NULL ,
[FramedProtocol] [varchar] (32) COLLATE Cyrillic_General_CI_AS NULL ,
[FramedIPAddress] [varchar] (15) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[AcctStartDelay] [int] NULL ,
[AcctStopDelay] [int] NULL
) ON [PRIMARY]
GO
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"
# Database table configuration
radius_db = "radius"
13. clients.conf
ищем и правим если нужно следущие строки
client 192.168.0.3 {
secret = secret
shortname = test1
}
все !
после этого скачиваем тестового клиента (например NTradPing,
http://www.mastersoft-group.com/download ) и пробуем ! должно заработать.
не забудте поставить Галку "CHAP"
1.1 , Konstantin (?? ), 00:20, 20/04/2004 [ответить ]
+ /–
поправка :
нужно использовать файл sql.conf, подправив в нем формат запросов в MSSQL и указав дравер
driver = "rlm_sql_unixodbc"
2.3 , smartkz (ok ), 08:59, 07/05/2004 [^ ] [^^ ] [^^^ ] [ответить ]
+ /–
у меня что то не работает вот что пишет лог:
May 7 11:58:18 2004 : Info: rlm_sql (sql): Trying to (re)connect unconnected handle 4..
May 7 11:58:18 2004 : Error: rlm_sql_unixodbc: Connection failed
May 7 11:58:18 2004 : Error: rlm_sql (sql): Failed to connect DB handle #4
May 7 11:58:18 2004 : Info: rlm_sql (sql): There are no DB handles to use! skipped 5, tried to c
May 7 11:58:22 2004 : Error: Dropping packet from client test1:1937 - ID: 6 due to dead request
1.4 , Федор (? ), 15:54, 30/05/2004 [ответить ]
+ /–
Я только одного не пойму, это радиус должен напрямую понимать freetds, зачем еще и unixodbc прикручивать?