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:
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:
Sample on Table:
CREATE TABLE [dbo].[NPS_Packets]([PacketTime] [datetime] NOT NULL,[NPS_Attributes] [xml] NOT NULL) ON [PRIMARY]
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
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:
you can find additional information on this issue using the below links:
There are few recommendation that I found useful to give you some heads up before planning to implement this solution:
Just to add that the database should be named NPSODBC as this example http://msdn.microsoft.com/en-us/library/bb960723(VS.85).aspx
Windows 2012 Server, when you enable RADIUS accounting, the tables and store procedures will be created. There is no need to use method mentioning above.