What are the minimum permissions needed to monitor SQL 2008 Clusters with an active directory account? The question is to monitor and discover SQL clusters not what perms are needed to change or write to anything via tasks. We don't want OpsMgr to have rights to fix, write, or change anything related to SQL, just read and report (alerts, change health state). We understand that a local account will not be able to make calls outside of the local box so we will be using an AD account. Is the assumption that the permissions below are what’s needed correct? Windows Server 2008 · Logon Local Read and connect and read to: · This would assume that remote regestry has been granted · HKLM\Software\Microsoft\Microsoft SQL Server\ · HKLM\Software\Wow6432Node\Microsoft\Microsoft SQL Server\ · WMI Namespace root/cimv2 · WMI Namespace/root/Microsoft/SqlServer Member of local groups: · Performance Monitor Users · Event Log Readers SQL Server 2008 SQL Permissions: · Run sp_help_job in the msdb database · VIEW ANY DEFINITION · VIEW SERVER STATE · CONNECT Connect rights to all databases in the instance: · (sp_helpdb"dbname") · Select from sys.databases table Account should be a member of this role: · SQLAgentReaderRole Thanks Karpakam Karpakam Balasubramanian

I have the same question.

Have you tried local administrator and SA on SQL instance?HTH, Jonathan Almquist - MSFT

Yes, and it works great : ) . I think the question here is "What are the minimum permissions needed to monitor SQL 2008 Clusters with an active directory account?" I'm not sure if there is anything more elevated then local admin and SA. May I ask what is it that the local admin group and SA profile have that is needed to run the discovery and rule/monitor workflows against a SQL cluster that can't be granted to an AD account that does not have permissions to write or change anything. Once the community knows we can write and share a PowerShell script to get people past the problem of DBA's not wanting to give away the keys to their kingdom (which in an enterprise environment is unheard of). Should I be telling our DBA team that this product won’t work with SQL clusters unless they grant the SQL action accounts local admin and sysadmin? I don’t need an MS supported solution, I can support it. I just need one that technically works and don’t have time to do the testing for the people that authored it. I think it would also be considered reverse engineering if I were to unseal the MP to figure out how it works, then test it which would break the EULA (might be wrong about that, not sure). Anyways any guidance would be much appreciated.

Page 21 of the latest SQL MP guide talks about setting up least-privileged monitoring, but it also states that it doesn't work for clustered SQL instances. Kevin Holman goes into detail about SQL MP profiles here: http://blogs.technet.com/b/kevinholman/archive/2010/09/08/configuring-run-as-accounts-and-profiles-in-r2-a-sql-management-pack-example.aspx As far as I know, and since the product group didn't offer guidance about least-privileged monitoring on clustered SQL instances, the only way to make it work is with SA. Now, this can be a domain account, and this service account can be managed by the SQL team (the SCOM admin doesn't need to know the credentials), so your SQL admin isn't necessarily giving up the keys. I do understand where you're coming from, though. We should allow a least-privileged monitoring environment. And if it's possible, the vendor should offer guidance around configuring this.HTH, Jonathan Almquist - MSFT

Thank you Jonathan for your reply. :) Understood, we are on the same page. I have read it and suggested the "you manage the account" idea. It's not that we "own" the account that’s the issue, it's that OpsMgr might do something that the DBA's don't want it to do and if something is changed, corrupted, written to the only option is to fix it after the fact, not prevent it from happing in the first place (due such elevated permission). Is this something you, Dan or Vlad can take up with the SQL team? It's kind of a big deal to large enterprise IT. Is this the best place to find out this info or should I be working through our TAM? I come here so that others can benefit. Thanks Man Ian

Hi Ian, Just to set your DBA at ease, there is nothing in the SQL MP that updates a database that it monitors. We do run some queries to gather performance data, like DB size and other metrics necessary for performance and availability monitoring, but we definited don't do anything that could potentially alter the SQL instance. That's not to say a customer could not author their own MP to do this, but that's another story - and in this case, we obviously need to trust the developer and thoroughly inspect any MP for this type of action before importing it. The SQL team is aware that this is a complaint from the community.HTH, Jonathan Almquist - MSFT

The argument that guns aren’t dangerous people with guns are comes to mind here. I say people with unloaded guns are ok by our DBAs :) Microsoft.SQLServer.2008.Databse.SetDBOffline Microsoft.SQLServer.2008.StopSQLAgent Microsoft.SQLServer.2008.StopSQLAgentFromJob Microsoft.SQLServer.2008.StopSQLAgentFromDBEngineInstance Microsoft.SQLServer.2008.StopClusteredSQLAgent Microsoft.SQLServer.2008.StopClusteredSQLAgentFromJob Microsoft.SQLServer.2008.StopClusteredSQLAgentFromDBEngine Microsoft.SQLServer.2008.AnalysisServices.StopService Microsoft.SQLServer.2008.AnalysisServices.StopClusteredService Microsoft.SQLServer.2008.StopSQLFullTextSearchService Microsoft.SQLServer.2008.IntegrationServices.StopService Microsoft.SQLServer.2008.ReportingServices.StopService Microsoft.SQLServer.2008.ReportingServices.StopClusteredService Microsoft.SQLServer.2008.DBEngine.StopService Microsoft.SQLServer.2008.DBEngine.StopClusteredService I guess if we don't have the answer at the moment thats ok. I would just like to know when we can expect it. Thanks, Ian

Recoveries and tasks are different. There shouldn't be any recoveries enabled OoB, and tasks require humans with sufficient privileges to execute them. HTH, Jonathan Almquist - MSFT

Understood. At the moment no guidance will be offered from Microsoft on what permissions are necessary to monitor SQL clusters without granting local administrator and sys admin via OpsMgr R2. Thank you anyway for your time. I know it's hard to please everyone especially with limited resources. I'll make this a weekend project and post back if I'm able to come up with something. Thanks again Ian

In the meantime, I did escalate this thread to the product group.HTH, Jonathan Almquist - MSFT

Thank you so much Jon for escalating this. And Thanks Ian for your time, interest and effort on this thread. Really appreciate it. Karpakam Balasubramanian

I totally agree with the earlier posts. It would be great if SQL (and other products/roles) surfaced all necessary information (for discovery and monitoring) in WMI, Registry, Performance Counters etc. That way we could even avoid read rights (which are a problem with sensitive information) on SQL itself. This is naturally a larger issue (instrumenting for monitoring) but it would make DBAs and security happier. /RogerThis posting is provided "AS IS" with no warranties, and confers no rights.

I noticed that MS recommends using the local system account for the cluster instead of a domain account. If we do this, don't we then need to include NT AUTHORITY\SYSTEM in SQL logins with SA priveleges? Isn't this a security issue?

Localsystem will not be able to make calls to the the virtual computer network name. It uses the cluster name to make calls, not the active node name. Localsystem doesn't have rights to do this. Where do you see this recomendation?