Store Audit Logs in SQL Server
Contents
casso10
Contents
2
Gather Database Information
Configuring a single SQL Server database to function as a policy store or any other type of
CA Single Sign-On
data store requires specific database information. 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.
- Database instance nameDetermine the name of the database instance that is to function as the policy store or data store.
- Administrative account name and passwordDetermine the user name and password of an account with privileges to create, read, modify, and delete objects in the database.
- (W) Data source nameDetermine the name you will use to identify the data source.Example:SM SQL Server Wire DS.
- (W) SQL Server nameDetermine the name of the SQL Server database that contains the instance that is to function as the policy store.
- (U) Policy Server rootDetermine the explicit path to where the Policy Server is installed.
- (U) IP AddressDetermine the IP Address of the SQL Server database.
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
- Open sm_mssql_logs.sql in a text editor and copy the contents of the entire file.
- Start the Query Analyzer and log in as the user who administers the Policy Server database.
- Select the database instance from the database list.
- Paste the schema from sm_mssql_logs.sql into the query.
- Execute the query.TheCA Single Sign-Onaudit log store schema is created in the database.
Configure a SQL Server Data Source for
CA Single Sign-On
If you are using ODBC, you need to configure a data source to let
CA Single Sign-On
communicate with the CA Single Sign-On
data store.SQL Server Authentication Mode Considerations
CA Single Sign-On
data sources do not support Windows authentication. Configure the CA Single Sign-On
data source with the credentials of a user that is stored in the database. For more information about SQL Server authentication modes, see the vendor−specific documentation.
Create a SQL Server Data Source on Windows
ODBC requires that you configure a data source for the SQL Server wire protocol.
This procedure only applies if the Policy Server is installed on a Windows System.
Follow these steps:
- 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:
- Navigate to C:\Windows\SysWOW64.
- Double–click odbcad32.exe.
- Click the System DSN tab.System data source settings appear.
- Click Add.The Create New Data Source dialog appears.
- SelectCA Single Sign-OnSQL Server Wire Protocol and click Finish.The ODBC SQL Server Wire Protocol Driver Setup dialog appears.
- Enter the data source name in the Data Source Name field.Example:CA Single Sign-OnData Source.Note:Take note of your data source name. This information is required as you configure your database as a policy store.
- Enter the name of the SQL Server host system in the Server field.
- Enter the database name in the Database Name field.
- Click Test.The connection settings are tested and a prompt appears specifying that the connection is successful.
- 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
CA Single Sign-On
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 CA Single Sign-On
.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.
If you modify of the first line of data source entry, which is [
CA Single Sign-On
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
CA Single Sign-On
. 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 [
CA Single Sign-On
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.Configure the SQL Server Wire Protocol Driver
You configure the wire protocol driver to specify the settings the Policy Server uses to connect to the database.
This procedure only applies if the Policy Server is installed on a UNIX system. If you have not already done so, copy one of the following files and rename it
system_odbc.ini
:- sqlserverwire.ini
- oraclewire.ini
- mysqlwire.ini
- postgresqlwire.ini
- db2wire.ini
These files are located in
siteminder_home
/db.The system_odbc.ini file contains the following sections. The data source that you are configuring determine the section or sections that you edit:
- [SiteMinder Data Source]Specifies the settingsCA Single Sign-Onis to use to connect to the database functioning as the policy store.
- [SiteMinder Logs Data Source]Specifies the settingsCA Single Sign-Onis to use to connect to the database functioning as the audit log database.
- [SiteMinder Keys Data Source]Specifies the settingsCA Single Sign-Onis to connect to the database functioning as the key store.
- [SiteMinder Session Data Source]Specifies the settingsCA Single Sign-Onis to connect to the database functioning as the session store.
- [SmSampleUsers Data Source]Specifies the settingsCA Single Sign-Onis to connect to the database functioning as the sample user data store.
Follow these steps:
- Open the system_odbc.ini file.
- Enter the following under [ODBC Data Sources]:SiteMinder Data Source=DataDirect 8.0 SQL Server Wire Protocol
- Depending on the data source you are configuring, edit one or more of the data source sections with the following information. When editing data source information, do not use the pound sign (#). Entering a pound sign comments the information, which truncates the value. The truncated value can cause ODBC connections to fail.Driver=nete_ps_root/odbc/lib/NSsqls28.soDescription=DataDirect 8.0 SQL Server Wire ProtocolDatabase=SiteMinder DataAddress=host_ip, 1433QuotedId=NoAnsiNPW=NoDMCleanup=2
- nete_ps_rootSpecifies the explicit path of the Policy Server installation, rather than a path with an environment variable.Example:export/smuser/siteminder
- SiteMinder DataSpecifies the SQL Server database instance name.
- host_ipSpecifies the IP Address of the SQL Server database.
- 1433Represents the default listening port for SQL Server.
- Save the file.The wire protocol driver is configured.
Point the Policy Server to the Database
You point the Policy Server to the database so the Policy Server can read and store audit logs.
To point the Policy Server to the data store
- Open the Policy Server Management Console, and click the Data tab.Database settings appear.
- Select ODBC from the Storage list.ODBC settings appear.
- Select Audit Logs from the Database list.
- Select ODBC from the Storage list.Data source settings become active.
- Enter the name of the data source in the Data Source Information field.
- (Windows) this entry must match the name you entered in the Data Source Name field when you created the data source.
- (UNIX) this entry must match the first line of the data source entry in the system_odbc.ini file. By default, the first line in the file is [CA Single Sign-OnData Sources]. If you modified the first entry, be sure that you enter the correct value.
- Enter and confirm the user name and password of the database account that has full access rights to the database instance in the respective fields.
- Specify the maximum number of database connections allocated toCA Single Sign-On.We recommend retaining the default for best performance.
- Click Apply.The settings are saved.
- Click Test Connection.CA Single Sign-Onreturns a confirmation that the Policy Server can access the data store.
- Click OK.The Policy Server is configured to use the database as an audit logging database.
Restart the Policy Server
You restart the Policy Server for certain settings to take effect.
Follow these steps:
- Open the Policy Server Management Console.
- Click the Status tab, and click Stop in the Policy Server group box.The Policy Server stops as indicated by the red stoplight.
- Click Start.The Policy Server starts as indicated by the green stoplight.Note: On UNIX, execute the stop-ps and start-ps commands to restart Policy Server. To restart Policy Server and CA Risk Authentication, execute the stop-all and start-all commands.