SiteMinder iRecorder can not retrieve any events from the SQL Log database because of Invalid column name 'eventid'

Products

• CA Security Command Center
• CA Data Protection (DataMinder)
• CA User Activity Reporting
• CA Single Sign On Secure Proxy Server (SiteMinder)
• CA Single Sign On SOA Security Manager (SiteMinder)
• CA Single Sign-On

Issue/Introduction

Description:

After completing all SiteMinder iRecorder configuration Steps no events are retrieved although events have been written to both SiteMinder's Audit Log Tables:

- smaccesslog4 - smaobjlog4 After running the SiteMinder iRecorder in debug mode, SmRecorder.log located in CA\Program Files\Shared Components\iTechnology reports the following error:

06/13/08 07:42:33: TRACE: [0x034b77c8] OdbcRecorderUtil::InitOdbc: [CSmAccessRecUtil Log] successfully attached to source [eTrust]
06/13/08 07:42:33: ERROR: [0x034b77c8] CSmRecorderUtil::RunQuery: [CSmAccessRecUtil Log] Select error; query <SELECT  sm_timestamp, sm_categoryid,
sm_eventid, sm_hostname, sm_sessionid, sm_username, sm_agentname, sm_realmname, sm_realmoid, sm_clientip, sm_domainoid, sm_authdirname, 
sm_authdirserver, sm_authdirnamespace, sm_resource, sm_action, sm_status, sm_reason, sm_transactionid, sm_domainname, sm_impersonatorname,
sm_impersonatordirname, eventid FROM         smaccesslog4 WHERE   eventid > 0 ORDER BY eventid ASC> failed
06/13/08 07:42:33: ERROR: [0x034b77c8] CSmRecorderUtil::RunQuery: [CSmAccessRecUtil Log] SQLExecDirect failed 42S22, 
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'eventid'.
 
06/13/08 07:42:33: TRACE: [0x034b77c8] OdbcRecorderUtil::TermOdbc: [CSmAccessRecUtil Log] detached from source [eTrust]
 

Solution:

To enable Audit Integration with SiteMinder, a SiteMinder database update needs to be performed.
sm_mssql_logs_eaudit_upgrade.sql script can be found in SiteMinderInstallDir\db\SQL

To run this script, proceed with the following steps:

- Stop iTechnology iGateway Service
- Stop SiteMinder Policy Server
- Launch SQL Query Analyzer and connect with sa and select the SiteMinder Log database
- From the File Menu, select Open and search for sm_mssql_logs_eaudit_upgrade.sql script in the directory specified above
- From the Query Tab, select execute
- Start SiteMinder Policy Server
- Start iTechnology iGateway Service

Environment

Release:
Component: ADTIRE