OpenNET: статья - 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"

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