In IT analytics standaone server the account used for SQL agent connection requires sysadmin privileges

book

Article ID: 178590

calendar_today

Updated On:

Products

IT Analytics

Issue/Introduction

 

Resolution

As per ITA standalone server 2.1 administrator guide "SQL Server Agent Settings" section (p.36)

"The application ID specified in the IT Analytics Server Setup Wizard will need to be granted the SQL sysadmin privilege on the SQL Server hosting the IT Analytics database to allow for configuration of this option. This is done in SQL Server Management Studio by connecting to the SQL Server hosting the database, expanding Security > Logins and selecting Properties from the right click menu of the application ID. Select Server Roles and place a checkmark in the sysadmin box. These settings can be removed after the configuration process has completed."

Same guide "Processing Jobs" section (p.38) saying that

"To define a Processing Job, SQL sysadmin privileges are required. The privilege must remain in place or the job will fail on execution. If the SQL sysadmin privilege needs to be removed after the job has been created, the jobs details can be modified after it has been created by changing the Owner of the job in SQL Server Management Studio under the SQL Server Agent > Jobs folder. The new Owner will need SQL sysadmin privileges."

In real life once appID granted sysadmin rights, processing jobs are created and processed just fine. Once sysadmin rights removed, SQL agent connection lost and cubes failed to process. This is a result of Microsoft SQL Server’s requirements to manipulate the SQL Server Agent. Manipulating the agent acts on system databases so elevated privileges are required to do this. 

The application ID is always used to manage the UI for the SQL Server Agent. So when you remove it you can’t manipulate the agent anymore. That means you can’t view, edit, or delete existing SQL Server Agent Jobs in the GUI. You also can’t create new jobs from within the GUI without the sysadmin privilege. While this is true, any jobs that were created before the privilege was removed will continue to function, so long as you have taken the steps to change the Owner of the job to an account with sysadmin privileges.

From the SQL Server Agent Configuration Wizard perspective, there is a page that requests credentials.

These credentials are used to impersonate the account specified in Analysis Server for processing the cubes, not for managing the UI for SQL Server Agent. As I mentioned at the start, that manipulation of the agent always uses the application ID. This behavior can’t be changed.

 

We have tested with removing sysadmin from appID while changing job Owner from appId to 'sa' account. In that case, from console you may Create/Edit/Delete/Run jobs without issues, but once pressing Refresh button, we are getting SQL agent connection permission error and losing UI for jobs.

 

It was requested from development to modify existing documentation, so it won't confuse the product users.

 

Generally, we need to add sysadmin privileges for application ID.

 

Attachments