Permissions required to setup monitoring SQL jobs

Permissions required to setup Monitoring SQL Jobs

Published on : Sep 27, 2017

Category : BizTalk360 Update

Praveena

Author

Biztalk360 comes with a lot of exciting features in every release. One of the important functionalities in BizTalk360 is the monitoring with the autocorrect options. BizTalk360 is the one-stop monitoring solution for BizTalk server. We can not only monitor the artefacts, but also the SQL jobs. Yes, the SQL jobs present in the SQL server can also be monitored. We can also set the autocorrect (enable/disable) functionality for the SQL jobs.

Read more: View the History of Jobs with SQL Jobs History Feature

There may be separate servers for BizTalk databases and BizTalk360 database or even single server hosting all the databases. The jobs in all these servers can be monitored via BizTalk360. But then, can all the users have access to monitor and autocorrect the SQL jobs? In this blog, I am going to explain about the permissions required by the users for monitoring SQL jobs and setting autocorrect functionality which we learnt from one of the support tickets.

Customer’s case:

Our support team often get some interesting tickets which do not directly deal with the functionality and features of BizTalk360. Some tickets may be related to performance, access permissions, AD users etc. Each ticket experience is a new learning for our support engineers. Let’s see one such case of a customer related to the access permission for the databases in the SQL server. The customer got the below exception when they tried to set up monitoring for SQL jobs. Permissions required to setup monitoring SQL jobs The sp_help_job is the stored procedure used to list the SQL jobs running in the server. This job returns information about jobs that are used by SQL Server Agent to perform automated activities in SQL Server. There are SQL jobs that get installed and scheduled automatically to maintain the health of the BizTalk environment. BizTalk360 allows to set the threshold for SQL jobs (Monitoring -> Manage Mapping ->SQL Server Instances ->SQL Jobs) to list out those SQL jobs and perform the automatic operation this “sp_help_job” job is being used. The exception in the above screenshot comes because of a missing permission for the BizTalk360 service account while accessing the SQL server. We have our support article in place which describes about the permissions for the SQL jobs. The customer has given the permission according to this article. But they were facing the error again when trying to enable autocorrect feature for SQL jobs. Permissions required to setup monitoring SQL jobs In this error message, it says “Only members of sysadmin role are allowed to update or delete jobs owned by a different login” This means that only if the service account has the SYSADMIN permission, then it can enable/disable the sql jobs from BizTalk360. But some of the customers would not prefer to provide SYSADMIN permission for the service account due to some security policies. So, what happens in such case? Let’s go ahead and check the resolution given. Before that lets have a quick glance at SQl jobs and permissions.

The SQL jobs:

A job is a series of operations performed by SQL Server Agent sequentially. A job can run on one local server or on multiple remote servers. The jobs are used to define administrative tasks that can be run one or more times and monitored for success or failure. SQL server agent runs these scheduled jobs. A job can be edited only by its owner or by the members of the sysadmin role.
You can download this article as a PDF document Download now.

The SQL job permissions:

SQL server has the following msdb database fixed roles through which the SQL server can be accessed and controlled. The roles from least to most privileged are:
  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole
Can we have a brief look at each one of them?

SQLAgentUserRole:

This is the least privileged role. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multi-server jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own.

SQLAgentReaderRole:

This role includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multi-server jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own.

SQLAgentOperatorRole:

This is the most privileged role which includes all the permissions of the above-mentioned roles. They have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server. SQLAgentOperatorRole members cannot change job ownership to gain access to jobs that they do not already own. The below table summarizes some of the properties for all these roles.
Database Role Action – Create/modify/delete Action – Enable/Disable
Local Jobs Multiserver jobs Job schedules
SQLAgentUserRole Yes Yes (Owned jobs) No No Yes Yes (Owned schedules)
SQLAgentReaderRole Yes Yes (Owned jobs) No No Yes Yes (Owned schedules)
SQLAgentOperatorRole Yes Yes No No Yes (Owned schedules) Yes
Of all the above-mentioned SQL database roles, the SYSADMIN is the highest privileged role which has the administrator rights on the SQL server.

The resolution provided:

As mentioned earlier, the BizTalk360 service account would require the SYSADMIN permission to monitor and autocorrect the SQL jobs. But in some customer scenarios, they would not prefer to provide the SYSADMIN permissions. In that case, we need to see what is the minimum level of permission that we can provide to the service account for monitoring the SQL jobs. Our support team did an extensive testing to check for various scenarios and permissions for the service account. The outcome of the testing is given below: As the table summarizes, when the BizTalk360 service account is given the permissions as SQLAgentUserRole or SQLAgentReaderRole, it can only view the SQL jobs and cannot perform any operations on them. But when the SQLAgentOperatorRole is given for the service account, the auto correct functionality will work for the SQL jobs. The SYSADMIN permission is not required for this. This role is the highest privileged role next to the SYSADMIN. Permissions required to setup monitoring SQL jobs

Conclusion:

Hence, for setting the autocorrect functionality (enable/disable) the SQL jobs, the BizTalk360 service account needs to be given the SQLAgentOperatorRole permission to the system database, if SYSADMIN permission is not preferred to be given. PS: BizTalk360 will not do any operation by itself until monitoring has configured for any of the available SQL Jobs and enable the Auto-correction ability.  In case, you don’t wish to monitor the SQL jobs you can avoid the permissions shown in the above image. If you have any questions, contact us at support@biztalk360.com. Also, feel free to leave your feedback in our forum.
You can download this article as a PDF document Download now.