Installation and Upgrade Guides › Policy Server Installation Guide › Configuring SiteMinder Data Stores in a Relational Database › Configure SQL Server Data Stores › How to Store Audit Logs in SQL Server

How to Store Audit Logs in SQL Server

To configure a SQL Server database as a standalone audit logging database, complete the following procedures:

1. Gather Database Information.
2. Create the Audit Log Schema.
3. Configure a SQL Server Data Source for SiteMinder.
4. Point the Policy Server to the Database.
5. Restart the Policy Server.

Note: If you are trying to configure or upgrade a SiteMinder store listed in the SiteMinder Platform Support Matrix and cannot find the procedures in this guide, see the Directory Configuration Guide.

Gather Database Information

Configuring a single SQL Server database to function as a policy store or any other type of SiteMinder data store requires specific database information.

Note: Information prefixed with (W) indicates that the information is only required if the Policy Server is installed on a Windows system; (U) indicates that the information is only required if the Policy Server is installed on a UNIX system. Different information is required when configuring the SQL Server data source.

Gather the following information before configuring the policy store or any other type of SiteMinder data store. You can use the SQL Server Information Worksheet to record your values.

Note: Policy and data store worksheets are provided to help you gather and record information before configuring or upgrading a SiteMinder data store. You can print the applicable worksheet and can use it to record required information before beginning.

• Database instance name —Determine the name of the database instance that is to function as the policy store or data store.
• Administrative account —Determine the user name of an account with privileges to create, read, modify, and delete objects in the database.
• Administrative Password —Determine the password for the Administrative account.
• (W) Data source name —Determine the name you will use to identify the data source.

  Example: SM SQL Server Wire DS.

• (W) SQL Server name —Determine the name of the SQL Server database that contains the instance that is to function as the policy store.
• (U) Policy Server root —Determine the explicit path to where the Policy Server is installed.
• (U) IP Address —Determine the IP Address of the SQL Server database.

More information:

SQL Server Information Worksheet

Create the Audit Log Schema

You create the logging schema so the SQL Server database can store audit logs.

To create the audit log schema

1. Open sm_mssql_logs.sql in a text editor and copy the contents of the entire file.
2. Start the Query Analyzer and log in as the user who administers the Policy Server database.
3. Select the database instance from the database list.
4. Paste the schema from sm_mssql_logs.sql into the query.
5. Execute the query.

   The SiteMinder audit log store schema is created in the database.

Configure a SQL Server Data Source for SiteMinder

If you are using ODBC, you need to configure a data source to let SiteMinder communicate with the SiteMinder data store.

More information:

SQL Server Authentication Mode Considerations

Create a SQL Server Data Source on Windows

ODBC requires that you configure a data source for the SQL Server wire protocol.

Note: This procedure only applies if the Policy Server is installed on a Windows System.

Follow these steps:

1. Complete one of the following steps:
   • If you are using a supported 32–bit Windows operating system, click Start and select Programs, Administrative Tools, ODBC Data Sources.
   • If you are using a supported 64–bit Windows operating system:
     1. Navigate to C:\Windows\SysWOW64.
     2. Double–click odbcad32.exe.

   The ODBC Data Source Administrator appears.

2. Click the System DSN tab.

   System data source settings appear.

3. Click Add.

   The Create New Data Source dialog appears.

4. Select SiteMinder SQL Server Wire Protocol and click Finish.

   The ODBC SQL Server Wire Protocol Driver Setup dialog appears.

5. Enter the data source name in the Data Source Name field.

   Example: SiteMinder Data Source.

   Note: Take note of your data source name. This information is required as you configure your database as a policy store.

6. Enter the name of the SQL Server host system in the Server field.
7. Enter the database name in the Database Name field.
8. Click Test.

   The connection settings are tested and a prompt appears specifying that the connection is successful.

9. Click OK.

   The SQL Server data source is configured and appears in the System Data Sources list.

Create a SQL Server Data Sources on UNIX Systems

The SiteMinder ODBC data sources are configured using a system_odbc.ini file, which you create by renaming sqlserverwire.ini, located in policy_server_installation/db, to system_odbc.ini. This system_odbc.ini file contains all of the names of the available ODBC data sources as well as the attributes that are associated with these data sources. This file must be customized to work for each site. Also, you can add additional data sources to this file, such as defining additional ODBC user directories for SiteMinder.

The first section of the system_odbc.ini file, [ODBC Data Sources], contains a list of all of the currently available data sources. The name before the “=” refers to a subsequent section of the file describing each individual data source. After the “=” is a comment field.

Note: If you modify of the first line of data source entry, which is [SiteMinder Data Source], take note of the change because you will need this value when configure your ODBC database as a policy store.

Each data source has a section in the system_odbc.ini file describing its attributes. The first attribute is the ODBC driver to be loaded when this data source is used by SiteMinder. The remaining attributes are specific to the driver.

Adding a MS SQL Server Data source involves adding a new data source name in the [ODBC Data Sources] section of the file, and adding a section that describes the data source using the same name as the data source. You need to change the system_odbc.ini file if you create a new service name or want to use a different driver. You should have entries for the Oracle or SQL drivers under [SiteMinder Data Source].

Again, to configure a MS SQL Server data source, you must first create a system_odbc.ini file in the policy_server_installation/db directory. To do this, you need to rename sqlserverwire.ini, located in policy_server_installation/db, to system_odbc.ini.