Menu Close

SQL MP Run As Accounts – NO LONGER REQUIRED

image

 

Download:  https://gallery.technet.microsoft.com/SQL-Server-RunAs-Addendum-0c183c32

 

 

Over the years I have written many articles dealing with RunAs accounts.  Specifically, the most common need is for monitoring with the SQL MP.  I have explained the issues and configurations in detail here:  Configuring Run As Accounts and Profiles in OpsMgr – A SQL Management Pack Example

 

Later, I wrote an automation solution to script the biggest pain point of RunAs accounts:  distributing them, here:  Automating Run As Account Distribution – Finally! 

 

Now – I want to show a different approach to configuring monitoring for the SQL MP, which might make life a lot simpler for SCOM admins, and SQL teams.

 

What if I told you – there was a way to not have to mess with RunAs accounts and the SQL MP at all?  No creating the accounts, no distributing them, no associating them with the profiles – none of that?    Interested?   Then read on.

 

The big challenge in SQL monitoring is that the SCOM agent runs as Local System for the default agent action account.  However, Local System does not have full rights to SQL server, and should not ever be granted the SysAdmin role in SQL.  This is because the Local System account is quite easy to impersonate to anyone who already has admin rights to the OS.

We can solve this challenge, by introducing Service SID’s.  SQL already uses Service Security Identifiers (SID’s) to grant access for the service running SQL server, to the SQL instance.  You can read more about that here:  https://support.microsoft.com/en-us/kb/2620201

Service SID’s were introduced in Windows Server 2008 and later.

 

We can do the same thing for the SCOM Healthservice.  This idea was brought to me by a fellow MS consultant – Ralph Kyttle.  He pointed out, this is exactly how OMS works to gather data about SQL server.  We have an article describing this recommended configuration here:  https://support.microsoft.com/en-us/kb/2667175

 

Essentially – this can be accomplished in two steps:

  1. Enable the HealthService to be able to use a service SID.
  2. Create a login in SQL for the HealthService SID to be able to access SQL server.

 

That’s it!

This creates a login in SQL, and allows the SCOM agent to be able to monitor SQL server, without having to maintain another credential, deal with password changes, and removes the security concern of a compromised RunAs account being able to access every SQL server in the company!  No more configuration, no more credential distribution.

 

In order to set the SCOM healthservice to be able to leverage a service SID:

sc sidtype HealthService unrestricted

To query the setting and ensure it is set to unrestricted:

sc qsidtype HealthService

To verify in the registry, look at:  HKLM\SYSTEM\CurrentControlSet\Services\HealthService

image

 

 

Management Pack:

I wrote a Management Pack to make setting this initial configuration up much simpler.

 

Let me demonstrate:

 

First, we need to ensure that all SCOM agents, where SQL is discovered – have the service SID enabled.  I wrote a monitor to detect when this is not configured, and targeted the SQL SEED classes.  For each SQL version, there is an Addendum MP which shows the SEED classes:

 

image

image

 

 

This monitor will show a warning state when the Service SID is not configured for any agent where we discover an instance of a SQL SEED class.

 

The monitor has a script recovery action, which is disabled by default.  You can enable this and it will automatically configure this as soon as SQL is detected, and will restart the agent.

 

image

 

This monitor is configured to alert, however there is an included override to disable the alerting.  You can delete the override, or override it yourself to enforce alerting on this monitor.

 

Additionally – I wrote a task you can run.  The task configures the service SID and then restarts the agent Healthservice.  You can multi-select items in this view and run against multiple agents, making this one-time configuration easy.

 

image

 

Here is what it looks like in action:

 

image

 

So – once that is complete – we can create the login for SQL.

In the Addendum MP – there is a state view for the DB engine.  If you switch to this view, or any Database Engine view – you will see two new tasks show up which will create a SQL login for the HealthService.  One creates the login and assigns it the SysAdmin role to the instance.  The other creates the login and configures the login for Lowest Privilege mode.  You just need to choose whichever you want to use for your organization.  Low Priv SQL scripts are documented at the end of this article.

 

image

 

If you run this task, and don’t have rights to the SQL server – you will get this:

 

image

 

Have your SQL team run the task and provide a credential to the task that will be able to create a login and assign the necessary SysAdmin role to the service:

 

image

 

NOTE*** Keep in mind, on SCOM 2019 – ANY account you use for tasks MUST have “Log on as a service” right on the target machines. 

 

Voila!

 

image

 

***NOTE – this task will always report success.  You NEED to review the output to ensure that it didn’t throw any errors.

 

What this actually does – is create this login on the SQL server, and then set it to have the rights necessary for the SQL MP to work.

 

image

 

All of these activities are logged for audit in the Task Status view:

 

image

 

The current SQL MP as of this article update (7.0.15.0) has a REALLY good monitor to help you know that you applied the configuration correctly.  There is a monitor targeting the DB engine class, that shows if any security rights are missing:

image

 

 

Now – as new SQL servers are added over time – the Service SID can automatically be configured using the recovery, and the SQL team will just need to add the HealthService login as part of their build configuration, or run this task one time for each new SQL server to enable it for monitoring.

 

I find this to be much simpler than dealing with RunAs accounts, and it appears to be a more secure solution as well.  I welcome any feedback on this approach, or for my Management Pack Addendum.

 

 

I have included my SQL RunAs Addendum MP’s to be available below:

 

https://gallery.technet.microsoft.com/SQL-Server-RunAs-Addendum-0c183c32

 

 

Here are the actual SQL scripts if you prefer to run these manually or have automation already in place:

 

--(SQL2012 and later) --Begin Query USE [master]; SET NOCOUNT ON; DECLARE @accountname sysname = 'NT SERVICE\HealthService'; -- Create the server role and grant instance level permissions CREATE SERVER ROLE [SCOM_HealthService]; GRANT VIEW ANY DATABASE TO [SCOM_HealthService]; --GRANT ALTER ANY DATABASE TO [SCOM_HealthService]; --Required only for SCOMDB tasks GRANT VIEW ANY DEFINITION TO [SCOM_HealthService]; GRANT VIEW SERVER STATE TO [SCOM_HealthService]; DECLARE @createLoginCommand nvarchar(200); SET @createLoginCommand = ' CREATE LOGIN '+ QUOTENAME(@accountname) +' FROM WINDOWS WITH DEFAULT_DATABASE=[master];' EXEC(@createLoginCommand); -- Add the login to the user defined server role EXEC sp_addsrvrolemember @loginame = @accountname, @rolename = 'SCOM_HealthService' -- Add the login and database role to each database DECLARE @createDatabaseUserAndRole nvarchar(max) SET @createDatabaseUserAndRole = ''; SELECT @createDatabaseUserAndRole = @createDatabaseUserAndRole + ' USE ' + QUOTENAME(db.name) + '; CREATE USER ' + QUOTENAME(@accountname) + ' FOR LOGIN ' + QUOTENAME(@accountname) + '; CREATE ROLE [SCOM_HealthService]; EXEC sp_addrolemember @rolename = ''SCOM_HealthService'', @membername = '+ QUOTENAME(@accountname) + '' -- 'ALTER ROLE [SCOM_HealthService] ADD MEMBER ' + QUOTENAME(@accountname) + ';' FROM sys.databases db LEFT JOIN sys.dm_hadr_availability_replica_states hadrstate ON db.replica_id = hadrstate.replica_id WHERE db.database_id != 2 AND db.user_access = 0 AND db.state = 0 AND db.is_read_only = 0 AND (hadrstate.role = 1 or hadrstate.role is null); EXEC(@createDatabaseUserAndRole) -- Add database specific permissions to database role USE [master]; GRANT EXECUTE ON sys.xp_readerrorlog TO [SCOM_HealthService] USE [msdb]; GRANT SELECT on [dbo].[sysjobschedules] TO [SCOM_HealthService]; GRANT SELECT on [dbo].[sysschedules] TO [SCOM_HealthService]; GRANT SELECT on [dbo].[sysjobs_view] TO [SCOM_HealthService]; GRANT SELECT on [dbo].[log_shipping_primary_databases] TO [SCOM_HealthService]; GRANT SELECT on [dbo].[log_shipping_secondary_databases] TO [SCOM_HealthService]; GRANT SELECT on [dbo].[log_shipping_monitor_history_detail] TO [SCOM_HealthService]; GRANT SELECT on [dbo].[log_shipping_monitor_secondary] TO [SCOM_HealthService]; GRANT SELECT on [dbo].[log_shipping_monitor_primary] TO [SCOM_HealthService]; GRANT EXECUTE on [dbo].[sp_help_job] TO [SCOM_HealthService]; GRANT EXECUTE on [dbo].[sp_help_jobactivity] TO [SCOM_HealthService]; EXEC sp_addrolemember @rolename='SQLAgentReaderRole', @membername='SCOM_HealthService'; EXEC sp_addrolemember @rolename='PolicyAdministratorRole', @membername='SCOM_HealthService'; --End of Query

--(SQL2005 and SQL 2008 on WS2008 and later) --(Will Not work on WS2003 - but you can use NT AUTHORITY\SYSTEM for the accountname) --Begin Query SET NOCOUNT ON; DECLARE @accountname nvarchar(128); DECLARE @command1 nvarchar(MAX); DECLARE @command2 nvarchar(MAX); DECLARE @command3 nvarchar(MAX); SET @accountname = 'NT SERVICE\HealthService'; SET @command1 = 'USE [master]; CREATE LOGIN ['+@accountname+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master];'; SET @command2 = ''; SELECT @command2 = @command2 + 'USE ['+name+']; CREATE USER ['+@accountname+'] FOR LOGIN ['+@accountname+'];' FROM sys.databases db WHERE db.database_id <> 2 AND db.user_access = 0 AND db.state = 0 AND db.is_read_only = 0; SET @command3 = 'USE [master]; GRANT VIEW ANY DATABASE TO ['+@accountname+']; GRANT VIEW ANY DEFINITION TO ['+@accountname+']; GRANT VIEW SERVER STATE TO ['+@accountname+']; GRANT SELECT on sys.database_mirroring_witnesses to ['+@accountname+']; USE [msdb]; EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';'; EXECUTE sp_executesql @command1; EXECUTE sp_executesql @command2; EXECUTE sp_executesql @command3; --End Query

 
 

 

Troubleshooting:

 

1.  These are required for Service SID’s to work for the SCOM Healthservice and SQL Monitoring:

  • Health Service SID needs to be enabled in the registry.  (ServiceSidType = 1)
  • Health Service must be restarted in order for SID’s to work after enabling service SID in registry.
  • A SQL login must be created for NT SERVICE\HealthService at the instance level.
  • The proper rights must be granted to NT SERVICE\HealthService at the instance level. (low priv script or sysadmin)
  • Each database to be monitored must have a User present of NT SERVICE\HealthService at the individual database level (if not sysadmin)
  • NT AUTHORITY\SYSTEM is recommended to be present as a SQL login, and must NOT be set to “Login:Disabled” in the SQL status for the Login.
  • NT AUTHORITY\SYSTEM is not absolutely required to be present on stand-alone SQL servers as a SQL login, but is required for Clustered and AlwaysOn configurations.

2.  If you are still getting alert errors for MSSQL on Windows: Discovery error or MSSQL on Windows: Monitoring error then check to ensure the problem is rights.  Verify that you did not miss any steps in enabling service SID, the agent was restarted, and ALL required security configurations are met above.

 

image

44 Comments

  1. Bijesh NS

    Hi Kevin,

    We have imported the SQL Server RunAs Addendum Management Pack version 7.0 in our SCOM 2012 R2 environment and performed the steps mentioned in the article. Also removed the association of SQL Run As Accounts with SQL Run AS profiles and deleted the SQL Run As accounts from SCOM. However we are receiving Discovery failed and Monitoring failed alerts from few of the SQL Servers.
    Alert Details as follows:

    Discovery Failed alert
    Event ID: 7105. Management Group: XXXX. Script: DiscoverSQL2016FileGroups.js. Version: 7.0.7.0. Instance: xxxx : File Groups Discovery script ‘DiscoverSQL2016FileGroups.js’ for instance ‘xxxx’ failed.
    Call stack:Exception.constructor(File Groups Discovery script ‘DiscoverSQL2016FileGroups.js’ for instance xxxx failed.,Can’t execute query ‘ SET NOCOUNT ON USE [xxxx] SELECT fg.name as fileGroupName, fg.data_space_id as fileGroupId, fg.is_read_only as fileGroupReadOnly, fg.type as fileGroupType, fg.type_desc as fileGroupTypeDesc FROM sys.filegroups fg’: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal “NL\SERVERNAME$” is not able to access the database “xxxx” under the current security context.
    Inner exception:
    Error Number : -2147467259
    Error Code : 16389
    Win32 Facility : 0

    Error Description : [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal ” NL\SERVERNAME $” is not able to access the database “xxxx” under the current security context.
    ),
    ADODB.ExecuteQuery( SET NOCOUNT ON USE [_Analysis_621] SELECT fg.name as fileGroupName, fg.data_space_id as fileGroupId, fg.is_read_only as fileGroupReadOnly, fg.type as fileGroupType, fg.type_desc as fileGroupTypeDesc FROM sys.filegroups fg),
    Main({BE4E5CA8-17A8-B2ED-4646-174851F381EC},{908DBFF1-4566-DF43-FF6F-68575A26C5B9},FQDN of the Server,Servername\DB name,DB,1113),
    anonymous(Object),
    ),
    ——————————————————————————————————————-

    Event ID: 7105. Management Group: XXXX. Script: DiscoverSQL2016FileGroups.js. Version: 7.0.7.0. Instance: : File Groups Discovery script ‘DiscoverSQL2016FileGroups.js’ for instance ‘EAUDIT’ failed.
    Call stack:Exception.constructor(File Groups Discovery script ‘DiscoverSQL2016FileGroups.js’ for instance ‘EAUDIT’ failed.,Can’t execute query ‘ SET NOCOUNT ON USE [93750bd0-78ba-47b8-a397-5a5b875c5df1] SELECT fg.name as fileGroupName, fg.data_space_id as fileGroupId, fg.is_read_only as fileGroupReadOnly, fg.type as fileGroupType, fg.type_desc as fileGroupTypeDesc FROM sys.filegroups fg’: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal “NT AUTHORITY\SYSTEM” is not able to access the database “XXXX” under the current security context.
    Inner exception:
    Error Number : -2147467259
    Error Code : 16389
    Win32 Facility : 0

    Event ID: 7105. Management Group: XXXX. Script: DiscoverSQL2016MirroringWitness.vbs. Version: 7.0.7.0. Instance: xxxx : Mirroring witness discovery script ‘DiscoverSQL2016MirroringWitness.vbs’ for instance ‘xxxx’ failed.
    The next errors occurred:
    Query execution failed: [Microsoft][SQL Server Native Client 11.0][SQL Server]The SELECT permission was denied on the object ‘database_mirroring_witnesses’, database ‘mssqlsystemresource’, schema ‘sys’.

    Monitoring Failed alerts:

    Event ID: 4001. Management Group: XXXX. Script: GetSQL2012SPNState.vbs. Version: 7.0.7.0 : The Query ‘SELECT ProtocolName FROM ServerNetworkProtocol where Enabled = true and InstanceName = ‘xxxx” did not return any valid instances. Please check to see if this is a valid WMI Query.. The remote procedure call failed.

    Event ID: 4001. Management Group: XXXX. Script: DiscoverSQL2008DBEngineDiscovery.vbs. Version: 7.0.7.0 : The Query ‘select * from SqlService where SQLServiceType =’1” did not return any valid instances. Please check to see if this is a valid WMI Query.. The remote procedure call failed.

    Event ID: 4211. Management Group: xxxx. Script: GetSQL2016DBFreeSpace.vbs. Version: 7.0.7.0 : The next errors occurred:
    Cannot connect to database ‘xxxx’
    Error Number: -2147467259
    Description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal “NT AUTHORITY\SYSTEM” is not able to access the database “xxxx” under the current security context.
    Instance: xxxx

    Event ID: 4001. Management Group: XXXX. Script: GetSQL2012DBFilesFreeSpace.vbs. Version: 7.0.7.0 : The next errors occurred:
    Cannot get a list of enabled Sql Server protocols
    Error Number: -2147023170
    Description: The remote procedure call failed.
    Instance: xxxx

    Apart form the above alerts we are also receiving the “Workflow Initialization: Failed to start a workflow that runs a process or script” alert.

    Data was found in the output, but has been dropped because the Event Policy for the process started at 8:48:36 AM has detected errors.
    The ‘ExitCode’ policy expression:
    [^0]+
    matched the following output:
    -1

    Command executed: “C:\Windows\system32\cscript.exe” /nologo “DiscoverSQL2016MirroringWitness.vbs” {83BE1EAC-0674-5424-1245-921FAB4F73A2} {848D52D8-D81E-0F86-D14E-685C84B5CBAE} FQDN \DB “Exclude:” “1113”
    Working Directory: C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Monitoring Host Temporary Files 17256\18776\

    One or more workflows were affected by this.
    Workflow name: Microsoft.SQLServer.2016.Mirroring.WitnessDiscovery
    Instance name: xxxx
    Instance ID: {848D52D8-D81E-0F86-D14E-685C84B5CBAE}
    Management group: XXXX

    Also whenever a new Database is added to a SQL Server, do we need to re-run the task/SQL query to create Health Service Login with Low Priv for that New Database on the SQL Server?”

    Please advise us on this

    Regards,

    Bijesh NS

    • Konstantin

      Yes you have if you are using “Health Service Login with Low Priv “. How should the new database know about the user? That’s why I uses the HS as sysadmin because otherwise it is an huge operational overhead in our environments.

      Regards
      Konstantin

      • Kevin Holman

        You can still use Low Priv easily – when you set the login to the model DB – this will exist in all new DB’s created. No overhead.

  2. Patrik

    Implementing this with low priv and are seeing some “securable status is bad”
    Anything you have seen ? Number one is most confusing beacuse i can see that the Healtservice login do have view any definition as a securable when looking in SSMS.

    1. Server-level permissions:
    • VIEW ANY DEFINITION

    2. Views:
    • sys.dm_db_xtp_checkpoint_files
    • sys.dm_db_xtp_table_memory_stats
    • sys.dm_db_xtp_hash_index_stats
    • msdb.dbo.sysjobschedules
    • msdb.dbo.log_shipping_primary_databases
    • msdb.dbo.log_shipping_secondary_databases

    3. Stored procedures:
    • sys.xp_readerrorlog
    • msdb.dbo.sp_help_jobactivity
    • msdb.dbo.sp_help_job

      • Kevin Holman

        Keep in mind – 7.0.9.0 and 7.0.13.0 are not intended for large production use – those are community technical preview MP’s meant for testing prior to the production MP’s releasing.

        The current SQL MP as of this comment is 7.0.7.0

  3. Cyr Az

    Hi Kevin,

    The SQL 2016 MP guide now includes a whole section about configuring Health Service SID, which in my opinion is great since it will ease the burden of SQL monitoring for much more people (at least for those who read documentation !).

    One question though : the first step in the guide is to run the “sc sidtype” command, equivalent to the task included in your MP.
    But then they add permissions to Component Services and WMI, which is not discussed in this article and logically not included in your MP neither.

    However I’ve been using your method since you released it and I didn’t notice much issues even though I never added these permissions… Can you give us more info about what their purpose is?

    Thanks!

    • Kevin Holman

      I’ll have to look into that, at one time due to the MP design – WMI permissions were required from one agent in a cluster/AlwaysOn configuration to another, as the agent would connect and query the partner nodes remotely – but I am not sure that is still the case and I requested the PG not do that, as it breaks the simple configuration of being able to use Service SID’s….. as these are almost “configuration free”.

      • Cyr Az

        They actually are configuration free once you override the sidtype command to run automatically and add the SQL scripts in your “new SQL instance” template 🙂

  4. David Bowles

    Hi Kevin, just came across this post and I believe it is going to save me a lot of work moving forward. Can I just confirm that once I have completed the steps listed above, am I then OK to remove the legacy associated account(s) from the various SQL run-as profiles and ultimately, delete the account as well?

    • Kevin Holman

      I would remove them from the associations in the Profiles first – then test and make sure everything works – before deleting the RunAs accounts. But yes.

  5. Rodrick Makore

    Hi Kevin,

    Thanks for the article.

    I implemented this and removed the Run As accounts completely. However, I do have some agents which appear as not monitored. How do I enforce them to be monitored?

  6. Kumar B

    Hi kevin,

    We have SQL Server RunAs Addendum Management Pack version 7.0 in our SCOM 2012 R2, we have been receiving frequent alerts related to Discovery & Monitoring Failed.
    we really require your assistance on this issue as alerts has more number of Repeat counts.

    Discovery Failed alert
    Event ID: 7105. Management Group: XXXX. Script: DiscoverSQL2016FileGroups.js. Version: 7.0.7.0. Instance: xxxx : File Groups Discovery script ‘DiscoverSQL2016FileGroups.js’ for instance ‘xxxx’ failed.
    Call stack:Exception.constructor(File Groups Discovery script ‘DiscoverSQL2016FileGroups.js’ for instance xxxx failed.,Can’t execute query ‘ SET NOCOUNT ON USE [xxxx] SELECT fg.name as fileGroupName, fg.data_space_id as fileGroupId, fg.is_read_only as fileGroupReadOnly, fg.type as fileGroupType, fg.type_desc as fileGroupTypeDesc FROM sys.filegroups fg’: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal “NL\SERVERNAME$” is not able to access the database “xxxx” under the current security context.
    Inner exception:
    Error Number : -2147467259
    Error Code : 16389
    Win32 Facility : 0

    Error Description : [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal ” NL\SERVERNAME $” is not able to access the database “xxxx” under the current security context.
    ),
    ——————————————————————————————————————-

    Event ID: 7105. Management Group: XXXX. Script: DiscoverSQL2016FileGroups.js. Version: 7.0.7.0. Instance: : File Groups Discovery script ‘DiscoverSQL2016FileGroups.js’ for instance ‘EAUDIT’ failed.
    Call stack:Exception.constructor(File Groups Discovery script ‘DiscoverSQL2016FileGroups.js’ for instance ‘EAUDIT’ failed.,Can’t execute query ‘ SET NOCOUNT ON USE [93750bd0-78ba-47b8-a397-5a5b875c5df1] SELECT fg.name as fileGroupName, fg.data_space_id as fileGroupId, fg.is_read_only as fileGroupReadOnly, fg.type as fileGroupType, fg.type_desc as fileGroupTypeDesc FROM sys.filegroups fg’: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal “NT AUTHORITY\SYSTEM” is not able to access the database “XXXX” under the current security context.
    Inner exception:
    Error Number : -2147467259
    Error Code : 16389
    Win32 Facility : 0

    Event ID: 7105. Management Group: XXXX. Script: DiscoverSQL2016MirroringWitness.vbs. Version: 7.0.7.0. Instance: xxxx : Mirroring witness discovery script ‘DiscoverSQL2016MirroringWitness.vbs’ for instance ‘xxxx’ failed.

    ———————————————————————————-

    Monitoring Failed alerts:

    Event ID: 4001. Management Group: XXXX. Script: GetSQL2012SPNState.vbs. Version: 7.0.7.0 : The Query ‘SELECT ProtocolName FROM ServerNetworkProtocol where Enabled = true and InstanceName = ‘xxxx” did not return any valid instances. Please check to see if this is a valid WMI Query.. The remote procedure call failed.

    Event ID: 4001. Management Group: XXXX. Script: DiscoverSQL2008DBEngineDiscovery.vbs. Version: 7.0.7.0 : The Query ‘select * from SqlService where SQLServiceType =’1” did not return any valid instances. Please check to see if this is a valid WMI Query.. The remote procedure call failed.

    Event ID: 4211. Management Group: xxxx. Script: GetSQL2016DBFreeSpace.vbs. Version: 7.0.7.0 : The next errors occurred:
    Cannot connect to database ‘xxxx’
    Error Number: -2147467259
    Description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal “NT AUTHORITY\SYSTEM” is not able to access the database “xxxx” under the current security context.
    Instance: xxxx

    Event ID: 4001. Management Group: XXXX. Script: GetSQL2012DBFilesFreeSpace.vbs. Version: 7.0.7.0 : The next errors occurred:
    Cannot get a list of enabled Sql Server protocols
    Error Number: -2147023170
    Description: The remote procedure call failed.
    Instance: xxxx

    Apart form the above alerts we are also receiving the “Workflow Initialization: Failed to start a workflow that runs a process or script” alert.

    Data was found in the output, but has been dropped because the Event Policy for the process started at 8:48:36 AM has detected errors.
    The ‘ExitCode’ policy expression:
    [^0]+
    matched the following output:
    -1

    Command executed: “C:\Windows\system32\cscript.exe” /nologo “DiscoverSQL2016MirroringWitness.vbs” {83BE1EAC-0674-5424-1245-921FAB4F73A2} {848D52D8-D81E-0F86-D14E-685C84B5CBAE} FQDN \DB “Exclude:” “1113”
    Working Directory: C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Monitoring Host Temporary Files 17256\18776\

    One or more workflows were affected by this.
    Workflow name: Microsoft.SQLServer.2016.Mirroring.WitnessDiscovery
    Instance name: xxxx
    Instance ID: {848D52D8-D81E-0F86-D14E-685C84B5CBAE}
    Management group: XXXX

  7. Tom

    Having an issue I hope someone can help with. I set this all up per the article.. I have a few working as the article states.. I have more not working then working..

    Example.. I have SQL 2016 Seed State View.. I have 31 servers listed in here (along with some cluster names)..

    I have enabled the SidState on a few for testing.. On one, I enabled it and it shows health in the Seed view.. I then go to the DB Engine view and that server is not listed there.. It’s been over an hour..

    Any ideas on what I can look at?

    Thanks!

    • Kevin Holman

      Look in the OperationsManager event log on the agents for clues. DB engine discovery is script based, and logged. It is likely you have issues with your default agent action account you are using (not using Local System?). I have not seen where Local System could not discover the engine.

      • Andy C

        This. From the SRS logs ..

        library!ReportServer_0-2!1a88!05/29/2019-15:58:10:: i INFO: Call to GetItemTypeAction(/). User: NT AUTHORITY\SYSTEM.
        library!ReportServer_0-2!1a88!05/29/2019-15:58:10:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AuthorizationExtensionException: , Microsoft.ReportingServices.Diagnostics.Utilities.AuthorizationExtensionException: An error occurred when invoking the authorization extension. —> System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: Value does not fall within the expected range.

        and from the MS logs at the same moment.

        GetUserRolesForOperationAndUser
        uuid:0a4d46e5-0ba7-42b0-8b7d-1de0fca46980;id=83
        Value does not fall within the expected range.

  8. David Culebras

    just updated to 2019 and all SQL monitoring stoped working.

    you have to re-execute the sidstate* Task. and all works again.

  9. Ferran

    Hi Kevin,

    excuseme this is not a question about your SQL MP, do you know if is safe to install SQL SERVER 2016 MP on a SCOM 2012 R2 ??

    Im worried about loss the compatibility with the original and old SQL Server 2008-2012 versions.

    Can I have both installed ?

    Best regards

  10. FERRAN

    Hello Kevin,

    thank you very much for your answer, your opinion is always the most valuable to me.

    I think the most prudent thing is to wait for your article, I’m worried about losing features with old versions of SQL server.

    and I do not want to compromise the stability of my SCOM 2012 R2

    best Regards!

  11. Andrew Reynolds

    Maybe I’m missing something here and I am very new to scom but when I run the enable healthService SiDState and then go to the DB view the server isn’t there for me to run the Create HealthService Login task?

    TYVM BTW for the great Blog posts

    • Kevin Holman

      This means your SQL SEED Class is being discovered, but your SQL DB Engine is not being discovered. You’d need to focus on why. There are events in the logs on the agent to help you understand what is failing.

  12. Mario

    Hi Kevin,
    will there be a sql version agnostic version of this MP? Currently e.g. the “Microsoft SQL Server 2014 RunAs Addendum Management Pack” still needs the old “Microsoft SQL Server 2014 (Discovery)” MP, which I normally do not need for Discovery, because I have the new (7.0.15.0) SQL MP installed

    • kevinholman

      The sql2017 one works right now. However, I am lunching a new one specifically for the agnostic version out soon.

  13. Sree

    Hi Kevin,

    We have imported the SQL Server RunAs Addendum Management Pack version 7.0 in our SCOM 2012 R2 environment and performed the steps mentioned in the article to monitor SQL Servers with HealthService Login as Low Priv mode. Also removed the association of SQL Run As Accounts with SQL Run AS profiles and deleted the SQL Run As accounts from SCOM. However we are receiving Discovery failed and Monitoring failed alerts from few of the SQL Servers.

    In the below Alert descriptions it is mentioned “The server principal “NT AUTHORITY\SYSTEM” is not able to access the database”, but now as we are using “HealthService” login for monitoring SQL Servers, why is “NT AUTHORITY\SYSTEM” being used to access the database. Are we missing any step(s) to perform, it would be of great help if you could respond.

    Some Alert Descriptions:

    MSSQL 2016: Discovery failed
    —————————-
    Event ID: 7105. Management Group: MG01. Script: DiscoverSQL2016FileGroups.js. Version: 7.0.7.0. Instance: : File Groups Discovery script ‘DiscoverSQL2016FileGroups.js’ for instance ” failed.
    Call stack:Exception.constructor(File Groups Discovery script ‘DiscoverSQL2016FileGroups.js’ for instance ” failed.,Can’t execute query ‘ SET NOCOUNT ON USE [1a7c0b6a-9a36-4ee5-8cac-272bbe8e69f3] SELECT fg.name as fileGroupName, fg.data_space_id as fileGroupId, fg.is_read_only as fileGroupReadOnly, fg.type as fileGroupType, fg.type_desc as fileGroupTypeDesc FROM sys.filegroups fg’: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal “NT AUTHORITY\SYSTEM” is not able to access the database “” under the current security context.
    Error Description : [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal “NT AUTHORITY\SYSTEM” is not able to access the database “” under the current security context.

    MSSQL: Monitoring failed
    ————————
    Event ID: 4001. Management Group: NLSCOM_MG01. Script: GetSQL2008SPNState.vbs. Version: 7.0.7.0 : The Query ‘SELECT ProtocolName FROM ServerNetworkProtocol where Enabled = true and InstanceName = ‘EAUDIT” did not return any valid instances. Please check to see if this is a valid WMI Query.. The remote procedure call failed.

    MSSQL 2016: Monitoring warning
    ——————————
    Event ID: 4211. Management Group: NLSCOM_MG01. Script: GetSQL2016DBFilesFreeSpace.vbs. Version: 7.0.7.0 : The next errors occurred:
    Cannot connect to database ‘model’
    Error Number: -2147467259
    Description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal “NT AUTHORITY\SYSTEM” is not able to access the database “model” under the current security context.
    Instance:

    • srnone

      Is anyone facing the SQL:Discovery failed and SQL:Monitoring failed alerts issue after importing the SQL Server Run-As Addendum Management Pack. We are facing this issue from quite some time, even though we performed the steps exactly as mentioned in this blog.

      As per the KB of the alerts it states
      ————————————-
      Causes
      The most common cause of monitoring workflow warnings is lack of permissions to monitor SQL Server.

      Since we have implemented SQL Server Run-As Addendum Management Pack to have HealthService Login to be created and to monitor the SQL Servers in Low Privilege mode, then why is the other Logins like “Domain Name\$” or “NT AUTHORITY\SYSTEM” being used by the SQL Server Management Pack for Disocvery and/or Monitoring. Is the SQL Server Run As Profile trying to use the “Domain Name\$” or “NT AUTHORITY\SYSTEM”, since now SQL Server Run As accounts are not longer available in SCOM as well as not associated with SQL Run As Profiles.

      Can anyone help on this issue.

      E.g of few Alert descriptions we are receiving frequently.

      Alert Description
      ———————-
      The server principal “NT AUTHORITY\SYSTEM” is not able to access the database “model” under the current security context.
      Instance: XXXX

      Alert Description
      ———————
      Cannot connect to database ‘model’
      Error Number: -2147467259
      Description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal “Domain Name\$” is not able to access the database “model” under the current security context.
      Instance: XXXX

      Alert Description
      ———————
      Event ID: 4211. Management Group: NLSCOM_MG01. Script: DiscoverSQL2016DBEngineDiscovery.vbs. Version: 7.0.7.0 : The next errors occurred:
      Unable to open WMI Namespace ‘winmgmts:\\\root\Microsoft\SqlServer’. Check to see if the WMI service is enabled and running, and ensure this WMI namespace exists.:

      The Query ‘select * from __NAMESPACE where Name =’ComputerManagement13” returned an invalid result set. Please check to see if this is a valid WMI Query.: Object required

      Alert Description
      ———————
      Event ID: 4001. Management Group: NLSCOM_MG01. Script: GetSQL2012SPNState.vbs. Version: 7.0.7.0 : The Query ‘SELECT ProtocolName FROM ServerNetworkProtocol where Enabled = true and InstanceName = ‘XXXX” did not return any valid instances. Please check to see if this is a valid WMI Query.. The remote procedure call failed.

      • Kevin Holman

        These are required for Service SID’s to work for the SCOM HealthService and SQL Monitoring:

        Health Service SID needs to be enabled in the registry. (ServiceSidType = 1)
        Health Service must be restarted in order for SID’s to work after enabling service SID in registry.
        A SQL login must be created for NT SERVICE\HealthService at the instance level.
        The proper rights must be granted to NT SERVICE\HealthService at the instance level. (low priv script or sysadmin)
        Each database to be monitored must have a User present of NT SERVICE\HealthService at the individual database level (if not sysadmin)
        NT AUTHORITY\SYSTEM when present as a SQL login, must NOT be set to “Login:Disabled” in the SQL status for the Login.
        NT AUTHORITY\SYSTEM does not need to be present on stand alone SQL servers as a SQL login, but is required for Clusters and AlwaysOn

        If you are still getting alert errors for MSSQL on Windows: Discovery error or MSSQL on Windows: Monitoring error then check to ensure the problem is rights. Verify that you did not miss any steps in enabling service SID, the agent was restarted, and ALL required security configurations are met above.

        Since you are getting the error from WMI – I’d start there. What does that WMI query return when you run it manually?

  14. M.Mathew

    Hi Kevin,

    What needs to be done to get this working with SQL Servers in DMZ. Agents have been installed & are communicating with SCOM MS, through a Gateway.Agents are running under “Local System” Account.

    • M.Mathew

      It was pretty straightforward. Just need to use an account which has Rights on SQL Server in DMZ to run the task to create “Health Service login was Low Prov Task”. Thanks

    • SRNONE

      Kevin, Thank you for the reply! We will check all the steps you have mentioned in your reply.

      Question: We also have few alerts i guess you missed those alerts in my earlier post which are mentioned below.
      I guess the same checks as per you previous reply have to be done for them as well?

      ——————————————————————————————————————
      “Failed to connect to data source ‘Computer1\Test’:”

      “The server principal “Domain1\Computer1$” is not able to access the database “Database1″ under the current security context. Instance: PRODUCTION”

      ——————————————————————————————————————————-
      MSSQL 2016: Monitoring failed
      —————————————–
      Event ID: 4211. Management Group: NLSCOM_MG01. Script: ModuleAction Module: GetHKPoolMemoryConsumption.ps1 Version: 7.0.7.0 : Error occurred during Request count data source executing.
      Computer:Computer1
      Reason: Cannot connect to the target Sql Server instance.
      Connection log:
      Failed to connect to data source ‘Computer1\Test’: Exception calling “Open” with “0” argument(s): “Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was – [Pre-Login] initialization=3; handshake=8407;

      ———————————————————————————————————————————————-

      Event ID: 4211. Management Group: NLSCOM_MG01. Script: GetSQL2016DBFreeSpace.vbs. Version: 7.0.7.0 : The next errors occurred:
      Cannot connect to database ‘Database1’
      Error Number: -2147467259
      Description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal “Domain1\Computer1$” is not able to access the database “Database1” under the current security context.
      Instance: PRODUCTION

  15. C.Christou

    Hello Kevin,

    Did you see that error before?I have tried to create the Service Login but i got that error:

    Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Unable to complete login process due to delay in opening server connection.

  16. VANDANA JAGGUNAIDU

    Can we use this solution to work group servers which are not in domain? Why because when you run the task ‘Create Healthservice login as SysAdmin’ we need to pass the domain as well along with userid/password. So my question is what needs to be passed for the Domain option for work group servers?

  17. CyrAz

    Just in case someone else faces the same issue :
    I had sent instructions about this to one of my custommer quite a while ago, and they only made the move to implement it last week.
    They used the SQL script at the end of the article, as I instructed them; but were facing issues with Mirroring.WitnessDiscovery (]The server principal “NT AUTHORITY\SYSTEM” is not able to access the database “XXXX” under the current security context.)

    The reason being they still use the old(er) version of the SQL MP and of your Run As MP, which require different permissions (that were properly listed on this page before)…

    Just my opinion, but maybe you should have posted a new article instead of updating this one 😉

    CyrAz

    • Kevin Holman

      I thought about that – the problem is – we no longer support the old MP’s, and customers need to transition to the newer MP for SQL 2012 and later. I know this cannot be overnight – but I didn’t want to post a new article and have two floating around because that confuses customers new to SCOM…. and eventually wanted to retire the old article – but so many external references already point to that URL. So I made the decision intentionally to be a little disruptive, but I feel its the right thing to do. The way I see it – if customers were using the old MP and RunAs config – they already have it. If they are doing something new, they should just invest in the new MP. No?

      • CyrAz

        Well in this specific case, they are running the old SQL MP and are absolutely not ready to upgrade to the new one; and they have been prepearing to use the Health service SID for quite some time now but only decided to go for it last week. They had already downloaded the MP though…
        That’s not really a big deal, you just need to realize what’s wrong and then the proper SQL script can be found right inside the MP itself 🙂

  18. Jurie du Plooy

    HI Kevin

    Thank you for the Blog, the stuff is great

    Just 1 thing if you can possibly help out.

    On SQL 2016 with a newly build SCOM 2019 environment, I have deployed the new SQL Agnostic MP and the SQL addendum pack, all clean build not a scom upgrade, no SQL Runas accounts configured seeing its brand new environment.

    Applied the permissions as per the 1st script on this blog.
    Monitoring on all DB’s are working fine, except the model DB, as other people have stated before in the thread.

    still the warnings are being generated for the model DB:
    Database Status – model = Warning state: Database recovering/Restoring
    1. “The server principal “Domain\SQLServer$” is not able to access the database “model” under the current security context.

    Securables Conifugration Status – Warning
    2. NoAccessSecurablesList

    Server-level permissions: • VIEW ANY DEFINITION
    Views: • msdb.dbo.sysjobschedules • msdb.dbo.log_shipping_primary_databases • msdb.dbo.log_shipping_secondary_databases 3. Stored procedures: • sys.xp_readerrorlog • msdb.dbo.sp_help_jobactivity • msdb.dbo.sp_help_job

    I went through the setup as per instructions you gave in the blog, but these keep popping up.

    In the SCRIPT is only SELECT required to populate the rights on the msdb DB for the Views as stated in the script?

    SQL server build is Version 13.0.5264 Standalone.

    Any Tips?

    Much Appreciated

    Thank you

  19. Scott Brown

    It seems like there may be an error in the script somewhere? After it has been run, I get the error:

    Error occurred during CPU Usage for SQL Instances data source executing. Computer: Reason: Exception calling “Fill” with “1” argument(s): “VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’. The user does not have permission to perform this action.” Position:810 Offset:3 Instance:MSSQLSERVER

    I had to go in and manually add View server state to the role

    • Kevin Holman

      Scott – were you using the Management Pack – or the script posted recently?

      Do you have a server role “SCOM_HealthService” ?

      In the script this is executed: GRANT VIEW SERVER STATE TO [SCOM_HealthService];

      What version of SQL server, and version of MP?

Leave a Reply

Your email address will not be published. Required fields are marked *