Issue:
I am running smauditimport tool to import access log into MS SQL database, but the access log data is not being imported:
C:\Program Files (x86)\CA\siteminder\bin>smauditimport.exe c:\smaccess_2.log "AuditScan" auditstoreadmin password -fb -s6
Info : szDbmsName: Microsoft SQL Server
Info : bulk loading supported
Info : szDbmsName: Microsoft SQL Server
Info : bulk loading supported
Table Name = AccessLogTable SQLState = 4, Msg = [
Error : Insertion Failed on line 3.
Table Name = AccessLogTable SQLState = 4, Msg = [
Error : Insertion Failed on line 4.
Table Name = AccessLogTable SQLState = 4, Msg = [
Error : Insertion Failed on line 5.
[...]
Successfully inserted 0 rows to smaccesslog4 table.
Successfully inserted 0 rows to smobjlog4 table.
Total rows inserted: 0.
https://docops.ca.com/ca-single-sign-on/12-52-sp1/en/configuring/policy-server-configuration/configure-policy-server-data-storage-options/audit-data-import-tool-for-odbc
How can I fix this?
Environment:
Siteminder Policy Server 12.52.x
Cause:
The reason why the data insert failed is because the audit log schema has not been created as per the guide:
https://docops.ca.com/ca-single-sign-on/12-52-sp1/en/installing/install-a-policy-server/configure-odbc-databases-as-policy-session-key-and-audit-stores/configure-odbc-databases-as-audit-store/store-audit-logs-in-sql-server
Resolution:
Create the Audit Log Schema as per guide instructions:
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.
The CA Single Sign-On audit log store schema is created in the database.
https://docops.ca.com/ca-single-sign-on/12-52-sp1/en/installing/install-a-policy-server/configure-odbc-databases-as-policy-session-key-and-audit-stores/configure-odbc-databases-as-audit-store/store-audit-logs-in-sql-server
KD : TEC1309438