Clarity: Any Clarity Job will not work when the SQLServer Agent is enabled in Microsoft SQL Server 2005 and 2008.

Document ID : KB000050306
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Applies To

Microsoft SQL Server 2005 and Microsoft SQL 2008
Clarity 8.1 and above

Symptom

The Datamart Extraction job fails with errors below in niku-bg.log when the SQL Server Agent is enabled in Microsoft SQL Server 2005
Caused by: java.sql.SQLException: [CA Clarity][SQLServer JDBC
Driver][SQLServer]The SELECT permission was denied on the object 'sysjobs_view', database 'msdb', schema 'dbo'.
at com.ca.clarity.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.ca.clarity.jdbc.base.BaseExceptions.getException(Unknown Source)

Solution:

The Clarity administration account must be added to the SQLAgentUserRole role for "SQL Server Agent" in Microsoft SQL Server 2005 (SQLSERVER2005SQLAGENTUSER), and for Microsoft SQL Server 2008(NT SERVICE\SQLSERVERAGENT) in order to use the "SQL Server Agent" in Microsoft SQL Server. Users added to the role will have the same SQL Server Agent experience as they had in SQL Server 2000. These users can create jobs and manage only jobs that they created.

The SQL Server Enterprise Manager tool can be used to add the Clarity administrator to the SQLAgentUserRole role by using the following steps:

  1. Login as 'sa' user.

  2. Open SQL Server 2005 or 2008 Management Console.

  3. Open the Security folder.

  4. Open Logins folder.

  5. Right click on user SQLSERVER2005SQLAGENTUSER(2005)

    1. Right click on user NT SERVICE\SQLSERVERAGENT(2008)

  6. Select Properties.

  7. Select User Mapping.

  8. Check Map box for MSDB Database.

  9. Confirm that the roles db_owner and public are checked.

  10. Right click on the Clarity schema owner, for example "niku" or "clarity".

  11. Select Properties.

  12. Select User Mapping.

  13. Check Map box for MSDB Database.

  14. Confirm that the roles db_owner and public are checked.

  15. Click OK to save changes.

The Clarity Administration and Configuration Guide for Clarity 8.1 were updated with the following information.
Note: For Microsoft SQL Server 2005/08 with the SQL Server Agent enabled, you must add the Clarity administrator account to the SQLAgentUserRole role to
run datamart extraction jobs. See Microsoft SQL Enterprise Manager documentation for details on adding user accounts to the SQLAgentUserRole role.