ACS 5.2 Configuring Remote Database Settings

[Mason Tu]

Hi,

I will deploy ACS 5.2 recently.

According to "User Guide for Cisco Secure Access Control System 5.2"

"Use this page to configure a remote database to which you can export the Monitoring & Report Viewer data. ACS exports data to this remote database at specified intervals. You can schedule the export job to be run once every 1, 2, 4, 6, 8, 12, or 24 hours. You can create custom reporting applications that interact with this remote database. ACS supports the following databases:

•Oracle SQL Developer

•Microsoft SQL Server 2005'

I will use MS SQL Server 2005, but I wonder if anyone use MS SQL 2008 for this function?

And there is a firewall between ACS and SQL Server.

Does anyone know what protocol should I open for the export?

Also, what kind of role for the SQL Server user should I create?

Thanks in advanced.

[RYFursenko]

please  help me.

i have ACS 5.2 whih patch :  5-2-0-26-6.

I tried to  use in  the remote database as  a MS sql 2008 and MS sql 2005.  All the time I have one and the same error on ACS server:

how to fix this error ?

[RYFursenko]

I wrote in the wrong topic? or nobody knows how to fix it?

ps sorry for my bad english .

[Mason Tu]

Hi Roman,

I got the same message in MonitoringAndReportingScheduler.log

Do you know how to fix this now?

Sep 16 2011 01:30:00 com.cisco.nm.acs.view.common.remotedatabase.ExportDataJob.execute(ExportDataJob.java:91) ERROR DefaultQuartzScheduler_Worker-1 Acs.MGMT.ACSVIEW Export Data : exception org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [HY000]; error code [-656];  

--- The error occurred in RemoteDatabaseMap.xml. 

--- The error occurred while applying a parameter map. 

--- Check the RemoteDatabaseMap.createProxyTable-InlineParameterMap. 

--- Check the statement (update failed). 

--- Cause: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server '172.24.3.202': [Sybase][ODBC Driver Manager] Unable to load driver /opt/CSCOacs/view/remotedb/lib/libtdsodbc.so; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:  

--- The error occurred in RemoteDatabaseMap.xml. 

--- The error occurred while applying a parameter map. 

--- Check the RemoteDatabaseMap.createProxyTable-InlineParameterMap. 

--- Check the statement (update failed). 

--- Cause: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server '172.24.3.202': [Sybase][ODBC Driver Manager] Unable to load driver /opt/CSCOacs/view/remotedb/lib/libtdsodbc.so

    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:121)

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)

    at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)

    at org.springframework.orm.ibatis.SqlMapClientTemplate.insert(SqlMapClientTemplate.java:397)

    at com.cisco.nm.acs.view.dbms.impl.RemoteDatabaseDAOImpl.createRemoteTables(RemoteDatabaseDAOImpl.java:317)

    at com.cisco.nm.acs.view.dbms.impl.RemoteDatabaseDAOImpl.exportDataToRemoteDatabase(RemoteDatabaseDAOImpl.java:179)

    at com.cisco.nm.acs.view.common.remotedatabase.ExportDataJob.execute(ExportDataJob.java:84)

    at org.quartz.core.JobRunShell.run(JobRunShell.java:202)

    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529)

Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:  

--- The error occurred in RemoteDatabaseMap.xml. 

--- The error occurred while applying a parameter map. 

--- Check the RemoteDatabaseMap.createProxyTable-InlineParameterMap. 

--- Check the statement (update failed). 

--- Cause: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server '172.24.3.202': [Sybase][ODBC Driver Manager] Unable to load driver /opt/CSCOacs/view/remotedb/lib/libtdsodbc.so

    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)

    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:447)

    at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:82)

    at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:399)

    at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)

    ... 6 more

Caused by: java.sql.SQLException: [Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server '172.24.3.202': [Sybase][ODBC Driver Manager] Unable to load driver /opt/CSCOacs/view/remotedb/lib/libtdsodbc.so

    at ianywhere.ml.jdbcodbc.IIPreparedStatement.execute(Native Method)

    at ianywhere.ml.jdbcodbc.IPreparedStatement.execute(IPreparedStatement.java:367)

    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)

    at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:81)

    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)

    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)

    ... 10 more

[RYFursenko]

I don't know yet...

[Mason Tu]

Hi Roman,

According to

https://supportforums.cisco.com/message/3445825

This may be a bug.

Hope it will be fixed in 5.3.

[rishi.sumbal]

We tried yesterday with 5.3, doesn't look better ...

[Mason Tu]

I have tried 5.2 FP7 and it works.

You have to download script file from ACS remote database setting page, then create DB on your own.

Then create a user and set the default database of the user to the DB you just create.

Try to give the user with DB owner privilege.

Good luck!!!