new Permissions needed for monitoring

FrankSchneiderFrankSchneider Posts: 8
edited January 12, 2017 7:31AM in SQL Monitor 6
Today i have upgraded SQLMonitor to Version 6.
The installation was successful and without any issues.

Afterwards i try to resume monitored which was suspended before installation.
Unfortunately i got the error :

User 'xxxyyyyyyyyyyyy' does not have permission to run DBCC DBINFO.

To solve this i had to set sysadmin role to the "Monitor" - User ( 'xxxyyyyyyyyyyyy' ).

Is there a way to get the monitoring running without giving the sysadmin role ?
Due to the risk we dont want to have monitoring account with such high permissions !

regards, Frank
· Share on Twitter

Comments

  • Adam WAdam W Posts: 111 Silver 3
    Hi,

    You shouldn't have to enable sysadmin permissions - here are the details of the requirements:

    https://documentation.red-gate.com/disp ... ermissions

    Let me know if you need further details
    · Share on Twitter
  • FrankSchneiderFrankSchneider Posts: 8
    Hi Adam

    thanks for your answer.

    Unfortunately i am not able to figure out which are the specific flags :
    sysadmin role required for Integrity check overdue alerts and to allow SQL Monitor to turn on the deadlock trace flag (this flag is required for Deadlock alerts to be raised; you can turn on the flag manually if you don't want to enable sysadmin permissions).

    We are monitoring a 2 node AlwaysOn Cluster with 4 Instances.
    In every Instance the following Commands were executed :
    GRANT VIEW ANY DEFINITION TO [xxxxxyyyyyyyyyyyyyy]
    GRANT VIEW SERVER STATE TO [xxxxxyyyyyyyyyyyyyy]
    GRANT VIEW DATABASE STATE TO [xxxxxyyyyyyyyyyyyyy]

    But i still got the error (after removing the sysadmin role from the monitoring account) :
    IntegrityCheckSampler Incorrect credentials or insufficient permissions SqlException User 'xxxxxyyyyyyyyyyyyyy' does not have permission to run DBCC DBINFO.

    Could you please explain how to disable the IntegrityCheckSampler ?

    SQLMonitor - Version below Version 6 was running fine over the last 2 years without the sysadmin - role.

    regards, Frank
    · Share on Twitter
  • ALPHONSODELARDALPHONSODELARD Posts: 13 Bronze 2
    Did this get fixed?

    We've just purchased the product, and upgraded at the same time, to find all the SQL monitoring has stopped.

    We really don't want to give the monitoring account sysadmin, that's a backwards step?
    · Share on Twitter
  • FrankSchneiderFrankSchneider Posts: 8
    No - we still had this issue.

    Without sysadmin - role the Monitoring doesnt work.

    Hope that Red-Gate can help with an solution.
    · Share on Twitter
  • ALPHONSODELARDALPHONSODELARD Posts: 13 Bronze 2
    Thanks Frank.

    Redgate did provide me with a workaround yesterday, which fixes the issue, if you are happy to live without the db integrity check alert.

    Not sure we want to give sysadmin to a monitoring user - we use a least privilege principle, a monitoring account shouldn't need sysadmin.
    · Share on Twitter
  • Dan BDan B Posts: 287 Gold 5
    Hi,

    The solution to this is below, as AlphonsoDelard mentions this does disable the integrity check alert but will allow the rest of the monitoring to continue.

    The workaround for this is to disable the integrity check from within the sampler. Whilst the alert is disabled SQL Monitor still collects this data in case you want to turn the alert on and then have historical data.

    To turn this off navigate to the sql monitor install directory <C:Program FilesRed GateSQL MonitorBaseMonitor> and open the ConfigurationFileEditor.exe. Click to load a config file and select <C:ProgramDataRed GateSQL MonitorRedGate.Response.Engine.Alerting.Base.Service.exe.settings.config>.

    You can select the [cluster].[sqlserver].[database].[integritycheck] and select disabled from the dropdown. Then restart the SQL Monitor base monitor and this should stop this sampler running.
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
    · Share on Twitter
  • DBATrollmanDBATrollman Posts: 11 Bronze 5
    I see where the needed permissions are given in the documentation https://documentation.red-gate.com/sm7/getting-started/requirements/permissions but why has no one at redgate thought to create a script so that as I add server instances to the monitor I only have to run a user script to create the user that has all the right permissions to run the monitor without having to have sysadmin rights. I can not find a script that already has been written and all I need to do is put in the user account for our domain to use.
    · Share on Twitter
  • NhiNhi Posts: 1 New member

    Hi Dan,

    I tried to disable integrity check but I cannot find the config file <C:ProgramDataRed GateSQL MonitorRedGate.Response.Engine.Alerting.Base.Service.exe.settings.config> that you mentioned above anywhere. Could you provide more detail how to disable the Integrity check. I cannot grant sysadmin right for monitor account.

    Thank you,

    · Share on Twitter
  • AndriOAndriO Posts: 1 New member
    In SqlMonitor 12 it's on: "C:\Program Files\Red Gate\SQL Monitor\BaseMonitor\Tools\ConfigurationFileEditor.exe"

    The conf file to load:
    "C:\ProgramData\Red Gate\SQL Monitor\RedGate.SqlMonitor.Engine.Alerting.Base.Service.exe.settings.config"

    Conf to disable and save:
    [cluster].[sqlserver].[database].[integritycheck]
    · Share on Twitter
Sign In or Register to comment.