A incercat cineva sa configureze un server IAS-RADIUS ca sa exporte logurile intr-o baza de date SqlServer2005?
Teoretic, acest lucru se face in doi pasi:
1. Configurare Server IAS: in interfata de configurare se precizeaza Numele serverului, al bazei de date, user&password apoi se testeaza conexiunea
2. Configurare server SQL2005: aici e putin mai mult de lucru. Trebuie creata o baza de date, o tabela in care se vor injecta datele, un user si o procedura stocata numita 'Report_event'. In help-ul IAS este descris modul cum functioneaza exportul IAS-SqlServer, dar nu sunt date detalii despre structura tabelei. Practic, IAS creeaza un XML, apeleaza procedura stocata 'Report_event', aceasta prelucreaza XML-ul si face Insert in tabela. Am cautat informatii despre structura acestei tabele si am gasit ceva in MSDN si pe Technet la Microsoft.

Am rulat cu succes scriptul de mai jos(scriptul este preluat din documentatia gasita pe Technet). S-a creat baza de date, type-ul 'Ipaddress',  tabela respectiva cat si procedura stocata 'report_event'. Am creat apoi un 'login' = 'USERIAS' pe care l-am mapat pe baza de date NPSODBC si i-am dat permisiuni de connect, select,insert,update si execute. Am configurat interfata de conectare a serverului de IAS cu datele respective(server='SERVERNAME', database='NPSODBC', authetication='SqlServer',User='USERIAS',Password='Password'), am testat conexiunea si a functionat OK.
Cand serverul IAS-Radius primeste o cerere de la un user pentru conectare la retea via IAS/RAS, creeaza o inregistrare in log (logul poate fi de tip text file sau poate fi inserata o inregistrare intr-un server sql)
Problema este ca de fapt nu se insereaza nicio inregistrare in baza de date Sql de la serverul IAS. In logul Sql se jurnalizeaza ca 'login failed for user USERIAS [Client:IPserverIAS]'. Totusi, cand testez conexiunea catre baza de date de pe serverul IAS-din interfata de configurare- imi spune ca e ok(Connection successful) Ce permisiuni ar trebui sa ii mai acord userului 'USERIAS'?
Mentiune: SqlServer2005 ruleaza pe o alta masina decat IAS, iar ambele masini au ca SO W2K3 R2 standard edition(cu SP2)


IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'NPSODBC')
    DROP DATABASE [NPSODBC]
GO

CREATE DATABASE [NPSODBC]  ON (NAME = N'NPSODBC_Data',
                               FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\NPSODBC_Data.MDF' ,
                               SIZE = 1, FILEGROWTH = 10%)
                       LOG ON (NAME = N'NPSODBC_Log',
                               FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\NPSODBC_Log.LDF' ,
                               SIZE = 1, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'NPSODBC', N'autoclose', N'false'
GO

exec sp_dboption N'NPSODBC', N'bulkcopy', N'false'
GO

exec sp_dboption N'NPSODBC', N'trunc. log', N'false'
GO

exec sp_dboption N'NPSODBC', N'torn page detection', N'true'
GO

exec sp_dboption N'NPSODBC', N'read only', N'false'
GO

exec sp_dboption N'NPSODBC', N'dbo use', N'false'
GO

exec sp_dboption N'NPSODBC', N'single', N'false'
GO

exec sp_dboption N'NPSODBC', N'autoshrink', N'false'
GO

exec sp_dboption N'NPSODBC', N'ANSI null default', N'false'
GO

exec sp_dboption N'NPSODBC', N'recursive triggers', N'false'
GO

exec sp_dboption N'NPSODBC', N'ANSI nulls', N'false'
GO

exec sp_dboption N'NPSODBC', N'concat null yields null', N'false'
GO

exec sp_dboption N'NPSODBC', N'cursor close on commit', N'false'
GO

exec sp_dboption N'NPSODBC', N'default to local cursor', N'false'
GO

exec sp_dboption N'NPSODBC', N'quoted identifier', N'false'
GO

exec sp_dboption N'NPSODBC', N'ANSI warnings', N'false'
GO

exec sp_dboption N'NPSODBC', N'auto create statistics', N'true'
GO

exec sp_dboption N'NPSODBC', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or
    ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
    exec sp_dboption N'NPSODBC', N'db chaining', N'false'
GO

use [NPSODBC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[report_event]') and
                                              OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[report_event]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[accounting_data]') and
                                              OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[accounting_data]
GO

if exists (select * from dbo.systypes where name = N'ipaddress')
exec sp_droptype N'ipaddress'
GO

setuser
GO

EXEC sp_addtype N'ipaddress', N'nvarchar (15)', N'not null'
GO

setuser
GO

CREATE TABLE [dbo].[accounting_data] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [timestamp] [datetime] NOT NULL ,
    [Computer_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Packet_Type] [int] NOT NULL ,
    [User_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [F_Q_User_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Called_Station_Id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Calling_Station_Id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Callback_Number] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Framed_IP_Address] [ipaddress] NULL ,
    [NAS_Identifier] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NAS_IP_Address] [ipaddress] NULL ,
    [NAS_Port] [int] NULL ,
    [Client_Vendor] [int] NULL ,
    [Client_IP_Address] [ipaddress] NULL ,
    [Client_Friendly_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Event_Timestamp] [datetime] NULL ,
    [Port_Limit] [int] NULL ,
    [NAS_Port_Type] [int] NULL ,
    [Connect_Info] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Framed_Protocol] [int] NULL ,
    [Service_Type] [int] NULL ,
    [Authentication_Type] [int] NULL ,
    [NP_Policy_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reason_Code] [int] NULL ,
    [Class] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Session_Timeout] [int] NULL ,
    [Idle_Timeout] [int] NULL ,
    [Termination_Action] [int] NULL ,
    [EAP_Friendly_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Acct_Status_Type] [int] NULL ,
    [Acct_Delay_Time] [int] NULL ,
    [Acct_Input_Octets] [int] NULL ,
    [Acct_Output_Octets] [int] NULL ,
    [Acct_Session_Id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Acct_Authentic] [int] NULL ,
    [Acct_Session_Time] [int] NULL ,
    [Acct_Input_Packets] [int] NULL ,
    [Acct_Output_Packets] [int] NULL ,
    [Acct_Terminate_Cause] [int] NULL ,
    [Acct_Multi_Session_Id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Acct_Link_Count] [int] NULL ,
    [Acct_Interim_Interval] [int] NULL ,
    [Tunnel_Type] [int] NULL ,
    [Tunnel_Medium_Type] [int] NULL ,
    [Tunnel_Client_Endpoint] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Tunnel_Server_Endpoint] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Acct_Tunnel_Connection] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Tunnel_Pvt_Group_Id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Tunnel_Assignment_Id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Tunnel_Preference] [int] NULL ,
    [MS_Acct_Auth_Type] [int] NULL ,
    [MS_Acct_EAP_Type] [int] NULL ,
    [MS_RAS_Version] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MS_RAS_Vendor] [int] NULL ,
    [MS_CHAP_Error] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MS_CHAP_Domain] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MS_MPPE_Encryption_Types] [int] NULL ,
    [MS_MPPE_Encryption_Policy] [int] NULL ,
    [Proxy_Policy_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Provider_Type] [int] NULL ,
    [Provider_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Remote_Server_Address] [ipaddress] NULL ,
    [MS_RAS_Client_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MS_RAS_Client_Version] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
/*
    The following column stores the NAP-specific information, available from NPS starting with Windows Server 2008.
    The allowed values are: 0 (Full Access), 1 (Quarantined), and 2 (Probation).
*/
    [MS_Quarantine_State] [int] NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.report_event
    @doc ntext
AS

SET NOCOUNT ON

DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

/*
    All RADIUS attributes written to the ODBC format logfile are declared here. 
    One additional attribute is added: @record_timestamp.
    The value of @record_timestamp is the UTC time the record was inserted in the database.

    Refer to IAS-Formatted Log Files in Online Help on www.technet.com for information on interpreting these values.
*/

DECLARE @record_timestamp datetime

SET @record_timestamp = GETUTCDATE()

INSERT accounting_data
SELECT
    @record_timestamp,
    Computer_Name,
    Packet_Type,
    [User_Name],
    F_Q_User_Name,
    Called_Station_Id,
    Calling_Station_Id,
    Callback_Number,
    Framed_IP_Address,
    NAS_Identifier,
    NAS_IP_Address,
    NAS_Port,
    Client_Vendor,
    Client_IP_Address,
    Client_Friendly_Name,
    Event_Timestamp,
    Port_Limit,
    NAS_Port_Type,
    Connect_Info,
    Framed_Protocol,
    Service_Type,
    Authentication_Type,
    NP_Policy_Name,
    Reason_Code,
    Class,
    Session_Timeout,
    Idle_Timeout,
    Termination_Action,
    EAP_Friendly_Name,
    Acct_Status_Type,
    Acct_Delay_Time,
    Acct_Input_Octets,
    Acct_Output_Octets,
    Acct_Session_Id,
    Acct_Authentic,
    Acct_Session_Time,
    Acct_Input_Packets,
    Acct_Output_Packets,
    Acct_Terminate_Cause,
    Acct_Multi_Session_Id,
    Acct_Link_Count,
    Acct_Interim_Interval,
    Tunnel_Type,
    Tunnel_Medium_Type,
    Tunnel_Client_Endpoint,
    Tunnel_Server_Endpoint,
    Acct_Tunnel_Connection,
    Tunnel_Pvt_Group_Id,
    Tunnel_Assignment_Id,
    Tunnel_Preference,
    MS_Acct_Auth_Type,
    MS_Acct_EAP_Type,
    MS_RAS_Version,
    MS_RAS_Vendor,
    MS_CHAP_Error,
    MS_CHAP_Domain,
    MS_MPPE_Encryption_Types,
    MS_MPPE_Encryption_Policy,
    Proxy_Policy_Name,
    Provider_Type,
    Provider_Name,
    Remote_Server_Address,
    MS_RAS_Client_Name,
    MS_RAS_Client_Version,
/*
    NAP-specific information, available from NPS starting with Windows Server 2008.
*/
    MS_Quarantine_State
FROM OPENXML(@idoc, '/Event')
WITH (
    Computer_Name nvarchar(255) './Computer-Name',
    Packet_Type int './Packet-Type',
    [User_Name] nvarchar(255) './User-Name',
    F_Q_User_Name nvarchar(255) './Fully-Qualifed-User-Name',
    Called_Station_Id nvarchar(255) './Called-Station-Id',
    Calling_Station_Id nvarchar(255) './Calling-Station-Id',
    Callback_Number nvarchar(255) './Callback-Number',
    Framed_IP_Address nvarchar(15) './Framed-IP-Address',
    NAS_Identifier nvarchar(255) './NAS-Identifier',
    NAS_IP_Address nvarchar(15) './NAS-IP-Address',
    NAS_Port int './NAS-Port',
    Client_Vendor int './Client-Vendor',
    Client_IP_Address nvarchar(15) './Client-IP-Address',
    Client_Friendly_Name nvarchar(255) './Client-Friendly-Name',
    Event_Timestamp datetime './Event-Timestamp',
    Port_Limit int './Port-Limit',
    NAS_Port_Type int './NAS-Port-Type',
    Connect_Info nvarchar(255) './Connect-Info',
    Framed_Protocol int './Framed-Protocol',
    Service_Type int './Service-Type',
    Authentication_Type int './Authentication-Type',
    NP_Policy_Name nvarchar(255) './NP-Policy-Name',
    Reason_Code int './Reason-Code',
    Class nvarchar(255) './Class',
    Session_Timeout int './Session-Timeout',
    Idle_Timeout int './Idle-Timeout',
    Termination_Action int './Termination-Action',
    EAP_Friendly_Name nvarchar(255) './EAP-Friendly-Name',
    Acct_Status_Type int './Acct-Status-Type',
    Acct_Delay_Time int './Acct-Delay-Time',
    Acct_Input_Octets int './Acct-Input-Octets',
    Acct_Output_Octets int './Acct-Output-Octets',
    Acct_Session_Id nvarchar(255) './Acct-Session-Id',
    Acct_Authentic int './Acct-Authentic',
    Acct_Session_Time int './Acct-Session-Time',
    Acct_Input_Packets int './Acct-Input-Packets',
    Acct_Output_Packets int './Acct-Output-Packets',
    Acct_Terminate_Cause int './Acct-Terminate-Cause',
    Acct_Multi_Session_Id nvarchar(255) './Acct-Multi-Session-Id',
    Acct_Link_Count int './Acct-Link-Count',
    Acct_Interim_Interval int './Acct-Interim-Interval',
    Tunnel_Type int './Tunnel-Type',
    Tunnel_Medium_Type int './Tunnel-Medium-Type',
    Tunnel_Client_Endpoint nvarchar(255) './Tunnel-Client-Endpt',
    Tunnel_Server_Endpoint nvarchar(255) './Tunnel-Server-Endpt',
    Acct_Tunnel_Connection nvarchar(255) './Acct-Tunnel-Connection',
    Tunnel_Pvt_Group_Id nvarchar(255) './Tunnel-Pvt-Group-Id',
    Tunnel_Assignment_Id nvarchar(255) './Tunnel-Assignment-Id',
    Tunnel_Preference int './Tunnel-Preference',
    MS_Acct_Auth_Type int './MS-Acct-Auth-Type',
    MS_Acct_EAP_Type int './MS-Acct-EAP-Type',
    MS_RAS_Version nvarchar(255) './MS-RAS-Version',
    MS_RAS_Vendor int './MS-RAS-Vendor',
    MS_CHAP_Error nvarchar(255) './MS-CHAP-Error',
    MS_CHAP_Domain nvarchar(255) './MS-CHAP-Domain',
    MS_MPPE_Encryption_Types int './MS-MPPE-Encryption-Types',
    MS_MPPE_Encryption_Policy int './MS-MPPE-Encryption-Policy',
    Proxy_Policy_Name nvarchar(255) './Proxy-Policy-Name',
    Provider_Type int './Provider-Type',
    Provider_Name nvarchar(255) './Provider-Name',
    Remote_Server_Address nvarchar(15) './Remote-Server-Address',
    MS_RAS_Client_Name nvarchar(255) './MS-RAS-Client-Name',
    MS_RAS_Client_Version nvarchar(255) './MS-RAS-Client-Version',
/*
    NAP-specific information, available from NPS starting with Windows Server 2008.
*/
    MS_Quarantine_State int './MS-Quarantine-State'
   )

EXEC sp_xml_removedocument @idoc

SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Ma poate ajuta cineva?

Pai sa zicem ca poti incepe cu dbowner.

Si apoi mai restrangem daca merge.

Keep me updated.

'Login-ul' 'USERIAS' are default database NPSODBC, este mapat pe aceasta baza de date, default schema este 'dbo', iar server roles este 'public'. Am atribuit userului 'USERIAS' din baza de date NPSODBC, database role= 'dbowner'.Astfel, pentru 'securables' dbo.accounting_data'(tabela) cat si pentru 'dbo.report_event'(procedura stocata ce ar trebui sa prelucreze xml-ul si sa insereze in tabela) exista toate permisiunile efective(nu doar cele explicite bifate de mine). Comportamentul e insa acelasi(adica nu insereaza nimic in tabela si authentificarile prin IAS sunt blocate datorita imposibilitatii serverului IAS de a crea loguri).
 Am incercat si folosirea 'Windows authentication' in fereastra de configurare a IAS-serverului(interfata pentru exportare loguri in sql server) Credentialele cu care eram logat pe serverul IAS sunt credentiale de domeniu cu drept de administrare local pe cele doua servere(sql si IAS). Cele doua servere sunt in acelasi domeniu. Si in acest caz exportul esueaza(desi, tot in ambele cazuri, testarea conexiunii din interfata de configurare a serverului IAS-ptr export loguri in sql-este OK: connection successful)
De pe o alta statie ce are client sql, m-am conectat cu credentialele userului "USERIAS' (folosind sqlcmd) si am rulat un select:

select * from dbo.accounting data;
go
A returnat 0 records, deci userul putea sa se conecteze si sa interogheze tabela.
Mai mult, am creat si un trace cu Profiler-ul. Se inregistreaza conectarea si 'Select-ul' de pe statia respectiva, dar cand incerc  exportul de pe serverul IAS apare 'login failed for user USERIAS' Cred ca e ceva ce esueaza legat de procedura stocata.
Am gasit un 'White paper' de la microsoft in care e descrisa procedura de configurare si modul cum se exporta logurile IAS-Radius in sql server(deploying IAS logging to sql server). Baza de date, tabela, type-ul(ipaddress) si procedura report-event sunt ca in aceasta descriere.
Dar nu functioneaza

http://www.microsoft.com/downloads/details.aspx?FamilyId=6E4357F7-4070-4902-95F1-3AD411D963B2&displaylang=en

Am instalat local, pe masina IAS-Radius un server 'SqlServer2005 Express Edition with Advanced Tools', am rulat cu succes scriptul descris anterior. Am recreat 'login'-ul, l-am mapat pe baza de date proaspat creata, i-am atribuit initial rol de 'dbowner' pe baza de date, apoi chiar si rol de 'sysadmin'
Am testat din nou si tot nu a functionat cu autentificare tip sql server.(Testarea conexiunii din Interfata de configurare IAS este OK; deschizand un sqlcmd se poate face conectare la baza de date cu credentialele acestui user tip sql, se ruleaza cu succes instructiuni sql de genul select, insert,update) Am incercat acelasi lucru si cu 'SA'.Cand serverul IAS incearca conectarea la baza de date, esueaza. Am rulat si un trace cu Profiler-ul. Aici, mesajul este acelasi: 'login failed for user....' (SqlServer e configurat pentru ambele tipuri de autentificare-atat sql cat si windows)
 In cele din urma am facut un test si cu autentificare de tip 'Windows authentication'. Surpriza: Aici, adica local,IAS insereaza corect log-uri in tabela.
In ’White Paper’-ul din link-ul postat anterior sunt  descrise trei exemple de configurare expot log-uri IAS/Radius in SqlServer, toate bazandu-se pe o solutie cu doua servere IAS(al doilea ca backup), fiecare din ele avand instalat local un ’engine’ SQL(MSDE2000/2005 sau SQL2000/2005).
Astfel, un server Sql2000/2005 de pe o alta masina poate fi folosit pentru replicarea datelor de pe cele doua servere IAS. Aici insa nu se precizeaza ca exportul nu ar functiona direct pe o alta masina ce gazduieste un server SQL2005. Este doar recomandat a nu se folosi acest mod(insertul de date direct pe o alta masina si nu mai intai local apoi replicare pe aceasta) intrucat problemele ce ar apare pe terta masina ce gazduieste SQLServer2005 ar duce la imposibilitatea serverului IAS de a crea log-uri si implicit,  userii nu se vor mai putea conecta la retea prin WLAN/RAS.
De pe un alt forum unde am mai postat problema mea am primit un raspuns, care ma face intradevar sa cred ca credentialele sub care se face conectarea la sql in modul automat(atunci cand IAS incearca sa exporte un log in sql) sunt altele decat cele sub care se face testarea din interfata(cele date de mine)
-----------------------------------------------------------------------------------------------------------
11-15-09, 23:18
Pat Phelan 
Resident Curmudgeon         Join Date: Feb 2004
Location: In front of the computer
Posts: 10,418
 
There are a lot of possible answers to your question. I don't think that the permissions are your problem.

Based on the information that you've posted, I'd guess that a different password was being used by the automated logging process than is being used by the manual testing process. I've seen two Radius servers that had this problem, due to SOAP constructor issues.

Another remote possibility is that the SQL Client on the Radius server may be old enough to be incompatible with the filtering being done by your DMZ router or other hardware. Just to be safe, I'd use the SQL 2005 CD to install its SQL Native Client onto the Radius server, and then I'd apply SQL 2005 sp 3 or later to the Radius server to make its SQL Native Client current.

In general, Radius servers are touchy beasts. They are designed to be highly secure, minimalist configurations to keep them safe. They are often designed to be configured by extremely knowledgable SME (Subject Matter Experts) and there is often more undocumented than is documented about configuring them. This is usually a challenging task, but a rewarding one once you get it completed!

-PatP
----------------------------------------------------------------------------------------------------------