Introduction

In this post I would like to go through quick steps to configure Network Access Protection to extract data to SQL Server, and describe the minimum settings needed to accomplish this task. This post has been written to reference the following technologies:

• SQL Server 2008 R2
• Microsoft Windows Server 2008 & NPS (RADIUS)

Configuration Steps

To keep this post short I will include the steps need to extract the data, in addition I will include some links that will provide additional references.

1. Create SQL Server Database:

To extract the NPS data you need to create a centralized repository to store logging & accounting information (A useful database name will be something like NPSDB). The database needs to have at least the following object created:

• NPS_Packets Table: This table will store the data coming from NPS.
• Report_Event Stored Procedure: This procedure will be used to send the data to the RADUIS_Events Table. Keep in mind that you need to use the exact name for the stored procedure as RADIUS will be using that exact name.

Sample on Table:

CREATE TABLE [dbo].[NPS_Packets](

[PacketTime] [datetime] NOT NULL,

[NPS_Attributes] [xml] NOT NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[NPS_Packets]
ADD DEFAULT (getdate()) FOR [PacketTime]

GO

Sample on Stored Procedure:

CREATE PROCEDURE [dbo].[Report_Event]

(@doc nvarchar(max))

AS

INSERT INTO NPS_Packets

([PacketTime], [NPS_Attributes])

VALUES (GETDATE(), @doc)

GO

Additional Information:

I found a nice post by Jeff Sigman that has another samples (http://blogs.technet.com/b/nap/archive/2008/07/08/nps-nap-logging-bsu-edu-style.aspx)

2. Configure NPS Accounting Settings:

After creating the database, you need to connect the NPS to SQL which is straight forward as following:

• Log-in to NPS Server
• Start NPS (from Control Panel -> Administrative Tools)
• Select Accounting Table (from the left side menu)
• Click on configuring NPS on SQL Server.
• Provide the SQL Server information & Database name

Additional Information:

you can find additional information on this issue using the below links:

• NPS SQL Server Logging (http://technet.microsoft.com/en-us/library/dd197595(v=WS.10).aspx)
• Interpret NPS Database Format Log Files (http://technet.microsoft.com/en-us/library/cc771748(v=WS.10).aspx)

General Consideration:

There are few recommendation that I found useful to give you some heads up before planning to implement this solution:

• NPS Accounting Information are sent to SQL Server in XML format, so you need to consider to extract that data and interpret it if you plan to use it somewhere else.
• If one of the attributes (columns) sent from NPS has a Null value, it will not appear in the XML.
• NPS usually sent huge amount of data to SQL, so you need to consider the performance of the database.
• If NPS failed to connect to the Database for records will be lost and cann't be retrieved, so consider providing a suitable error handling technique.