Audit data archiving error

Hi,

We recently purchased the DB Audit central repository bundle and 10-server license and have been setting it up to work with our database estate (MS SQL 2000/2005, DB2 v9.1 LUW and Sybase ASE 11.9.2).

We are first configuring it all to run in our test environment, to give us a feel of how everything hangs together before rolling it out to production. We have had success in setting up local audit databases for each of our database systems, however, when we setup the automatic archiving to the repository database (hosted on an instance of MS SQL 2005), via a DB Audit Alert Center job for our Sybase ASE 11.9.2, we get an error in the job log when using the data pump method, as below:

Code:

Connecting to Alert Center...

******************************************************
* Copyright (C) 2000-2007 SoftTree Technologies, Inc.*
******************************************************
* 24x7 Scheduler Java API v4.1. *
******************************************************
* Unauthorized use or distribution of this program *
* may result in severe civil and criminal penalties, *
* and will be prosecuted to the maximum extent *
* possible under the law. *
******************************************************

Getting properties of job #17...
Audit trail data archiving job
Error recipient: name@email.com
Error sender: name@email.com
Database profile: SYBASESERVER
Getting properties of database profile SYBASESERVER...
JDBC interface: ./drivers/syb/jconn3.jar
JDBC driver: com.sybase.jdbc3.jdbc.SybDriver
Database URL: jdbc:sybase:Tds:SYBASESERVER:5000/sybsecurity
User: DBAUDITACCOUNT
Getting properties of repository profile Repository Server...
Repository JDBC interface: ./drivers/mss/sqljdbc.jar
Repository JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
Repository Database URL: jdbc:sqlserver://REPOSITORYSERVER:1443;databaseName=DBAudit
Repository User: DBAUDITACCOUNT
Connecting to repository database...
Connected.
Connecting to repository database (aux)...
Connected.
Repository server type: Microsoft SQL Server
Archiving method: DATA PUMP
Connecting to database...
Connected
Retrieving catalog data...
Catalog query execution time: 0.0 seconds.
Pre-processing: Truncating staging table db_audit.STAGE$300001...
Pre-processing: Truncate query execution time: 0.11 seconds.
Validating staging table db_audit.STAGE$300001...
Archiving audit data from sybsecurity.dbo.sysaudits_01...
Audit time range: [None] to 2011-08-04 14:09:13.381 with timezone offset 0
Searching remote audit trail...
Copying records from remote audit trail into staging table...
Audit data archiving failed for table sybsecurity.dbo.sysaudits_01. Implicit conversion from data type nvarchar to binary is not allowed. Use the CONVERT function to run this query.
ERROR: Implicit conversion from data type nvarchar to binary is not allowed. Use the CONVERT function to run this query.
Table skipped...
Audit data archiving complete.

Disconnecting from database...
Disconnecting from repository database...
Closing Alert Center connection and terminating...
DATABASE: SYBASESERVER - audit data archiving complete. Tables processed: 1. Tables failed: 1. Audit records archived: 0. Processing time: 0 minutes and 3 seconds.

Total processing time: 0 minutes and 3 seconds.

I can see that the column names and data types are the same in both the sybsecurity.dbo.sysaudits_01 table (local audit database for Sybase) and the repository staging table db_audit.STAGE$300001

We are using the 'data pump' method to get the data across, as we cannot use linked servers due to the repository database being hosted on x64 and sybase 11.9.2 is on x86 and there aren't any Sybase x64 ODBC drivers for our old version of Sybase (without going down the expensive third-party route). The BCP method also fails, with a 'The connection is closed' error as below:

Code:

Connecting to Alert Center...

******************************************************
* Copyright (C) 2000-2007 SoftTree Technologies, Inc.*
******************************************************
* 24x7 Scheduler Java API v4.1. *
******************************************************
* Unauthorized use or distribution of this program *
* may result in severe civil and criminal penalties, *
* and will be prosecuted to the maximum extent *
* possible under the law. *
******************************************************

Getting properties of job #18...
Audit trail data archiving job
Error recipient: name@email.com
Error sender: name@email.com
Database profile: SYBASESERVER
Getting properties of database profile SYBASESERVER...
JDBC interface: ./drivers/syb/jconn3.jar
JDBC driver: com.sybase.jdbc3.jdbc.SybDriver
Database URL: jdbc:sybase:Tds:SYBASESERVER:5000/sybsecurity
User: DBAUDITACCOUNT
Getting properties of repository profile Repository Server...
Repository JDBC interface: ./drivers/mss/sqljdbc.jar
Repository JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
Repository Database URL: jdbc:sqlserver://REPOSITORYSERVER:1443;databaseName=DBAudit
Repository User: DBAUDITACCOUNT
Connecting to repository database...
Connected.
Connecting to repository database (aux)...
Connected.
Repository server type: Microsoft SQL Server
Archiving method: BULK LOAD
Connecting to database...
Connected
Getting repository database server name...
Repository server: [Unknown], assuming local server
Retrieving catalog data...
Audit data archiving failed. The connection is closed.
ERROR: The connection is closed.
Disconnecting from databases and Alert Center...

Any help/guidance to allow me to get this working would be much appreciated...

Kind Regards,
Adam.

Hi,

To correct the issue you will need to alter a couple of tables in DB_AUDIT schema in the repository database.
Firs t in the repository database run
SELECT object_name(id) AS table_name FROM syscolumns WHERE name = 'xactid'

In found tables alter 'xactid' column data type to nvarchar(14). Just in case... the tables are in DB_AUDIT schema.

Please let us know if that doesn't help

Thanks for the quick reply!

I've made the change as mentioned, and now the staging table is being populated with 6830 rows, however, the job is still failing as below:

Code:

Connecting to Alert Center...

******************************************************
* Copyright (C) 2000-2007 SoftTree Technologies, Inc.*
******************************************************
* 24x7 Scheduler Java API v4.1. *
******************************************************
* Unauthorized use or distribution of this program *
* may result in severe civil and criminal penalties, *
* and will be prosecuted to the maximum extent *
* possible under the law. *
******************************************************

Getting properties of job #17...
Audit trail data archiving job
Error recipient: name@email.com
Error sender: name@email.com
Database profile: SYBASESERVER
Getting properties of database profile SYBASESERVER...
JDBC interface: ./drivers/syb/jconn3.jar
JDBC driver: com.sybase.jdbc3.jdbc.SybDriver
Database URL: jdbc:sybase:Tds:SYBASESERVER:5000/sybsecurity
User: DBAUDITACCOUNT
Getting properties of repository profile Repository Server...
Repository JDBC interface: ./drivers/mss/sqljdbc.jar
Repository JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
Repository Database URL: jdbc:sqlserver://REPOSITORYSERVER:1443;databaseName=DBAudit
Repository User: DBAUDITACCOUNT
Connecting to repository database...
Connected.
Connecting to repository database (aux)...
Connected.
Repository server type: Microsoft SQL Server
Archiving method: DATA PUMP
Connecting to database...
Connected
Retrieving catalog data...
Catalog query execution time: 0.016 seconds.
Pre-processing: Truncating staging table db_audit.STAGE$300001...
Pre-processing: Truncate query execution time: 0.047 seconds.
Validating staging table db_audit.STAGE$300001...
Archiving audit data from sybsecurity.dbo.sysaudits_01...
Audit time range: [None] to 2011-08-08 14:13:35.781 with timezone offset 0
Searching remote audit trail...
Copying records from remote audit trail into staging table...
1000 records copied
1000 records copied
1000 records copied
1000 records copied
1000 records copied
1000 records copied
Audit data archiving failed for table sybsecurity.dbo.sysaudits_01. null
ERROR: null
Table skipped...
Audit data archiving complete.

Disconnecting from database...
Disconnecting from repository database...
Closing Alert Center connection and terminating...
DATABASE: SYBASESERVER - audit data archiving complete. Tables processed: 1. Tables failed: 1. Audit records archived: 0. Processing time: 0 minutes and 9 seconds.

Total processing time: 0 minutes and 9 seconds.

I'm guessing that it cannot process this data and move it into the db_audit.REP_SYS_AUDIT_TRAIL table?

Thanks,
Adam.

Have got a bit further after truncating the sysaudits_01 table and the db_audit.STAGE$300001 table.

The staging table is now populated, however, we're getting a 'You can only use SELECT or SELECT INTO with the special table.' error message:

The second hit when googling for this error brings us back here, to this thread: http://www.softtreetech.com/support/phpBB2/viewtopic.php?p=26370&sid=81d853dfd28685736fede9f47d29f228

However, I can't see a solution there either...

Here's the full report we're getting:

Code:

Connecting to Alert Center...

******************************************************
* Copyright (C) 2000-2007 SoftTree Technologies, Inc.*
******************************************************
* 24x7 Scheduler Java API v4.1. *
******************************************************
* Unauthorized use or distribution of this program *
* may result in severe civil and criminal penalties, *
* and will be prosecuted to the maximum extent *
* possible under the law. *
******************************************************

Getting properties of job #17...
Audit trail data archiving job
Error recipient: name@email.com
Error sender: name@email.com
Database profile: SYBASESERVER
Getting properties of database profile SYBASESERVER...
JDBC interface: ./drivers/syb/jconn3.jar
JDBC driver: com.sybase.jdbc3.jdbc.SybDriver
Database URL: jdbc:sybase:Tds:SYBASESERVER:5000/sybsecurity
User: DBAUDITACCOUNT
Getting properties of repository profile Repository Server...
Repository JDBC interface: ./drivers/mss/sqljdbc.jar
Repository JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
Repository Database URL: jdbc:sqlserver://REPOSITORYSERVER:1443;databaseName=DBAudit
Repository User: DBAUDITACCOUNT
Connecting to repository database...
Connected.
Connecting to repository database (aux)...
Connected.
Repository server type: Microsoft SQL Server
Archiving method: DATA PUMP
Connecting to database...
Connected
Retrieving catalog data...
Catalog query execution time: 0.0 seconds.
Pre-processing: Truncating staging table db_audit.STAGE$300001...
Pre-processing: Truncate query execution time: 0.047 seconds.
Validating staging table db_audit.STAGE$300001...
Archiving audit data from sybsecurity.dbo.sysaudits_01...
Audit time range: [None] to 2011-08-09 11:02:57.313 with timezone offset 0
Searching remote audit trail...
Copying records from remote audit trail into staging table...
Total audit data records copied: 37
process time: 0.141 seconds
performance: 262.41135 records per second
Deleting audit data from sybsecurity.dbo.sysaudits_01...
Leaving 60 days of data in the audit trail, deleting everything before 2011-06-10 11:02:57.313
Audit data archiving failed for table sybsecurity.dbo.sysaudits_01. You can only use SELECT or SELECT INTO with the special table.

ERROR: You can only use SELECT or SELECT INTO with the special table.

Table skipped...
Audit data archiving complete.

Disconnecting from database...
Disconnecting from repository database...
Closing Alert Center connection and terminating...
DATABASE: SYBASESERVER - audit data archiving complete. Tables processed: 1. Tables failed: 1. Audit records archived: 0. Processing time: 0 minutes and 1 seconds.

Total processing time: 0 minutes and 1 seconds.

Thanks in advance....

Adam.

Using the Central Repository Deployment Tool please change audit data replication options for Sybase database servers to not delete data from sysaud_NN tables, keep all days. If properly configured Sybase takes care of audit data deletion automatically. This will make the job not to execute DELETE command and resolve the issue

Hi,

Thanks for the info, this has resolved the 'You can only use SELECT or SELECT INTO with the special table.' issue we were getting and the replication is now working as expected.

Thanks again.

Regards,
Adam.

Hi. thank you for the status update. Glad to know it is now working for you. If anything else comes up, please let us know.