FreeRadius 3 + iODBC + Base de Dados em MS SQL Server 2008 no Ubuntu Server - Guia definitivo

ferreiraeb2

Após muitas pesquisas e horas e mais horas de trabalho, consegui realizar esta junção de forma simples e funcional. São praticamente nulas as fontes de informações do FreeRadius com Microsoft, desta forma, este artigo passa a ser o guia definitivo para quem busca configurar o FreeRadius 3 com uma base de dados Microsoft SQL.

[ Hits: 20.369 ]

Por: Eduardo em 25/07/2016

7 0

Denuncie Favoritos Indicar Impressora

Preparando o Microsoft SQL Server 2008

Antes de iniciar, crie uma nova Base de Dados no MSSQL chamada "radius", crie um usuário de acesso chamado "radius" e a senha "radius". Com base nas informações abaixo, crie as tabelas e o usuário.


/****** Object:  Table [radacct]    Script Date: 26.03.02 16:55:17 ******/
CREATE TABLE [radacct] (
	[RadAcctId] [numeric](21, 0) IDENTITY (1, 1) NOT NULL ,
	[AcctSessionId] [varchar] (64) DEFAULT (''),
	[AcctUniqueId] [varchar] (32) DEFAULT (''),
	[UserName] [varchar] (64) DEFAULT (''),
	[GroupName] [varchar] (64) DEFAULT (''),
	[Realm] [varchar] (64) DEFAULT (''),
	[NASIPAddress] [varchar] (15) DEFAULT (''),
	[NASPortId] [varchar] (15) NULL ,
	[NASPortType] [varchar] (32) NULL ,
	[AcctStartTime] [datetime] NOT NULL ,
	[AcctStopTime] [datetime] NOT NULL ,
	[AcctSessionTime] [bigint] NULL ,
	[AcctAuthentic] [varchar] (32) NULL ,
	[ConnectInfo_start] [varchar] (32) DEFAULT (null),
	[ConnectInfo_stop] [varchar] (32) DEFAULT (null),
	[AcctInputOctets] [bigint] NULL ,
	[AcctOutputOctets] [bigint] NULL ,
	[CalledStationId] [varchar] (30) DEFAULT (''),
	[CallingStationId] [varchar] (30) DEFAULT (''),
	[AcctTerminateCause] [varchar] (32) DEFAULT (''),
	[ServiceType] [varchar] (32) NULL ,
	[FramedProtocol] [varchar] (32) NULL ,
	[FramedIPAddress] [varchar] (15) DEFAULT (''),
	[XAscendSessionSvrKey] [varchar] (10) DEFAULT (null),
	[AcctStartDelay] [int] NULL ,
	[AcctStopDelay] [int] NULL
) ON [PRIMARY]
GO

/****** Object:  Table [radcheck]    Script Date: 26.03.02 16:55:17 ******/
CREATE TABLE [radcheck] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[UserName] [varchar] (64) NOT NULL ,
	[Attribute] [varchar] (32) NOT NULL ,
	[Value] [varchar] (253) NOT NULL ,
	[op] [char] (2) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [radgroupcheck]    Script Date: 26.03.02 16:55:17 ******/
CREATE TABLE [radgroupcheck] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[GroupName] [varchar] (64) NOT NULL ,
	[Attribute] [varchar] (32) NOT NULL ,
	[Value] [varchar] (253) NOT NULL ,
	[op] [char] (2) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [radgroupreply]    Script Date: 26.03.02 16:55:17 ******/
CREATE TABLE [radgroupreply] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[GroupName] [varchar] (64) NOT NULL ,
	[Attribute] [varchar] (32) NOT NULL ,
	[Value] [varchar] (253) NOT NULL ,
	[op] [char] (2) NULL ,
	[prio] [int] NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [radreply]    Script Date: 26.03.02 16:55:18 ******/
CREATE TABLE [radreply] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[UserName] [varchar] (64) NOT NULL ,
	[Attribute] [varchar] (32) NOT NULL ,
	[Value] [varchar] (253) NOT NULL ,
	[op] [char] (2) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [radusergroup]    Script Date: 26.03.02 16:55:18 ******/
CREATE TABLE [radusergroup] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[UserName] [varchar] (64) NOT NULL ,
	[GroupName] [varchar] (64) NOT NULL,
	[Priority] [int] NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [radusergroup]    Script Date: 16.04.08 19:44:11 ******/
CREATE TABLE [radpostauth] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[userName] [varchar] (64) NOT NULL ,
	[pass] [varchar] (64) NOT NULL ,
	[reply] [varchar] (32) NOT NULL ,
	[authdate] [datetime] NOT NULL
)
GO

ALTER TABLE [radacct] WITH NOCHECK ADD
	CONSTRAINT [DF_radacct_GroupName] DEFAULT ('') FOR [GroupName],
	CONSTRAINT [DF_radacct_AcctSessionId] DEFAULT ('') FOR [AcctSessionId],
	CONSTRAINT [DF_radacct_AcctUniqueId] DEFAULT ('') FOR [AcctUniqueId],
	CONSTRAINT [DF_radacct_UserName] DEFAULT ('') FOR [UserName],
	CONSTRAINT [DF_radacct_Realm] DEFAULT ('') FOR [Realm],
	CONSTRAINT [DF_radacct_NASIPAddress] DEFAULT ('') FOR [NASIPAddress],
	CONSTRAINT [DF_radacct_NASPortId] DEFAULT (null) FOR [NASPortId],
	CONSTRAINT [DF_radacct_NASPortType] DEFAULT (null) FOR [NASPortType],
	CONSTRAINT [DF_radacct_AcctStartTime] DEFAULT ('1900-01-01 00:00:00') FOR [AcctStartTime],
	CONSTRAINT [DF_radacct_AcctStopTime] DEFAULT ('1900-01-01 00:00:00') FOR [AcctStopTime],
	CONSTRAINT [DF_radacct_AcctSessionTime] DEFAULT (null) FOR [AcctSessionTime],
	CONSTRAINT [DF_radacct_AcctAuthentic] DEFAULT (null) FOR [AcctAuthentic],
	CONSTRAINT [DF_radacct_ConnectInfo_start] DEFAULT (null) FOR [ConnectInfo_start],
	CONSTRAINT [DF_radacct_ConnectInfo_stop] DEFAULT (null) FOR [ConnectInfo_stop],
	CONSTRAINT [DF_radacct_AcctInputOctets] DEFAULT (null) FOR [AcctInputOctets],
	CONSTRAINT [DF_radacct_AcctOutputOctets] DEFAULT (null) FOR [AcctOutputOctets],
	CONSTRAINT [DF_radacct_CalledStationId] DEFAULT ('') FOR [CalledStationId],
	CONSTRAINT [DF_radacct_CallingStationId] DEFAULT ('') FOR [CallingStationId],
	CONSTRAINT [DF_radacct_AcctTerminateCause] DEFAULT ('') FOR [AcctTerminateCause],
	CONSTRAINT [DF_radacct_ServiceType] DEFAULT (null) FOR [ServiceType],
	CONSTRAINT [DF_radacct_FramedProtocol] DEFAULT (null) FOR [FramedProtocol],
	CONSTRAINT [DF_radacct_FramedIPAddress] DEFAULT ('') FOR [FramedIPAddress],
	CONSTRAINT [DF_radacct_AcctStartDelay] DEFAULT (null) FOR [AcctStartDelay],
	CONSTRAINT [DF_radacct_AcctStopDelay] DEFAULT (null) FOR [AcctStopDelay],
	CONSTRAINT [PK_radacct] PRIMARY KEY  NONCLUSTERED
	(
		[RadAcctId]
	)  ON [PRIMARY]
GO

ALTER TABLE [radcheck] WITH NOCHECK ADD
	CONSTRAINT [DF_radcheck_UserName] DEFAULT ('') FOR [UserName],
	CONSTRAINT [DF_radcheck_Attribute] DEFAULT ('') FOR [Attribute],
	CONSTRAINT [DF_radcheck_Value] DEFAULT ('') FOR [Value],
	CONSTRAINT [DF_radcheck_op] DEFAULT (null) FOR [op],
	CONSTRAINT [PK_radcheck] PRIMARY KEY  NONCLUSTERED
	(
		[id]
	)  ON [PRIMARY]
GO

ALTER TABLE [radgroupcheck] WITH NOCHECK ADD
	CONSTRAINT [DF_radgroupcheck_GroupName] DEFAULT ('') FOR [GroupName],
	CONSTRAINT [DF_radgroupcheck_Attribute] DEFAULT ('') FOR [Attribute],
	CONSTRAINT [DF_radgroupcheck_Value] DEFAULT ('') FOR [Value],
	CONSTRAINT [DF_radgroupcheck_op] DEFAULT (null) FOR [op],
	CONSTRAINT [PK_radgroupcheck] PRIMARY KEY  NONCLUSTERED
	(
		[id]
	)  ON [PRIMARY]
GO

ALTER TABLE [radgroupreply] WITH NOCHECK ADD
	CONSTRAINT [DF_radgroupreply_GroupName] DEFAULT ('') FOR [GroupName],
	CONSTRAINT [DF_radgroupreply_Attribute] DEFAULT ('') FOR [Attribute],
	CONSTRAINT [DF_radgroupreply_Value] DEFAULT ('') FOR [Value],
	CONSTRAINT [DF_radgroupreply_op] DEFAULT (null) FOR [op],
	CONSTRAINT [DF_radgroupreply_prio] DEFAULT (0) FOR [prio],
	CONSTRAINT [PK_radgroupreply] PRIMARY KEY  NONCLUSTERED
	(
		[id]
	)  ON [PRIMARY]
GO

ALTER TABLE [radreply] WITH NOCHECK ADD
	CONSTRAINT [DF_radreply_UserName] DEFAULT ('') FOR [UserName],
	CONSTRAINT [DF_radreply_Attribute] DEFAULT ('') FOR [Attribute],
	CONSTRAINT [DF_radreply_Value] DEFAULT ('') FOR [Value],
	CONSTRAINT [DF_radreply_op] DEFAULT (null) FOR [op],
	CONSTRAINT [PK_radreply] PRIMARY KEY  NONCLUSTERED
	(
		[id]
	)  ON [PRIMARY]
GO

ALTER TABLE [radusergroup] WITH NOCHECK ADD
	CONSTRAINT [DF_radusergroup_UserName] DEFAULT ('') FOR [UserName],
	CONSTRAINT [DF_radusergroup_GroupName] DEFAULT ('') FOR [GroupName],
	CONSTRAINT [DF_radusergroup_Priority] DEFAULT (0) FOR [Priority],
	CONSTRAINT [PK_radusergroup] PRIMARY KEY  NONCLUSTERED
	(
		[id]
	)  ON [PRIMARY]
GO

ALTER TABLE [radpostauth] WITH NOCHECK ADD
	CONSTRAINT [DF_radpostauth_userName] DEFAULT ('') FOR [userName],
	CONSTRAINT [DF_radpostauth_pass] DEFAULT ('') FOR [pass],
	CONSTRAINT [DF_radpostauth_reply] DEFAULT ('') FOR [reply],
	CONSTRAINT [DF_radpostauth_authdate] DEFAULT (getdate()) FOR [authdate],
	CONSTRAINT [PK_radpostauth] PRIMARY KEY NONCLUSTERED
	(
		[id]
	) ON [PRIMARY]
GO

 CREATE  INDEX [UserName] ON [radacct]([UserName]) ON [PRIMARY]
GO

 CREATE  INDEX [FramedIPAddress] ON [radacct]([FramedIPAddress]) ON [PRIMARY]
GO

 CREATE  INDEX [AcctSessionId] ON [radacct]([AcctSessionId]) ON [PRIMARY]
GO

 CREATE  UNIQUE INDEX [AcctUniqueId] ON [radacct]([AcctUniqueId]) ON [PRIMARY]
GO

 CREATE  INDEX [AcctStartTime] ON [radacct]([AcctStartTime]) ON [PRIMARY]
GO

 CREATE  INDEX [AcctStopTime] ON [radacct]([AcctStopTime]) ON [PRIMARY]
GO

 CREATE  INDEX [NASIPAddress] ON [radacct]([NASIPAddress]) ON [PRIMARY]
GO

 CREATE  INDEX [UserName] ON [radcheck]([UserName]) ON [PRIMARY]
GO

 CREATE  INDEX [GroupName] ON [radgroupcheck]([GroupName]) ON [PRIMARY]
GO

 CREATE  INDEX [GroupName] ON [radgroupreply]([GroupName]) ON [PRIMARY]
GO

 CREATE  INDEX [UserName] ON [radreply]([UserName]) ON [PRIMARY]
GO

 CREATE  INDEX [UserName] ON [radusergroup]([UserName]) ON [PRIMARY]
GO

INSERT INTO radcheck (UserName, Attribute, Value, op) VALUES ('sqltest','Cleartext-Password','testpwd',':=');

Página anterior Próxima página

Páginas do artigo

   1. Introdução
   2. Preparando o Microsoft SQL Server 2008
   3. Instalando o FreeTDS e o iODBC
   4. Instalando e configurando o FreeRadius 3

Outros artigos deste autor

Nenhum artigo encontrado.

Leitura recomendada

Nagios 3 + NagiosQL no Ubuntu Server 12.04

Instalando o oVirt 4.3 Single Host

Criando RADIUS no Windows Server 2012 para autenticar no Mikrotik

Redes de Computadores · IPtables · Endereços IPs - Explicações básicas

Sincronizando Bases OpenLDAP e Active Directory

Comentários

[1] Comentário enviado por EduardoBelmonte em 26/07/2016 - 10:20h

Parabéns pelo artigo, também tive dificuldade em configurar o freeradius 2.x no slack consultando os usuários pela base ldap com controle de grupo, funcionando tanto para windows, linux e etc, fiquei 3 meses nessa pesquisa, mas valeu a pena pelo conhecimento.
Grande abraço

5 0