Introduction (Deploying SQL Server Logging with Windows Server 2003 Internet Authentication Service (IAS))

Applies To: Windows Server 2003, Windows Server 2003 R2, Windows Server 2003 with SP1, Windows Server 2003 with SP2

SQL Server logging is a new feature for Internet Authentication Service (IAS) in Microsoft® Windows® Server 2003, Standard Edition; Windows Server 2003, Enterprise Edition; and Windows Server 2003, Datacenter Edition.

IAS is the Microsoft implementation of a Remote Authentication Dial-in User Service (RADIUS) server and proxy. As a RADIUS server, IAS performs centralized connection authentication, authorization, and accounting for many types of network access including wireless, 802.1X authenticating switch, and remote access dial-up and virtual private network (VPN) connections. As a RADIUS proxy, IAS forwards authentication and accounting messages to other RADIUS servers. RADIUS is an Internet Engineering Task Force (IETF) standard.

Figure 1 shows the components of a RADIUS infrastructure when the IAS server is configured to log to a computer running SQL Server.

Figure 1: Components of a RADIUS infrastructure

A RADIUS server has access to user account information and can check network access authentication credentials. If the user's credentials are authenticated and the connection attempt is authorized, the RADIUS server authorizes the user's access on the basis of specified conditions and logs the network access connection in an accounting log. The use of RADIUS allows the network access user authentication, authorization, and accounting data to be collected and maintained in a central location, rather than on each access server.

The centralization of accounting data simplifies both troubleshooting and the log file backup process. In addition, as networks and IAS deployments scale up and out, you can deploy IAS SQL Server logging to centralize logging for multiple IAS servers.

Using accounting logs

IAS accounting logs can be used for a variety of purposes, including the following:

• Connection troubleshooting

• Billing and auditing

• Report creation

• Session state information

IAS provides the following logging options to address these purposes:

Event logging for IAS. IAS errors are automatically recorded in the system event logs of Windows Server 2003, Standard Edition; Windows Server 2003, Enterprise Edition; and Windows Server 2003, Datacenter Edition. IAS authentication events are recorded in the system event log on the basis of event log settings, which you can customize for your logging needs. You can log rejected authentication requests, successful authentication requests, or both. Event logging is used primarily for auditing and troubleshooting connection attempts.

To configure event logging for IAS

1. Open the Internet Authentication Service Microsoft Management Console (MMC) snap-in.

2. Right-click Internet Authentication Service (IAS), and then click Properties.

3. On the General tab, select each required option, and then click OK.

For more information, see “Related Links” later in this white paper.

Logging user authentication and accounting requests to local text files. Recording user authentication and accounting requests in log files is used primarily for connection analysis and billing purposes, and is also useful as a security investigation tool, providing you with a method of tracking the activity of a malicious user after an attack. You can configure IAS to log in IAS format or database-import format, both of which are text file formats.

To select requests to be logged in IAS format or database-import format

1. Open the Internet Authentication Service snap-in.

2. In the console tree, click Remote access logging.

3. In the details pane, right-click Local File, and then click Properties.

4. On the Settings tab, select one or more check boxes for recording authentication and accounting requests in the IAS log files:

   • To capture accounting requests and responses, select Accounting requests.

   • To capture authentication requests, Access-Accept messages, and Access-Reject messages, select Authentication requests.

   • To capture periodic status updates, such as interim accounting packets, select Periodic status.

For more information, see “Related Links” later in this white paper.

IAS SQL Server logging. IAS SQL Server logging is used when you need session state information, for report creation and data analysis purposes, and to centralize and simplify management of your accounting data. Windows Server 2003 Internet Authentication Service provides the ability to use SQL Server logging to record user authentication and accounting requests received from one or more network access servers (NASs) to a data source on a computer running the Microsoft SQL Server Desktop Engine (MSDE 2000) or SQL Server 2000. Accounting data is passed from IAS in XML format to a stored procedure in an MSDE 2000 or SQL Server 2000 database, which supports both structured query language (SQL) and XML (SQLXML). Recording user authentication and accounting requests in an XML-compliant SQL Server database enables multiple IAS servers to share one data source, and provides the advantages of using a relational database.

Introduction to SQL Server 2000

Microsoft SQL Server is a relational database management and analysis product for e-commerce, line-of-business, and data warehousing solutions. SQL Server 2000 includes support for XML and HTTP, performance and availability features to partition loads and ensure uptime, and advanced management and tuning functionality to automate routine tasks and lower total cost of ownership. For more information, see “Related Links” later in this white paper.

Advantages of SQL Server logging

Logging to a SQL Server 2000 relational database instead of a standard text file (that is, either IAS format or database-compatible format) has the following advantages:

• Relationships between data tables enable the flexible creation of dynamic data views by using queries and reports.

• SQL Server 2000 has high-speed optimizations that can effectively support very large, terabyte-sized databases.

• Multiple bulk copy operations can be performed concurrently against a single table in SQL Server to speed data entry.

• The Transact-SQL BACKUP and RESTORE statements are optimized to read through a database serially and write in parallel to multiple backup devices. In addition, SQL Server 2000 supports differential backups, which replicate only the data changed after the last backup.

• You can configure SQL Server logging to provide failover and redundancy, and multiple IAS servers can log to the same database, providing ease of administration.

• Data from multiple IAS servers can be stored in the same database, providing a centralized data source for many IAS servers and the ability to achieve a more comprehensive data view for applications that query the database, such as Help Desk applications.

In addition, you can improve logging performance and, in some circumstances, reduce the cost of deploying IAS SQL Server logging by using MSDE 2000 databases installed on each IAS server.

Stored procedures

All three of the scenarios depicted in this white paper require the use of SQL Server 2000 stored procedures. A stored procedure is a group of Transact-SQL statements compiled into a single execution plan, or program. A stored procedure centralizes business rules, tasks, and processes within the server. When you use IAS SQL Server logging with MSDE 2000 or SQL Server 2000, the Transact-SQL programming language is the primary programming interface between your IAS server and the MSDE 2000 or SQL Server 2000 database.

When you use Transact-SQL programs, you must store the programs as stored procedures in MSDE 2000 and SQL Server 2000. IAS is configured to send data in XML format to a stored procedure called report_event, which then processes the results. Stored procedures in SQL Server 2000 and MSDE 2000 are similar to procedures and functions in other programming languages in that they can:

• Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

• Contain programming statements that perform operations in the database, including calling other procedures.

• Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

For more information, see “Related Links” later in this white paper.