Reporting Database Synchronization job fails in CCS Reporting and Analytics

Products

• Control Compliance Suite Databases MS SQL SRVR
• Control Compliance Suite Unix
• Control Compliance Suite Databases Oracle
• Control Compliance Suite Netware
• Control Compliance Suite Exchange
• Control Compliance Suite Windows

Issue/Introduction

Reporting Database Synchronization job fails in CCS Reporting and Analytics with multiple failures

Symptoms
SSIS MS errors.

This is pre CCS SP1 as prerequisite for installing SP1 state that all modules must function including Report Database Synch jobs.
SA is assigned the GPO per AD (logon as a service is restricted by GPO but when viewing lists of enforcement in the GPO, SA is listed as having rights to logon and run a service.

Attempts to launch scripts but the run fails due to privilege error
SA seems to have read /write privileges on csm db and msbd db all according to the doc attach

 

Cause

Logon as a service restricted by GPO in Security Settings Local Policy Logon a s Service - Replace process level token

Resolution

Remove this restriction



Technical Information
Verify the account running the SQL Agent service has the following permissions in Local Security Policy. See bullets below:

Windows user rights -

Typically, the default installation of the operating system gives the Local Administrators Group all the user rights that SQL Server requires to function correctly. Therefore, local Windows NT accounts or domain accounts that have been added to the Local Administrators Group, with the intent of being the startup account for the SQL Server service, have all the user rights that they require. However, we do not recommend that you run SQL Server under such high user rights.

For SQL Server 2005, if you do not want the SQL Server or the SQL Server Agent startup account to be a member of the Local Administrators Group, see the "Reviewing Windows NT Rights and Privileges Granted for SQL Server Service Accounts" section in the "Setting Up Windows Service Accounts" topic in SQL Server 2005 Books Online.

For SQL Server 2000, if you do not want the SQL Server or the SQL Server Agent startup account to be a member of the Local Administrators Group, then the startup account for the MSSQLServer service and the SQLServerAgent service (either a local Windows NT account, or a domain Windows NT account) must have these user rights:
• Act as Part of the Operating System = SeTcbPrivilege
• Bypass Traverse Checking = SeChangeNotify
• Lock Pages In Memory = SeLockMemory
• Log on as a Batch Job = SeBatchLogonRight
• Log on as a Service = SeServiceLogonRight
• Replace a Process Level Token = SeAssignPrimaryTokenPrivilege

How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005