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

Products

• Clarity PPM On Premise

Issue/Introduction

The Datamart Extraction job fails with errors below in niku-bg.log when the SQL Server Agent is enabled in Microsoft SQL Server  
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)

This error has also been seen on some scenarios:

 "The Server Principal "NIKU" is not able to access the database "msdb" under the current security context"

Environment

Clarity

Cause

The SQL Server Agent is not configured.

Resolution

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:

Login as 'sa' user.

Open SQL Server 2005 or 2008 Management Console.

Open the Security folder.

Open Logins folder.

Right click on user SQLSERVER2005SQLAGENTUSER(2005)

Right click on user NT SERVICE\SQLSERVERAGENT(2008)

Select Properties.

Select User Mapping.

Check Map box for MSDB Database.

Confirm that the roles db_owner and public are checked.

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

Select Properties.

Select User Mapping.

Check Map box for MSDB Database.

Confirm that the roles db_owner and public are checked.

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 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.