Compliance Reporting: First Step in Controlling Client Cloud Access
Improve your auditing and compliance reporting by using NAP and IPsec to control client access through DirectAccess.
Dan Griffin and Lee Walker
Establishing secure access is a logical first step to extending the enterprise into the cloud. By setting policies for compliance, reporting and remote connectivity now, you set the stage for how your team will work within the cloud in a smooth and secure fashion. Using Network Access Protection (NAP) with IPsec connectivity technologies like DirectAccess can help by improving your auditing and compliance reporting.
It can be difficult to identify and gather the necessary data when creating an auditing and reporting solution for a new DirectAccess or IPsec deployment. Here we will show how a hypothetical company might create a DirectAccess and NAP solution and provide reporting data to determine who was connected, when they were connected and if the client computer was within compliance.
The Compliance Problem
As the workforce becomes increasingly mobile, more organizations are adopting flexible remote access technologies like DirectAccess. With DirectAccess, whenever an authorized machine connects to the Internet, the user is automatically connected to the remote network. Because remote clients can sometimes be out-of-date with security patches and possibly infected by malware, many organizations also deploy NAP with IPsec to help ensure that only healthy clients access secured resources.
In industries like financial services, healthcare and government, the importance of verifying that only healthy and approved clients connect to cloud-based or on-site network resources is essential for data integrity. These industries are often required by internal compliance policies and Federal law to confirm that there has been no access to personally identifiable information (PII), such as bank account numbers, names and health records, by any unauthorized parties (including malware and unknown third-party applications).
As users seek easier remote access to their work resources, IT managers in these secure industries must also ensure that only healthy clients access the corporate network. Unfortunately, there are challenges to creating meaningful reports from NAP and DirectAccess logs.
Setting up a DirectAccess infrastructure for seamless remote client access, securing intranet resources with NAP and IPsec, and monitoring the policy through reporting is the solution. TechNet includes some good information about how to implement NAP with DirectAccess, but there’s little guidance on effectively logging and reporting on client health. We will examine a hypothetical company (Woodgrove National Bank) and show how a consultant could use some simple code and SQL queries to create human-readable reports that detail the clients that connected during a specified period of time and whether they were NAP-compliant.
Setting up NAP on Top of DirectAccess
DirectAccess requires that the connecting clients run a compatible version of Windows (Windows 7 Ultimate or Windows 7 Enterprise). These clients connect to a DirectAccess server running Windows Server 2008 R2. A DirectAccess deployment can include one or more DirectAccess servers. (We recommend at least two servers to help load balance on busy networks.) The deployment also must include a network location server (to determine whether the client is connected to the Internet or intranet) and one or more certification revocation list (CRL) distribution points (used to track clients that should no longer be allowed access). To learn how to design a DirectAccess deployment, see the DirectAccess Design Guide on TechNet.
When adding NAP on top of DirectAccess, you must implement the IPsec enforcement method for NAP. When using IPsec, clients that are NAP-compliant are granted health certificates. If a computer is not compliant, it’s not allowed to communicate with computers that are compliant. To learn about how to design and deploy NAP, see Planning DirectAccess with Network Access Protection on TechNet. To learn about how to design NAP with IPsec as the enforcement method, see IPsec Enforcement Design on TechNet.
It’s interesting to consider how the NAP IPsec enforcement scenario works within the context of DirectAccess and its IPsec connection policies. First, because DirectAccess uses IPsec for authentication and confidentiality, the NAP enforcement scenario in a DirectAccess deployment must be IPsec.Second, keep in mind that the AuthIP component of IPsec lets you configure two separate authentication requirements in the policy, such that the connection must meet both to succeed. Typically, if both AuthIP authentication options are configured, the first is the machine credential and the second is the user credential. However, it is technically possible to configure two machine credentials.
Where does NAP fit into the AuthIP policies? The NAP/IPsec enforcement scenario gives healthy machines a certificate with the health object identifier (OID). The AuthIP policy engine includes an option for requiring that health OID. Thus, only healthy machines will be able to meet the first AuthIP authentication requirement and establish an IPsec connection to the DirectAccess server.
Finally, the user credential is the purpose of the second AuthIP authentication option. Technically, the user credential is optional for DirectAccess. In other words, clients could authenticate to the DirectAccess endpoint using just a machine certificate. Security-conscious IT personnel should be nervous about giving full remote network access without strong authentication. At the minimum, therefore, the AuthIP policy should be configured to require a second authentication of Kerberos. Requiring a certificate for the user credential, as in the Woodgrove National Bank scenario, is preferable because it reduces the risk of remote static password attacks.
In this scenario, the network security and compliance departments of Woodgrove National Bank have requested a report showing who has connected to the corporate network over a specified period of time and whether or not those clients were NAP-compliant. These groups believe that there may have been a compromise of customer data during that time. As a consultant for the bank, we need to determine how to enable after-the-fact reporting for DirectAccess and NAP, and then pull that information into a human-readable report.
Proper Policy Configuration
In this hypothetical scenario, Woodgrove National Bank has configured DirectAccess so the IPsec policy requires client certificates that include the NAP system health OID and the client authentication OID. Woodgrove is using NAP in enforcement mode (rather than just reporting mode), which means unhealthy clients will be blocked from communicating with healthy clients.
Finally, Woodgrove has configured the DirectAccess IPsec policy to use two certificate-based credentials—one from the client computer and one from the user. As previously suggested, Woodgrove chose the double-certificate configuration in order to better utilize its PKI investment, to eliminate static passwords for remote access and to take advantage of certificate auto-enrollment.
The remainder of this story assumes that you have a working knowledge of how DirectAccess, NAP and the IPsec enforcement mode work. Please see the following resources to learn more about these technologies:
- DirectAccess Executive Overview
- Introduction to Network Access Protection
- Understanding NAP IPsec Enforcement
The following reporting solution takes advantage of the built-in auditing features of IPsec on the DirectAccess server as well as the auditing capabilities of the Health Registration Authority (HRA) feature of the Network Policy Server (NPS). These auditing features produce events in the system and security event logs, which we extract and report. In developing this approach, we found that the HRA events are produced by default, while the IPsec events might have to be explicitly enabled. You can use the following commands at a command prompt window to enable the IPsec events:
auditpol.exe /set /category:"Logon/Logoff" /subcategory:"IPsec Main Mode" /success:enable /failure:enable
auditpol.exe /set /category:"Logon/Logoff" /subcategory:"IPsec Quick Mode" /success:enable /failure:enable
auditpol.exe /set /category:"Logon/Logoff" /subcategory:"IPsec Extended Mode" /success:enable /failure:enable
DirectAccess Health Reporting
To begin working with the NAP and DirectAccess events from Woodgrove National Bank, we downloaded and installed Log Parser 2.2 from Microsoft. Log Parser is an indispensable tool for a project like this, where you must explore a new event source and develop an appropriate schema. In summary, Log Parser can import from and export to several data formats, including the Windows event log (.evtx), CSV and SQL.
The next step is to capture the events that are of interest. Those include:
- IPsec Security Association-related events in the security log of the DirectAccess servers
- Health Registration Authority-related events in the security and system logs of the HRA server, or servers (this item only applies if you’re using NAP)
An ideal solution for gathering those events is both automated and centralized. The Windows event forwarding feature is one option. Microsoft System Center would be more typical in a large production deployment. In our case, we did not want to introduce new dependencies for production servers, so we used simple scripts for gathering the events.
Given that approach, the challenge is twofold. First, because the goal is to correlate multiple data sources, it’s important that the data from all of the sources be gathered roughly at the same time. Second, because we’re only taking a snapshot of the logs, and high-traffic event logs roll over quickly, it’s inevitable that some correlating events will be missing, especially at the edge of the time period of the snapshot. This does not invalidate the experiment, but it does make it more difficult to tune the queries.
For each IPsec main-mode security association (on one of the DirectAccess servers) we expect to see NAP health traffic (on one of the HRA servers). In NAP reporting mode, the client machine may have been compliant or not. In NAP enforcement mode, the client machine should have been compliant. Otherwise, how does it have a valid certificate for authenticating to the DirectAccess server and establishing a security association (SA)? Suppose we do a one-time log capture on all DirectAccess and HRA servers simultaneously at 3 p.m. It’s possible we would see the main mode security association (MM SA) event, but not the health event.
Even more likely is that we would see IPsec quick mode security association (QM SA) and IPsec extended mode security association (EM SA) events, but not the MM SA or health events. The former can occur as much as an hour or more after the latter. In addition, because the logs on separate servers almost certainly roll over at different frequencies, we might have events from 2 p.m. on the DirectAccess server, but events only as early as 2:30 p.m. on the HRA. For these reasons, we want to reiterate that it’s important to use centralized event gathering in production.
Generating the Data
To generate the data, we ran scripts on the DirectAccess servers and the HRA servers. We also configured the scripts to be run automatically with Task Scheduler. We configured the script to run on the DirectAccess server and all of the HRAs one time, simultaneously.
Collecting Data on DirectAccess Servers
We used the following script to capture events on the DirectAccess server (see Figure 1):
set MPATH=c:\temp\Logs
%MPATH%\LogParser.exe "SELECT * INTO %MPATH%\DA_Security_Events_%COMPUTERNAME%.csv FROM Security WHERE EventCategory=12549 OR EventCategory=12547 OR EventCategory=12550" -o:CSV
Note: This script uses a local copy of LogParser.exe (and LogParser.dll, its dependency). This reference was used for convenience so that we could easily copy the script from server to server.
Figure 1 Details of the events captured from the DirectAccess server using a script run automatically with Task Scheduler.
| Event | Description |
| 12547 | IPsec Main Mode Security Association information |
| 12549 | IPsec Quick Mode Security Association information |
| 12550 | IPsec Extended Mode Security Association information |
Collecting Data on the HRA Servers
To capture events on the HRA servers, we used the following script:
set MPATH=c:\temp\Logs
%MPATH%\LogParser.exe "SELECT * INTO %MPATH%\HRA_Security_Events_%COMPUTERNAME%.csv FROM Security WHERE EventCategory=12552" -o:CSV
%MPATH%\LogParser.exe "SELECT * INTO %MPATH%\HRA_System_Events_%COMPUTERNAME%.csv FROM System WHERE SourceName='HRA'" -o:CSV
Note: In the HRA script, event category 12552 maps to the Network Policy Server service.
Importing the Data
After the scripts had run, we gathered the output CSV files to a separate machine running SQL Server 2008. We used the following script to import the CSV data into SQL:
LogParser.exe "SELECT * INTO DaSasTable FROM DA_*.csv" -i:CSV -o:SQL -server:dev1 -database:NapDa -createTable:ON -maxStrFieldLen:1023
LogParser.exe "SELECT * INTO HraSecurityEventsTable FROM HRA_Security_*.csv" -i:CSV -o:SQL -server:dev1 -database:NapDa -createTable:ON -maxStrFieldLen:1023
LogParser.exe "SELECT * INTO HraSystemEventsTable FROM HRA_System_*.csv" -i:CSV -o:SQL -server:dev1 -database:NapDa -createTable:ON -maxStrFieldLen:1023
Notes:
- The name of the SQL host machine is dev1. The database is named NapDa, and the database was created using the default values in SQL Management Studio.
- The three tables, DaSasTable, HraSecurityEventsTable and HraSystemEventsTable did not already exist. The -createTable:ONLog Parser command-line option specifies Log Parser to automatically create those tables with a suitable schema based on the input data (the event log CSV files, in this case).
- The -maxStrFieldLen:1023setting is important in this scenario. Without this setting, a default varchar field length of 255 would be used by Log Parser for the various event log string fields. However, the event log CSV format has some data strings that are longer than that (particularly in the Strings field; see Figure 2), and it’s important that they not be truncated. Experimentally, a default length of 1023 seems to be adequate.
Figure 2 shows the schema that resulted from the Log Parser CSV event log import.
.jpg)
Figure 2 Schema for the Log Parser CSV event log import.
Preparing the Data
In creating the DirectAccess health report, the primary challenge in terms of extracting the required data is that the event log CSV format is based on data strings. In the context of a GUI, the data are interleaved into static strings that describe the meaning of each data field. The data strings include everything interesting to a DirectAccess health report, including user names, machine names, policy group names and IP addresses.
The data strings are concatenated into a single CSV field, and eventually a single SQL column (again, strings). Each string is separated from the next with a “|” character. One option would be to tokenize the strings, before or immediately after importing the data into SQL. However, our preference was to instead parse the strings after they’re in SQL, then extract the few specific data items we needed and populate separate SQL tables with those items.
Accomplishing this task with string pattern matching with T-SQL is difficult. As an alternative, we used an approach documented in a previous MSDN Magazine article, “Regular Expressions Make Pattern Matching and Data Extraction Easier — implementing user-defined functions for SQL using C#, specifically for the purpose of regular expression-based pattern matching.
Using Visual Studio 2008, we followed the steps in that article almost exactly, although it was helpful to refer to additional documentation on getting initial SQL and CLR integration working with Visual Studio. Also, because of the inherent complexity of regular expressions (RegEx), it was helpful to refer to the documentation for that technology as well, particularly the section on grouping, as that’s the approach used by the sample code from the MSDN Magazine article.
The following code sample details the source code for the SQL user-defined function that exposes RegEx capabilities into our SELECT statements. The function is called RegexGroup, just like the one from the MSDN Magazine article. We made one modification in the first two lines of the function body so that we can check for NULL input values. Before we added these two lines, we encountered numerous exceptions because several of our SQL helper table columns (described here) have NULL values:
usingSystem;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Data.SqlTypes;
usingMicrosoft.SqlServer.Server;
usingSystem.Text.RegularExpressions;
publicpartialclassUserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
publicstaticSqlChars RegexGroup(
SqlChars input, SqlString pattern, SqlString name)
{
if (true == input.IsNull)
returnSqlChars.Null;
Regex regex = newRegex(pattern.Value, Options);
Match match = regex.Match(newstring(input.Value));
returnmatch.Success ?
newSqlChars(match.Groups[name.Value].Value) : SqlChars.Null;
}
};
The following SQL commands create and populate two helper tables consisting of strings extracted from the initial data set using RegEx:
/* Create the User-Computer-Address mapping helper table */
/* This step should only be performed once per data set */
CREATE TABLE UserComputerAndAddr
(
[RowN] int null,
[UserName] varchar(1023) null,
[ComputerName] varchar(1023) null,
[Address] varchar(1023) null
)
/* Populate the User-Computer-Address table */
/* This step should only be performed once per data set */
INSERT INTO UserComputerAndAddr(RowN, UserName, ComputerName, Address)
SELECT RowNumber,
dbo.RegexGroup([Strings],N'(?<un>[0-9a-zA-Z]+)@redmond.corp.microsoft.com',N'un'),
dbo.RegexGroup([Strings],N'(?<machine>[0-9a-zA-Z\-]+)(?<!CO1RED-TPM-01)\$@redmond.corp.microsoft.com',N'machine'),
dbo.RegexGroup([Strings],N'(?<ipv6addr>[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4}:[0-9a-fA-F]{1,4})',N'ipv6addr')
FROM [NapDa].[dbo].[DaSasTable]
/* Create the Computer-Health mapping helper table */
/* This step should only be performed once per data set */
CREATE TABLE ComputerHealth
(
[RowN] int null,
[TimeGenerated] datetime null,
[EventType] int null,
[ComputerName] varchar(1023) null
)
/* Populate the Computer-Health mapping table */
/* This step should only be performed once per data set */
INSERT INTO ComputerHealth(RowN, TimeGenerated, EventType, ComputerName)
SELECT RowNumber,
TimeGenerated,
EventType,
dbo.RegexGroup([Strings],N'REDMOND\\(?<machine>[0-9a-zA-Z\-]+)\$',N'machine')
FROM [NapDa].[dbo].[HraSystemEventsTable]
You can get a sense of the string patterns by examining the first SELECT statement and its use of the RegexGroup function installed with the technique that we described. Figure 3 details the three RegEx patterns that we defined.
Figure 3 The three RegEx patterns defined by using SQL commands.
| Regular Expression Pattern | Notes |
| User name | Example: ichiro@redmond.corp.microsoft.com |
| Computer name | Example: dan-dev-1@redmond.corp.microsoft.com Notice that we are explicitly excluding matches against the DirectAccess server itself in this pattern. |
| IPv6 address | Example: 2001:0:4137:1f6b:8c8:2f30:e7ed:73a8 · This pattern will not match all valid IPv6 addresses. You will need to enhance this pattern if you want to use it in other contexts. · While there are other embedded IPv6 address fields in the Strings column, the client address seemed to be the only one matching this pattern. You might need to revisit this if you get unexpected addresses in your queries. |
Together, those regular expressions help to create a table, which consists of the User, Computer, and Address information from each row in the DaSasTable (i.e., the IPsec SA events exported from the DirectAccess machine).
After the UserComputerAndAddr table is created and populated, a second table is created that maps computer name to event type for each row in the HraSystemEventsTable table. If you examine the computer name pattern, you will see that the computer name format is different in this log from the DirectAccess log. In this case, we are looking for strings like REDMOND\dan-dev-1. Figure 4 details the different events that might be present in the EventType field.
Figure 4 Event types that might be present in the EventType field.
| Event Type | Description |
| 0 | Success. The computer submitted a compliant NAP statement of health. |
| 1 | Failure. The computer was non-compliant with the NAP policy. |
In the health report for this deployment, we only expect to see event type 0. That’s because NAP is being used in enforcement mode. As you will see below, we are also filtering on successful IPsec security associations. If the client was non-compliant, it should not have been able to acquire a valid IPsec certificate and establish an SA. On the other hand, if your organization has deployed NAP in reporting mode, you will expect to see some non-compliant machines connected. The relative percentage of compliant versus non-compliant machines connected to the network is an important statistic to report.
Note: When using tables for RegEx results, we recommend that you use permanent SQL tables. If you use temporary tables (as we do for the demo in the next step) the RegEx queries will be slow.
Building the Report
With the regular expression parsing complete and the helper tables created, we can now focus on building the health report itself. Once the data has been prepared, writing the report queries is relatively easy.
The purpose of this sample report is to list all users who established a DirectAccess connection between 3 p.m. and 4 p.m. on May 5, 2010. Along with the user name, the report lists the computer name and health status.
In order to identify those users, we will start by querying for successful (event type 8) QM SAs (event category 12549) within that period. The QM SA event is useful in this scenario because IPsec has been configured to require second-factor authentication (the user’s credentials). We chose to use this reporting approach because QM SAs are relatively short-lived (an hour, in this case, with an inactivity timeout of five minutes) and hence are useful for auditing access. An MM SA, in contrast, only implies the use of the computer credential and persists for eight hours by default (although we recommend decreasing the MM lifespan if auditing is an important component of your DirectAccess deployment).
Unfortunately, the QM events do not include either the user name or machine name; they only include the IP address. To map the IP address to a machine name and user name, we can use a few SELECT statements in our SQL query. The first SELECT statement in the following query will return the list of addresses that appear in new QM SAs within that period. The second SELECT statement uses those addresses to map to the user name and computer name that are associated with that address elsewhere in the log. (This user/computer/address association is in the EM SA events. These events are critical for this exercise because they contain all three values; if you’re not requiring IPsec second-authentication, then you will not be able to do this type of reporting.)
/* The following steps build the report, based on the three imported tables
* plus the two helpers above. These steps can be run any number of times as
* you refine your query.
*/
/* Create a temporary table to populate as the final report */
CREATE TABLE #SaHealthReport
(
[UserName] varchar(1023) null,
[ComputerName] varchar(1023) null,
[HealthEventType] int null
)
/* Run a query to find all IPsec Quick Mode Security Associations established
* within a given period. Populate a temporary table with the client IPv6
* addresses. */
SELECT DISTINCT[Address] INTO #TempAddresses
FROM [NapDa].[dbo].[DaSasTable] JOIN [NapDa].[dbo].[UserComputerAndAddr]
ON RowN = RowNumber
WHERE [EventType]=8 AND
[EventCategory]=12549 AND
([TimeGenerated] BETWEEN'2010-05-10 15:00:00.000' AND '2010-05-10 16:00:00.000')
/* Map the QM SA addresses to user and computer names and insert those into
* the final report. */
INSERT INTO #SaHealthReport(UserName,ComputerName)
SELECT UserComputerAndAddr.UserName,UserComputerAndAddr.ComputerName
FROM [NapDa].[dbo].[UserComputerAndAddr] JOIN #TempAddresses
ON #TempAddresses.Address = UserComputerAndAddr.Address
WHERE (UserComputerAndAddr.UserName IS NOT NULL) AND (UserComputerAndAddr.ComputerName IS NOT NULL)
/* Populate the health column of the report. */
UPDATE #SaHealthReport
SET HealthEventType = ComputerHealth.EventType
FROM #SaHealthReport JOIN [NapDa].[dbo].[ComputerHealth]
ON #SaHealthReport.ComputerName = ComputerHealth.ComputerName
/* Display all rows and columns of the report. */
SELECT DISTINCT *
FROM #SaHealthReport
The final step in populating the SaHealthReport report tableis to correlate the HRA health information with the computer and user identity information, which has thus far come exclusively from the DirectAccess server. The incorporation of the HRA server information into this mix is a powerful tool that allows you to determine whether the computers connecting remotely to your network are introducing risk (due to non-compliance). See the UPDATE statement in the previous SQL query—the correlation between DirectAccess and HRA data is accomplished by using the client computer name.
Figure 5 shows sample data that could be returned from a completed health report.
Figure 5 A Sample Completed Health Report
| UserName | ComputerName | HealthEventType |
| Ichiro | ichiroadmin1 | 0 |
| Grinch | whoville-cli | 0 |
| Raquel | omybc | 0 |
You can establish reporting on IPsec (including DirectAccess) and NAP deployment relatively easily with some custom scripts and the LogParser tool. This approach will help a company start to create a secure framework for exposing line-of-business services, whether on-site or in the cloud.
.jpg)
Dan Griffin is a software security consultant based in Seattle. He can be reached at www.jwsecure.com
.jpg)
Lee Walker is the technology architect for Microsoft’s internal IT department, focusing on IPv6, IPsec, NAP, DirectAccess and other technologies. He can be contacted at lee.walker@microsoft.com.