Download: https://github.com/thekevinholman/SQLRunAsAddendum
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
Now – I want to show a BETTER approach to configuring monitoring for the SQL MP, which will make life a lot simpler for SCOM admins, and SQL teams, and increase security.
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:
Service SID’s were introduced in Windows Server 2008 and later.
We can do the same thing for the SCOM HealthService.
Essentially – this can be accomplished in two steps:
- Enable the HealthService to be able to use a service SID.
- 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
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:
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 ENABLED by default. It will automatically configure this as soon as SQL is detected, and will restart the agent. This will automatically fix agents that get upgraded or re-installed as well.
Additionally – I wrote a task you can run manually if needed. 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. You only ever need to run this specific task if the Seed class is in a warning state.
Here is what it looks like in action:
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 is the best practice and SQL scripts are documented at the end of this article.
If you run this task, and don’t have rights to the SQL server – you will get this:
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:
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!
***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 the login on the SQL server, and then sets it to have the rights necessary for the SQL MP to work.
All of these activities are logged for audit in the Task Status view:
The current SQL MP as of this article update (7.0.42.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:
There is also a monitor targeting the database for securables.
If you want to query your entire management group for missing permissions to help out your DBA’s, here is a simple SQL query:
--To identify SQL engines and databases with misconfigured security: SELECT bme.DisplayName, s.HealthState, bme.FullName, s.MonitorId, s.LastModified FROM BaseManagedEntity bme JOIN State s ON s.BaseManagedEntityId = bme.BaseManagedEntityId WHERE s.MonitorId IN (SELECT MonitorId FROM Monitor WHERE MonitorName = 'Microsoft.SQLServer.Windows.DBEngine.Configuration.SecurablesConfigMonitor' OR MonitorName = 'Microsoft.SQLServer.Windows.Monitor.Database.SecurablesDbConfig') AND bme.IsDeleted = 0 AND s.HealthState = 2 ORDER BY s.MonitorId DESC
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 available below:
https://github.com/thekevinholman/SQLRunAsAddendum
Here are the actual SQL scripts if you prefer to run these manually or have automation already in place:
/* SQL2012 and later * Updated 1-10-23 * BEGIN QUERY */ USE [master]; SET NOCOUNT ON; /*The user account that System Center Operations Manager will use to access the SQL Server instance*/ DECLARE @accountname sysname = 'NT SERVICE\HealthService'; /* In some cases, administrators change the 'sa' account default name. * This will retrieve the name of the account associated to princicpal_id = 1 */ DECLARE @sa_name sysname = 'sa'; SELECT @sa_name = [Name] FROM sys.server_principals WHERE principal_id = 1 /* Create the server role with authorization to the account associated to principal id = 1 * only if it does not already exist */ DECLARE @createSrvRoleCommand nvarchar(200); SET @createSrvRoleCommand = 'IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE [Name] = ''SCOM_HealthService'') BEGIN CREATE SERVER ROLE [SCOM_HealthService] AUTHORIZATION [' + @sa_name + ']; END' EXEC(@createSrvRoleCommand); DECLARE @createLoginCommand nvarchar(200); SET @createLoginCommand = 'IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE [Name] = '''+ @accountname +''') BEGIN CREATE LOGIN '+ QUOTENAME(@accountname) +' FROM WINDOWS WITH DEFAULT_DATABASE=[master]; END' EXEC(@createLoginCommand); /* Add the login to the user defined server role */ DECLARE @addServerMemberCommand nvarchar(200); SET @addServerMemberCommand = 'ALTER SERVER ROLE [SCOM_HealthService] ADD MEMBER ' + QUOTENAME(@accountname) + ';' EXEC(@addServerMemberCommand); /* Add the login and database role to each database */ DECLARE @createDatabaseUserAndRole nvarchar(max); SET @createDatabaseUserAndRole = ''; SELECT @createDatabaseUserAndRole = @createDatabaseUserAndRole + ' USE ' + QUOTENAME(db.name) + '; IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [Name] = '''+ @accountname +''') BEGIN CREATE USER ' + QUOTENAME(@accountname) + ' FOR LOGIN ' + QUOTENAME(@accountname) + '; END; IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [Name] = ''SCOM_HealthService'') BEGIN CREATE ROLE [SCOM_HealthService] AUTHORIZATION [dbo]; END; 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); /* Grant server and database specific permissions to database role */ /* Server-level permissions */ USE [master]; GRANT VIEW ANY DATABASE TO [SCOM_HealthService]; GRANT VIEW ANY DEFINITION TO [SCOM_HealthService]; GRANT VIEW SERVER STATE TO [SCOM_HealthService]; /* master db object permissions */ GRANT EXECUTE ON sys.xp_readerrorlog TO [SCOM_HealthService]; GRANT EXECUTE ON sys.xp_instance_regread TO [SCOM_HealthService]; GRANT SELECT ON sys.database_mirroring_witnesses TO [SCOM_HealthService]; /* Required only for SCOMDB tasks */ /* GRANT ALTER ANY DATABASE TO [SCOM_HealthService]; */ /* msdb database permissions */ USE [msdb]; GRANT SELECT ON [dbo].[sysjobschedules] TO [SCOM_HealthService]; GRANT SELECT ON [dbo].[sysschedules] TO [SCOM_HealthService]; GRANT SELECT ON [dbo].[syscategories] TO [SCOM_HealthService]; GRANT SELECT ON [dbo].[sysjobs_view] TO [SCOM_HealthService]; GRANT SELECT ON [dbo].[sysjobactivity] TO [SCOM_HealthService]; GRANT SELECT ON [dbo].[sysjobhistory] TO [SCOM_HealthService]; GRANT SELECT ON [dbo].[syssessions] 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].[agent_datetime] TO [SCOM_HealthService]; GRANT EXECUTE ON [dbo].[SQLAGENT_SUSER_SNAME] TO [SCOM_HealthService]; ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [SCOM_HealthService]; /* END 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.
- Make sure the DBA did not “harden” the default SQL server rights configuration, such as remove default rights from the “public” user role.
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. Also inspect to ensure the error is not “Invalid Class” in the text of the 4221 event or alert, because that means WMI is corrupt or the WMI classes are missing. For instructions on repairing missing SQL WMI classes or namespace: Error occurs when you open Configuration Manager – SQL Server | Microsoft Docs
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
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
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.
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
This was a bug in the SQL 2012+ 7.0.9.0 MP. 7.0.13.0 fixed it.
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
Hi Kevin
thank you for your assistance.
After the creation of the SQL login I received the below WMI Health State alert the servers I worked.
Please advise.
https://drive.google.com/file/d/1XTdDY2lJMNcBfyRsnsEimDMHYw236CKC/view?usp=sharing
From the looks of that, I’d say WMI is not ok. 🙂
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!
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”.
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 🙂
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?
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.
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?
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
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!
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.
Hi Kevin,
Yes…I am also facing the same issue with only one of our Production server. Server is discovered under SQL Seed class but not visible under SQL DB Engine on windows. Its been more than 24 hours.
Only one server is having this issue other servers are discovered and executed Login as Low Priv Tasks.
Please help here. Thanks,
Bala
Kevin Please have a look. #1 the Seed shows green and the login should create automatically but in fact only works sometimes. Some SQL instances do, some dont. #2 some show up in DBEngine to run the low Priv task, only AFTER manually running the query and checking permissions on the instances/DBs. Mix of SQL 2016, 2019, 2022. Not sure what is missing. We have been through troubleshooting steps above on each server. Doing compare with working/non etc and not getting far.
#1. Logins are never created automatically. Someone with rights must create the logins but running the low priv task.
#2. If SQL DB engines are not discovered by default, this is because of non-standard SQL configuration, or improper agent action account. This design assumes the agent action account is ALWAYS “Local System”. This should be set this way for all agents. Local System has enough rights to discover the SQL DB engine by default on all systems. If it does not, it is because of something your company does to customize Windows Server or SQL permissions. To work around that, you will need to run the Low Priv script first, to allow the Service SID enough rights to discover the SQL DB engine.
Kevin,
Is there any way we can monitor the SQL SSRS servers without configuring the SSRS RunAs Accounts/Profiles?
I would also like to know about it
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.
SSRS MP Guide says:
“It is not recommended to use Local System account or HealthService SSID as its special case to
monitor SSRS, as some workflows run from the server hosting an SSRS instance and try to reach the
SSRS Database usually installed on another server. You will need to provide the computer accounts of
all servers hosting SSRS instances with the required permissions to access the SSRS Database. A
domain account is a more preferable option.”
just updated to 2019 and all SQL monitoring stoped working.
you have to re-execute the sidstate* Task. and all works again.
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
The SQL 2016 MP *was* supported on SCOM 2012R2. However, we have dropped support for the “old” SQL 2008-2012, 2014, and 2016 MP’s.
We have shipped the SQL Version agnostic MP which monitors SQL 2012 – SQL 2019 currently in a single management pack:
https://techcommunity.microsoft.com/t5/SQL-Server/Released-Version-agnostic-Management-Pack-for-SQL-Server-7-0-15/ba-p/479573
I am planning an article discussing how to transition from the old ones to the new one.
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!
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
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.
Hi Kevin,
Don’t suppose you could point me in the direction of the logs in question?
Operation Manager is the Event Log
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
The sql2017 one works right now. However, I am lunching a new one specifically for the agnostic version out soon.
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:
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.
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?
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.
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
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
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.
I have not seen that.
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?
Yes – you can. For a workgroup machine – you should be able to input COMPUTERNAME\accountname
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
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?
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 🙂
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
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
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?
Hi Kevin,
we require the below information.
we are using Run As addendum MP in order to monitor the SQL servers.
Currently we are planning to import the below MP’s in SCOM 2012 R2.
SQL Server 2012, 2014, 2016 & 2017 Analysis Services MP
SQL Server 2012 2014, 2016 & 2017 Reporting Services (Native Mode) MP
kindly let us know if Run As Addendum MP(ver: 7.0.0.0) will support the above mentioned MP’s.
I have not done any testing with Analysis services MP.
Please let us know if you have any plan to test this MP. So that we can import it once we get confirmation from you.
Hi Kevin,
Need your assistance on the below.
We have SQL MP version 7.0.15.0 and Run As Addendum MP version 7.0.0.0
We deployed the SCOM Agent which has SQL 2016. As per the DBA team confirmation the server has only SQL 2016 DB however we see the server in both SQL 2016 and 2017 Seed view.
kindly let us know why the server showing in SQL 2017 seed views since there is no SQL 2017 component installed on the server.
You have the wrong run as account addendum MP. You also likely have duplicate MPs for SQL version agnostic, and the old legacy MPs running at the same time. See my recent article on transitioning to the SQL Version Agnostic MP and the new addendum MP
Hi
We are trying to create the healthservicelogin but it fails because we are not using default port 1433. is there anyway to edit port number when creating the login?
The login fails on non-default ports, because in the new Version Agnostic MP the port is no longer discovered and placed into the “connect string” value like it used to be. I could potentially re-write the powershell script to try and “discover” the port, but we asked the PG to put this port discovery back into the MP. If you wish to fix this yourself, you only need to edit the powershell script, and add logic to discover the port number before connecting to SQL.
Does this work with SQL Express as well
I’ll be honest – I don’t know. Normally my customers exclude SQL express from all monitoring – a lot of our built in workflows fail on express editions and cause noise.
Hi Kevin,
After enabling the HealthService SID and creating the HealthService Login as Low Priv via the script provided with the MP, we still see the Monitor HealthService Connect to SQL Monitor in warning state with the error:
Data Source could not be initialized
[DBNETLIB][ConnectionOpen (SECCreateCredentials()).]SSL Security error.
Could you assist on this issue.
Hi Kevin,
Does this also work with the new SQL Version Agnostic MP?
br
Karl
Yes. I keep updating the versions and keep it up to date with the latest MP and feedback from customers.
The 2012+ script is missing GRANT SELECT on sys.database_mirroring_witnesses to [SCOM_HealthService];
This causes mirroring witness discovery to fail. Just an FYI. I don’t know about the 2008 one, as we don’t use it, and I’m not sure if the security principal is the same.
What 2012+ MP’s are you using?
This is now fixed with 7.0.15.2 version of the addendum. This added the select on database mirroring, but that’s only applicable if you are running the older, now deprecated SQL MP’s. The SQL version agnostic MP’s don’t use that right, as SQL mirroring is no longer monitored.
Hi Kevin,
Can we use Run As addendum Management Pack with SQL Analysis and Reporting Management Pack?
Thanks,
Sreejeet
I don’t know on analysis, but I know reporting wont work. This is because SSRS can have a seperate front end from the back end, and the MP we have for reporting wont work with Service SID’s without some work on each back end DB Engine system, granting rights to the computer account of the SSRS server. I’m not a huge fan of our SSRS MP because of this, so I wrote a much simpler one that does the discovery and just monitors the SSRS service.
Hi Kevin,
We are receving the following alert on few of the SQL servers regularly.
We have enabled HealthService SID and ran the script to create Health Service Login on the SQL servers with Low Priv… rights. Could you please advice to fix this issue.
Also, how can we run the WMI query using HealthService Login on the SQL Server with the below alert, to check if indeed the HealthService Login has the proper rights to run the WMI queries.
Event ID: 4001. Management Group: XXXX. Script: DiscoverSQL2008DBEngineDiscovery.vbs. Version: 7.0.15.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.
Thanks in advance!
Hey Kevin, you are awesome!
I have a problem after implementing the HealthService runas account solution:
When I create monitors for SQL and try to use MP Simulator in Visual Studio, it does not work. I cannot connect to SQL.
How do I get MP Simulator to use NT Service\Healthservice?
Thanks
David Sjölund
Hi Kevin,
Nice article!
However, I would like to point out some drawbacks in your SQL query for low priv assignment.
First, query does not check if login/user/role exists and when executed second time throw errors.
Second, for some reason it creates a user and a role in each database which is not needed. The same can be achieved by granting CONNECT ANY DATABASE permission on server level.
Below is my version of the query where these issues addressed.
–(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
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ‘SCOM_HealthService’)
BEGIN
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];
IF CAST(SERVERPROPERTY(‘ProductMajorVersion’) AS int) >= 12 — For SQL 2014 and higher
EXEC (‘GRANT CONNECT ANY DATABASE TO [SCOM_HealthService];’); — Wrap in EXEC to prevent SQL 2012 to throw an error on compilation
END
DECLARE @createLoginCommand nvarchar(200);
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = @accountname)
BEGIN
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’
END
DECLARE @createDatabaseUserAndRole nvarchar(max);
— Add user to each database (only for SQL 2012 as it does not have CONNECT ANY DATABASE permission)
IF CAST(SERVERPROPERTY(‘ProductMajorVersion’) AS int) = 11 — SQL 2012
BEGIN
SET @createDatabaseUserAndRole = ”;
SELECT @createDatabaseUserAndRole = @createDatabaseUserAndRole + ‘USE ‘ + QUOTENAME(db.name) + ‘; CREATE USER ‘ + QUOTENAME(@accountname) + ‘ FOR LOGIN ‘ + 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 > 4 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);
END
— Add database specific permissions to database role
USE [master];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name] = @accountname)
BEGIN
SET @createDatabaseUserAndRole = ‘USE [master]; CREATE USER ‘ + QUOTENAME(@accountname) + ‘ FOR LOGIN ‘ + QUOTENAME(@accountname) + ‘;
CREATE ROLE [SCOM_HealthService]; EXEC sp_addrolemember @rolename = ”SCOM_HealthService”, @membername = ‘+ QUOTENAME(@accountname) + ‘;’;
EXEC (@createDatabaseUserAndRole);
END
GRANT EXECUTE ON sys.xp_readerrorlog TO [SCOM_HealthService]
GRANT SELECT ON sys.database_mirroring_witnesses TO [SCOM_HealthService];
USE [msdb];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name] = @accountname)
BEGIN
SET @createDatabaseUserAndRole = ‘USE [msdb]; CREATE USER ‘ + QUOTENAME(@accountname) + ‘ FOR LOGIN ‘ + QUOTENAME(@accountname) + ‘;
CREATE ROLE [SCOM_HealthService]; EXEC sp_addrolemember @rolename = ”SCOM_HealthService”, @membername = ‘+ QUOTENAME(@accountname) + ‘;’;
EXEC(@createDatabaseUserAndRole);
END
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’;
USE [model]
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name] = @accountname)
BEGIN
SET @createDatabaseUserAndRole = ‘USE [model]; CREATE USER ‘ + QUOTENAME(@accountname) + ‘ FOR LOGIN ‘ + QUOTENAME(@accountname) + ‘;’;
EXEC(@createDatabaseUserAndRole);
END
–End of Query
Hi,
I also made corrections and adding only when permission don’t exists already. I created SQL agent job for this to daily apply this if someone have changed permissions or new databases added. If this SQL job fails then I will get alert on SCOM. So commands are these:
–Begin Query
USE [master];
SET NOCOUNT ON;
DECLARE @accountname sysname = ‘NT SERVICE\HealthService’;
—-Server level part
— Create the server role
IF NOT EXISTS (SELECT [name] FROM sys.server_principals where name=’SCOM_HealthService’ and Type = ‘R’) BEGIN USE [master]; CREATE SERVER ROLE [SCOM_HealthService] END
— Create login
IF NOT EXISTS (SELECT [name] FROM sys.syslogins WHERE [name] = ‘NT SERVICE\HealthService’) BEGIN USE [master]; Create Login [NT SERVICE\HealthService] From Windows WITH DEFAULT_DATABASE=[master] END
— Grant instance level permissions to server role
IF NOT EXISTS (SELECT role.name AS RoleName, member.name AS MemberName FROM sys.server_role_members JOIN sys.server_principals AS role ON sys.server_role_members.role_principal_id = role.principal_id JOIN sys.server_principals AS member ON sys.server_role_members.member_principal_id = member.principal_id WHERE member.name = ‘NT SERVICE\HealthService’ AND role.name = ‘SCOM_HealthService’) BEGIN USE [master]; Exec sp_addsrvrolemember @rolename = ‘SCOM_HealthService’, @loginame = ‘NT SERVICE\HealthService’; END
–GRANT VIEW ANY DATABASE TO [SCOM_HealthService];
IF NOT EXISTS (select P.name, SP.permission_name, SP.state_desc From master.sys.server_principals P Inner Join master.sys.server_permissions SP On SP.grantee_principal_id = P.principal_id Where P.name = ‘SCOM_HealthService’ AND SP.permission_name = ‘VIEW ANY DATABASE’ AND SP.state_desc = ‘GRANT’) BEGIN USE [master]; GRANT VIEW ANY DATABASE To [SCOM_HealthService]; END
–GRANT ALTER ANY DATABASE TO [SCOM_HealthService]; –Required only for SCOMDB tasks
–IF NOT EXISTS (select P.name, SP.permission_name, SP.state_desc From master.sys.server_principals P Inner Join master.sys.server_permissions SP On SP.grantee_principal_id = P.principal_id Where P.name = ‘SCOM_HealthService’ AND SP.permission_name = ‘ALTER ANY DATABASE’ AND SP.state_desc = ‘GRANT’) BEGIN USE [master]; GRANT ALTER ANY DATABASE To [SCOM_HealthService]; END
–GRANT VIEW ANY DEFINITION TO [SCOM_HealthService];
IF NOT EXISTS (select P.name, SP.permission_name, SP.state_desc From master.sys.server_principals P Inner Join master.sys.server_permissions SP On SP.grantee_principal_id = P.principal_id Where P.name = ‘SCOM_HealthService’ AND SP.permission_name = ‘VIEW ANY DEFINITION’ AND SP.state_desc = ‘GRANT’) BEGIN USE [master]; GRANT VIEW ANY DEFINITION To [SCOM_HealthService]; END
–GRANT VIEW SERVER STATE TO [SCOM_HealthService];
IF NOT EXISTS (select P.name, SP.permission_name, SP.state_desc From master.sys.server_principals P Inner Join master.sys.server_permissions SP On SP.grantee_principal_id = P.principal_id Where P.name = ‘SCOM_HealthService’ AND SP.permission_name = ‘VIEW SERVER STATE’ AND SP.state_desc = ‘GRANT’) BEGIN USE [master]; GRANT VIEW SERVER STATE To [SCOM_HealthService]; END
—- Database level part
–Create database user
DECLARE @createDatabaseUser nvarchar(max) SET @createDatabaseUser = ”;
SELECT @createDatabaseUser = @createDatabaseUser + ‘ USE ‘ + QUOTENAME(db.name) + ‘; IF NOT EXISTS (SELECT [name] FROM sys.database_principals where name=”NT SERVICE\HealthService” and Type = ”U”) BEGIN CREATE USER ‘ + QUOTENAME(@accountname) + ‘ FOR LOGIN ‘ + QUOTENAME(@accountname) + ‘; END ‘
— ‘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(@createDatabaseUser)
–Create database role
DECLARE @createDatabaseRole nvarchar(max) SET @createDatabaseRole = ”;
SELECT @createDatabaseRole = @createDatabaseRole + ‘IF NOT EXISTS (SELECT [name] FROM sys.database_principals where name=”SCOM_HealthService” and Type = ”R”) BEGIN CREATE ROLE [SCOM_HealthService]; EXEC sp_addrolemember @rolename = ”SCOM_HealthService”, @membername = ‘+ QUOTENAME(@accountname) + ‘; END ‘
— ‘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(@createDatabaseRole)
— Add database specific permissions to database role
USE [master];
IF NOT EXISTS (SELECT OBJECT_NAME(major_id) AS SPname, USER_NAME(grantee_principal_id) AS PrincName, permission_name FROM sys.database_permissions p WHERE OBJECT_NAME(major_id) = ‘xp_readerrorlog’ AND USER_NAME(grantee_principal_id) = ‘SCOM_HealthService’ AND permission_name = ‘EXECUTE’) BEGIN GRANT EXECUTE ON sys.xp_readerrorlog TO [SCOM_HealthService]; END
IF NOT EXISTS (SELECT OBJECT_NAME(major_id) AS SPname, USER_NAME(grantee_principal_id) AS PrincName, permission_name FROM sys.database_permissions p WHERE OBJECT_NAME(major_id) = ‘database_mirroring_witnesses’ AND USER_NAME(grantee_principal_id) = ‘SCOM_HealthService’ AND permission_name = ‘SELECT’) BEGIN GRANT SELECT ON sys.database_mirroring_witnesses TO [SCOM_HealthService]; END
USE [msdb]; IF NOT EXISTS (SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, ‘No members’) AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = ‘R’ AND DP1.name = ‘SQLAgentReaderRole’ AND DP2.name = ‘SCOM_HealthService’)
BEGIN USE [msdb]; EXEC sp_addrolemember @rolename=’SQLAgentReaderRole’, @membername=’SCOM_HealthService’; END
USE [msdb]; IF NOT EXISTS (SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, ‘No members’) AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = ‘R’ AND DP1.name = ‘PolicyAdministratorRole’ AND DP2.name = ‘SCOM_HealthService’)
BEGIN USE [msdb]; EXEC sp_addrolemember @rolename=’PolicyAdministratorRole’, @membername=’SCOM_HealthService’; END
USE [msdb]; IF NOT EXISTS (select * FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE USER_NAME(usr.principal_id) = ‘SCOM_HealthService’ AND perm.permission_name = ‘SELECT’ AND perm.state_desc = ‘GRANT’ AND obj.name = ‘log_shipping_primary_databases’ AND SCHEMA_NAME(obj.schema_id) = ‘dbo’) BEGIN USE [msdb]; GRANT SELECT ON [dbo].[log_shipping_primary_databases] TO [SCOM_HealthService]; END
USE [msdb]; IF NOT EXISTS (select * FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE USER_NAME(usr.principal_id) = ‘SCOM_HealthService’ AND perm.permission_name = ‘SELECT’ AND perm.state_desc = ‘GRANT’ AND obj.name = ‘log_shipping_monitor_primary’ AND SCHEMA_NAME(obj.schema_id) = ‘dbo’) BEGIN USE [msdb]; GRANT SELECT ON [dbo].[log_shipping_monitor_primary] TO [SCOM_HealthService]; END
USE [msdb]; IF NOT EXISTS (select * FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE USER_NAME(usr.principal_id) = ‘SCOM_HealthService’ AND perm.permission_name = ‘SELECT’ AND perm.state_desc = ‘GRANT’ AND obj.name = ‘log_shipping_monitor_history_detail’ AND SCHEMA_NAME(obj.schema_id) = ‘dbo’) BEGIN USE [msdb]; GRANT SELECT ON [dbo].[log_shipping_monitor_history_detail] TO [SCOM_HealthService]; END
USE [msdb]; IF NOT EXISTS (select * FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE USER_NAME(usr.principal_id) = ‘SCOM_HealthService’ AND perm.permission_name = ‘SELECT’ AND perm.state_desc = ‘GRANT’ AND obj.name = ‘log_shipping_secondary_databases’ AND SCHEMA_NAME(obj.schema_id) = ‘dbo’) BEGIN USE [msdb]; GRANT SELECT ON [dbo].[log_shipping_secondary_databases] TO [SCOM_HealthService]; END
USE [msdb]; IF NOT EXISTS (select * FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE USER_NAME(usr.principal_id) = ‘SCOM_HealthService’ AND perm.permission_name = ‘SELECT’ AND perm.state_desc = ‘GRANT’ AND obj.name = ‘log_shipping_monitor_secondary’ AND SCHEMA_NAME(obj.schema_id) = ‘dbo’) BEGIN USE [msdb]; GRANT SELECT ON [dbo].[log_shipping_monitor_secondary] TO [SCOM_HealthService]; END
USE [msdb]; IF NOT EXISTS (select * FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE USER_NAME(usr.principal_id) = ‘SCOM_HealthService’ AND perm.permission_name = ‘EXECUTE’ AND perm.state_desc = ‘GRANT’ AND obj.name = ‘sp_help_job’ AND SCHEMA_NAME(obj.schema_id) = ‘dbo’) BEGIN USE [msdb]; GRANT EXECUTE ON [dbo].[sp_help_job] TO [SCOM_HealthService]; END
USE [msdb]; IF NOT EXISTS (select * FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE USER_NAME(usr.principal_id) = ‘SCOM_HealthService’ AND perm.permission_name = ‘SELECT’ AND perm.state_desc = ‘GRANT’ AND obj.name = ‘sysjobs_view’ AND SCHEMA_NAME(obj.schema_id) = ‘dbo’) BEGIN USE [msdb]; GRANT SELECT ON [dbo].[sysjobs_view] TO [SCOM_HealthService]; END
USE [msdb]; IF NOT EXISTS (select * FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE USER_NAME(usr.principal_id) = ‘SCOM_HealthService’ AND perm.permission_name = ‘EXECUTE’ AND perm.state_desc = ‘GRANT’ AND obj.name = ‘sp_help_jobactivity’ AND SCHEMA_NAME(obj.schema_id) = ‘dbo’) BEGIN USE [msdb]; GRANT EXECUTE ON [dbo].[sp_help_jobactivity] TO [SCOM_HealthService]; END
USE [msdb]; IF NOT EXISTS (select * FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE USER_NAME(usr.principal_id) = ‘SCOM_HealthService’ AND perm.permission_name = ‘SELECT’ AND perm.state_desc = ‘GRANT’ AND obj.name = ‘sysschedules’ AND SCHEMA_NAME(obj.schema_id) = ‘dbo’) BEGIN USE [msdb]; GRANT SELECT ON [dbo].[sysschedules] TO [SCOM_HealthService]; END
USE [msdb]; IF NOT EXISTS (select * FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE USER_NAME(usr.principal_id) = ‘SCOM_HealthService’ AND perm.permission_name = ‘SELECT’ AND perm.state_desc = ‘GRANT’ AND obj.name = ‘sysjobschedules’ AND SCHEMA_NAME(obj.schema_id) = ‘dbo’) BEGIN USE [msdb]; GRANT SELECT ON [dbo].[sysjobschedules] TO [SCOM_HealthService]; END
–End of Query
Hi Kevin,
Under the Troubleshooting Section of this article, it is mentioned “The proper rights must be granted to NT SERVICE\HealthService at the instance level. (low priv script or sysadmin)”. In our SQL Server environment we ran the low priv script as we do not want Health Service to have sysadmin rights.
Now we are getting SQL Discovery and Monitoring failed alerts with Alert description “The server principal “NT AUTHORITY\SYSTEM” is not able to access the database “XXXX”.
We have performed all the steps exactly as mentioned in this article.
One Doubt: What rights must be granted to NT SERVICE\HealthService at the instance level? Are these the rights which are mentioned under Security Considerations > Low-Privilege Environments section of SQL Server Management Pack guide? If yes, then we cn check this in our SQL Servers.
Thanks,
Kumar
Hi Kevin,
As a national governmental institution we are eagerly making use of your SQL Run As solution in our SCOM environment.
When using the “create login as low priv” script from the SCOM console it fails to execute the creation of the SCOM_HealthService role and NT SERVICE\HealthService account. We are getting the following return (Output 1).
Notes: Logon as a service is enabled for the User Account.
The SQL ports (1433 – 1434) are open to the SQL instances running on the server and the SCOM port (5723) is able to communicate with the Gateway Server.
The user logs on succesfully and is able to execute the powershell script locally on the server.
When we excecute the script locally on the same server with the same account, the script runs succesfully (Output 2).
I don’t want to appear as someone who expects support on an independantly made, freely available Management Pack.
Therefore I would like to ask you and the community if you are experiencing the same state of play, or have an idea what could be causing this difference.
Kind Regards,
Niels
Output 1
////////////////////////////////////////////////////////////////////
Script is starting.
Running as (Contoso\SomeUser).
Attempting to Create SQL Login and assign permissions for Healthservice.
ERROR returned from SQL server (SomeServer.Contoso\INST01).
Terminating script.
Error is: (Exception calling “ExecuteNonQuery” with “0” argument(s): “ExecuteNonQuery requires an open and available Connection. The connection’s current state is closed.” Exception calling “Open” with “0” argument(s): “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol)”).
Exception calling “Open” with “0” argument(s): “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol)”
At line:89 char:1
+ $SQLConnection.Open()
+ ~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
Exception calling “ExecuteNonQuery” with “0” argument(s): “ExecuteNonQuery requires an open and available Connection. The connection’s current state is closed
.”
At line:93 char:1
+ $SQLCmd.ExecuteNonQuery() | Out-Null
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException
////////////////////////////////////////////////////////////////////
Output 2
////////////////////////////////////////////////////////////////////
Script is starting.
Running as (Contoso\SomeUser).
Attempting to Create SQL Login and assign permissions for Healthservice.
ERROR returned from SQL server (SomeServer.Contoso\INST01).
Terminating script.
Error is: (Exception calling “ExecuteNonQuery” with “0” argument(s): “The server principal ‘SCOM_HealthService’ already exists.
The server principal ‘NT SERVICE\HealthService’ already exists.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
Changed database context to ‘master’.
Changed database context to ‘master’.
Changed database context to ‘msdb’.”).
Exception calling “ExecuteNonQuery” with “0” argument(s): “The server principal
‘SCOM_HealthService’ already exists.
The server principal ‘NT SERVICE\HealthService’ already exists.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
Changed database context to ‘master’.
Changed database context to ‘master’.
Changed database context to ‘msdb’.”
At line:93 char:1
+ $SQLCmd.ExecuteNonQuery() | Out-Null
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
////////////////////////////////////////////////////////////////////
Shared Memory, Named Pipes and TCP/IP Protocols are all enabled in the SQL Server Network Configuration.
Shared Memory, Named Pipes and TCP/IP Protocols are all enabled in the SQL Server Network Configuration.
Can we use this approach in existing Run As Account configured environment so that new servers getting added into Management Group can be monitoring for databases automatically ?
It appears I’ve encountered an example that doesn’t want to be monitored with this approach. After all steps have been executed succesfully and the mma has been restarted. The alert some securables are inaccessible remains.
The permissions in SQL look identical compared to all the other succesfully configured ones. I’m puzzled.
Did you double check ALL of these:
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.
Yes, that is what makes this so confusing. Ran both SID and low priv scripts again, restarting the agent, flushing the healthcache, checked in registry manually, compared sql authorizations and permissions with working examples. I just can’t find a difference. And it’s stand alone (not a cluster or anything).
Every time this has ever come up, it was something the person missed. Every, single, time. Is NT AUTHORITY\SYSTEM present as a login? Is it set to disabled in the status? What happens if you set this to Sysadmin temporarily?
I didn’t forget anything. I executed the same tasks in the same way over 20 times. And just this one instance does not work.
I even deleted all of the added login, user accounts and roles manually and ran the scripts again, same thing. It’s just stuck on the same unanswered error in de msdb database earlier posted.
When setting it to sysadmin it works. Obviously removing it, it stops working again. Comparing the securables to a working instance of the same edition same sp and ru level -> identical.
@Martijn,
I had exactly the same Problem and the Answer was so simple:
We forgot to restart the Service (Microsoft Monitor)
Hi Kevin,
2016 DBs in Seed Class View are displayed as “not monitored” although every granting and enabling step had been done successfully before, also DB Engines and Instances being monitored – what could be the reason?
Thanks in advance,
Guido
DB’s won’t be in a seed class, do you mean the seed class as shown in my RunAs addendum views? Some of these will show not monitored by design, if they are clusters or alwaysOn instances of computers.
Hi Kevin,
Few SQL Cluster instances are displayed as “not monitored” under SQL seed class.
Display Name MSSQL on Windows: Discover Installation Source
So as you said as per design cluster instances will show as not monitored?
When i run – Enable HealthService SID and restart agent task the task status is showing as success. Please confirm.
Thanks,
Bala
Yes – cluster virtual computers will show in seed class as not monitored by design.
There is no reason to run the task – that is simply running on whatever node they are currently hosted by. The node is also a seed instance and is monitored.
I recommend enabling the recovery, so that you never have to run the task anyway – it will just enable SID’s whenever a SQL seed is discovered not to have it configured correctly.
When an agent gets an upgrade, fe. from 1807 to 2019 the enabled healthservice sid setting gets lost, resulting in discovery alerts.
I’d like to request a ‘nice to have’ new feature if that’s possible that if the agent was configured to use sid, it will be enabled automatically again when the health state in de sql seed class gets unhealthy.
I have asked the PG to just make SID enabled by default on ALL agents. But until a customer raises a case for this, it wont get a lot of traction.
Since my MP monitors for this, and includes a recovery to set it back, making this automated is REALLY easy.
I have run this on on a couple of SQL servers and I am still getting discovery errors. Am I supposed to add the SID as a run as account some how? What setup is needed in SCOM for this?
Hi Kevin, what if I see the server name in SQL Seed Class, but not in SQL DB Engine on Windows ? I have some server that, even being SQL Servers, don’t show up at all since I started to use the new “SQL Version Agnostic MP”.
Thanks
Troubleshoot them! What version of SQL are they? Do you have any overrides on your discovery to exclude versions? Do you have the proper rights set up to discover them? Is the agent using local system? Have you configured SID monitoring properly? Is the agent event log filled with clues?
Hi Kevin,
We are noticing that sometimes the “HealthService SID Enabled Monitor” goes into Warning state and due to this we receive the Discovery alerts. We know we had Enabled the task HealthService SidSate…. and made sure the HealthService SID Enabled Monitor changes to healthy, before running the script to Create HealthService Login on SQL Servers. Currently we have again Run the task Enable HealthService SidSate…. if we find this happening.
This has happened couple of times. What could be the reason for this issue.
Reinstalling/upgrading the agent will do that. Possibly even a repair.
You should enable the recovery I added to the monitor, which will automatically fix it if detected.
Thank you Kevin!
I guess, when we enable the recovery “Configure HealthService SID and Restart Agent Script Recovery”, it will automatically run and enable the HealthService SID for the Servers whenever a new SQL Seed is discovered.
Yes. Or when it gets reverted back to default for some reason, like an agent reinstall.
Hi Kevin, thank for you reply!
I enabled the recovery “Configure HealthService SID and Restart Agent Script Recovery” by overriding it and changing the Enable Parameter to True, waited for one day. But I noticed that the Existing SQL Seed instances which were discovered earlier but did not have HealthService SID set have the Monitor “HealthService SID Enabled Monitor” still in warning state and did not change to healthy. I have to manually restart the agent for the monitor to change to healthy. Does it mean this recovery will only run for newly discovered SQL Seed instances and not for exisitng SQL Seed instances. Note: I also tried to check the Options “Run recovery automatically” and “Recalculate monitor state after recovery finishes”, but the resuts are same as mentioned above. Could you please advice. Thanks in advance.
Recoveries run at the moment when a monitor changes state from healthy to unhealthy. If you enable a recovery after the fact, it does not run on existing unhealthy monitors. That’s just SCOM by design. You need to use the task to enable service SID on any unhealthy SEED instances.
Never check the box to “Recalculate monitor state” in a recovery. This requires a special design and does not work for most monitors, and can create unintended conditions when monitors are not designed for it.
Hi Kevin,
I am using SCOM 2019 with SQL 7.20.0 MP. I have configured Service SID for SQL monitoring. on Some servers, i am getting below error:
Message: [Error number: 916] The server principal “NT AUTHORITY\SYSTEM” is not able to access the database “” under the current security context.
Appreciate, if you could help me to understand that why it is still trying to access the DB with “NT Authority\System” instead of Service ID?
AM i missing any configuration setting?
FYI: SQL version is 14.0.3162.1 on server where we are getting this error.
forgot to mention:
Error name is : MSSQL on Windows Discovery Error
And Database is already discovered.
I have the same exact issue on SCOM 2019 + SQL 2017. It keeps throwing errors on NT AUTHORITY\SYSTEM not having permission to access the model/OperationsManagerDW databases. It doesn’t seem to report anything on master, msdb, OperationsManager DB. What could have went wrong?
The NT SERVICE\HealthService is already present in the instance login and granted the Connect and View Any Definition rights.
Great tool as usual Kevin!
Couple things.
1. Microsoft is retiring the TechNet Gallery and not migrating projects to Github, so the link in this article will eventually die.
2. Doesn’t quite belong here, but I’ve discovered that Task failures generate an alert from insufficient rights when running the Create HealthService Login as Low Priv Task. I can’t find anything on how to squelch this alert. Any pointers?
We have implemented new SCOM version and now the task “Create Health Service login as a sysadmin” in our RUN-AS-Addendum MP fails.
I am administrator on the agent computer and MSSQL Sysadmin, and when i input my login and password to run this task, i get this error:
Logon failure: the user has not been granted the requested logon type at this computer. Error Code: -2147023511 (Logon failure: the user has not been granted the requested logon type at this computer).
I have tried to add my own account to “login as a service” on the server, and if i do that the task runs just as it did on the old SCOM versions. Can you fx change the task, so that it put my account into “allowed logon as a service” runs the task and removes my account from “allow logon as a service” or maybe the task should go directly into MSSQL with my account, and just add NT SERVICE\HealthService to Sysadmin without logging on as a service and all that stuff ?
This is a design change in SCOM 2019 and well documented. You cannot use a task to add log on as a service, as the task cannot log on as a service in the first place.
https://kevinholman.com/2019/03/14/security-changes-in-scom-2019-log-on-as-a-service/
If you want to run tasks in SCOM 2019 as a user account, that user account must already be granted log on as a service. This is a lower level privilege than “log on locally” which you already have….. as an administrator.
Thanks, and also thanks for the great MP we have been using that right from the start, and it saves us a lot of work.
The other approach going into MSSQL (logging in as my account already being sysadmin) and just creating the SQL Login domain\nt healthservice and giving it the rights) it that an option, off course i would have to provide SQL connectionstring and sqlport as override to the task…
Hi Kevin,
We have alerts related to SQL 2012 Always on in SCOM with event id 4101. Could you please help me understand the reason behind this .
Alert Name: Always On discovery script failed
Error in description:
Reason: Exception calling “LoadAllAlwaysOnPolicies” with “0” argument(s): “Data is Null. This method or property cannot be called on Null values.”
Position:215
Offset:2
Instance:MSSQLSERVER
Detailed error output: Exception calling “LoadAllAlwaysOnPolicies” with “0” argument(s): “Data is Null. This method or property cannot be called on Null values.”
Really appreciate any help on this.
Regards,
Swapna M
Hi Kevin,
We are using this method but due to upgrade of Mma due to log analytics extension upgrade,healthservice sid got removed and through recovery it came again,that is all fine.
But during this upgradation , we are recieving alert for database status with alert context “is accessible” false and monitoring status as bad..But after recovery enabled the health service sid again ,the alert get auto resolve
Can you please help on this situation?
What is there to help with? It sounds like it fixed itself, and all is working fine now?
Yes,but the database status is online showing in the alert context,then why its monitoring status showing as bad and generate an alert if health service sid not available for some time.
I understand from monitor logics that monitor will be healthy if database status is online.
Confused with the SQL DB Engine display. MSSQL on Windows: Agent, MSSQL on Windows: Local Agent, and MSSQL on Windows:Resource Pool Group. An explanation of each column would be really helpful?
Hi Kevin,
Need to download the latest version of Run As Addendum MP, kindly share us the Download link.
Thanks
Kumar B
Hello Kevin,
We’re trying to import this MP into our test environment however on import we get the following error:
“This management pack cannot be imported.
: XSD verification failed for the management pack. [Line: 68, Position: 90]
‘>’ is an unexpected token. The expected token is ‘=’. Line 68, position 90.”
I ideas as why?
Thank you!
How did you download the MP? Did you select the “CODE” button and “download zip” ?
Nope, I was just provided a folder with an XML.
I just redownloaded via the code button and all is good. Thank you very much.
Hello Kevin,
Thanks for the post, could you please suggest, in order to monitor SQL Always on and sql cluster nodes, Is it necessary to perform any other extra steps like remote enable/remote launch on all Monitored SQL servers (which are part of always on clusters) ?
Thanks,
santi
Hello Kevin,
any idea if we need to perform any additions to support for Microsoft.SQLServer.2014/12/16.AlwaysOn which also uses SQL run as account ?
Hi Kevin,
I have imported SQLRunAsAddendum MP. I found that under “SQL DB Engine on Windows” I only able to see the newest DB Engine. However, under “SQL Seed Class” I am able to see all different version SQL servers. Is that normal? Please advise.
Thank you!
Johnny
Yes – that’s normal, you need to modify the override discussed in this article:
https://kevinholman.com/2019/06/12/how-to-transition-to-the-sql-version-agnostic-mp/
Hi Kevin,
We have SQL Agnostic MP version 7.0.24.0 and Addendum MP 7.0.20.1.
Just checking was there any version update is available for Addendum MP.
Thanks,
Sharanya
As always thank you. I installed today the 7.0.20.1 version of this in my QC environment along side the 7.0.0.0 version. I did not read any place where I had to remove the older one. So I now have two Microsoft SQL Server RunAs config sections in my monitoring pane. The earlier one shows me SQL 2008,2012,2014,2016, the recent one shows only:
SQL DB Engine on Windows
SQL Seed Class
Is this correct? I am trying to make sure I am also capturing SQL 2019, which I have just today installed the latest updated SQL Agnostic MP over the earlier one.
Whewww
Thank you !!
Anthony
Yep – that’s normal. Once you remove the SQL 2012, 2014, and 2016 specific MP’s and transition to agnostic – you can remove those RunAs config MP’s specific to those versions. If you still have SQL 2008 (ugh!) then you can leave that one and the library.
Can you tell me how the discovery for the seed class is done? I have a server where sql was installed, then removed but is now still found in the RunAs Config – Seed class. It will not show up in the DB engine class of course as there is no real sql server installed anymore. Is it possible to set an override for the discovery ? Couldn´t find a discovery for the RunAs Config Mgmt pack…. Thx, Stephan
The seed class is part of the SQL MP.
The SQL agnostic SEED class discovery is script based and looks for ANY of these reg paths:
‘HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\110\Machines’
‘HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\110\Machines’
‘HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\120\Machines’
‘HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\120\Machines’
‘HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\130\Machines’
‘HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\Machines’
‘HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\150\Machines’
If you still have a seed instance, it is because you still have one of these keys, as long as the discovery is still running.
Hi Kevin,
Is Run as Addendum Management Pack” goes fine with SQL MP Version 7.0.24 ?
We have newly installed SCOM 2019 environment,after importing SQL MP Version 7.0.24 is there any other steps to be followed.
Kindly let us know.
Regards,
Ravi shankar
Yes, that is the most current version of the RunAs addendum. The steps that need to be followed are the same for any version of SCOM – you need to enable the service SID on the agents, and apply the security for the NT Service\Healthservice account as a SQL login, per this article.
Thanks for the update Kevin
Hi Kevin,
Is Run as Addendum Management Pack” goes fine with SQL MP Version 7.0.24 ?
We have newly installed SCOM 2019 environment, after importing SQL MP Version 7.0.24 is there any other steps to be followed.
Kindly let us know.
Regards,
Ravi shankar
Hello,
I am following this correctly I believe, but if the action account for the sql server is not sysadmin, i get a securables error, although I the HealthService was created correctly as a sysadmin task. How is this possible? I understand the whole point of this is to no grant sysadmin permissions to a service account. It is like it is using the action account anyway.
Look at the troubleshooting steps. In every case I have ever worked – it was one of those being missed.
Hey, thanks for replying. I checked every step there.
1. Health Service SID needs to be enabled in the registry. (ServiceSidType = 1)
This is correct, I used the MP and also the SQL Seed CLass panel shows healthy on every SQL cluster node.
2. Health Service must be restarted in order for SID’s to work after enabling service SID in registry.
This has been restarted, actually this has been on producciotn for a while, and server have been fully rebooted several times now.
3. A SQL login must be created for NT SERVICE\HealthService at the instance level.
I checked this ofr every clustered instance, and the account is created.
4. The proper rights must be granted to NT SERVICE\HealthService at the instance level. (low priv script or sysadmin).
Now I have all instances with sysadmins rights for the healthservice account. I have actually tried both scenarios.
5. 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.
This is a cluster, and the SYSTEM account does exist and is not disabled.
So as soon as de action account is removed from sysadmin role (this is an AD service account, not local System), even though the HealthService account is created and is sysadmin, the following Securables return error in the MP
NoAccessSecurablesList
1. Server-level permissions: • VIEW SERVER STATE • VIEW ANY DEFINITION 2. Tables: • 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
Why are you using a domain account as the default agent action account? That is less secure in most cases. I have not tested using that as I always recommend using Local System as the default agent action account.
Hi Kevin, so that was the issue, I switched to local SYSTEM action account and problem solved, I also compltely removed the SOMC Actin Account from SQL login and no problem.
“I always recommend using Local System as the default agent action account.”
I definitely should read your blog more. I will change the AA to local System for all monitored servers.
Hi Kevin,
Is this MP supports for SQL 2019 Versions ?
Thanks
Kamal.
Kevin, you rock man!!! Thanks a lot!!!!
Hi Kevin,
Thanks for this great article. I have an out of box question.
Am a DBA. Monitoring is all working fine. However, I understand the requirement of creating a new account ‘NT SERVICE\HealthService’ and granting necessary permission in SQL. My question is
1. In this case, Shouldn’t I see SCOM connections using ‘NT SERVICE\HealthService’ account?. May I know why am I seeing it from NT AUTHORITY\SYSTEM rather than HealthService?
2. For some cases, am also seeing SCOM connections using CNO account (Domain\serveraname$).
Hi.
We discovered in our environment that MS DPM requires setting NT AUTHORITY\SYSTEM as SysAdmin , so IF Your SQL’s are backed up by DPM, then allowing Service SID and granting permission is not needed 🙁 run as local system.
https://docs.microsoft.com/en-us/system-center/dpm/back-up-sql-server?view=sc-dpm-2019
Of course there is no proof of quarantee that MS doesnt fix DPM in next versions.
Hi Kevin
I have been looking for documentation on how to combine SQL Server monitoring with Service SID and SQL Replication monitoring. In SQL Replication monitoring you can’t use Service SID.
I’m currently testing to set up the monitoring following the low privileges from Microsoft docs but I’m unsure on how it will work together?
On servers where I need to monitor something in SQL that can’t use Service SID monitoring what is the best practices to set that up?
If I add a runas account does it work together with Service SID? Or on those servers do I need to set up fully low privileges monitoring according to Microsoft docs?
Thanks
Had this working for some time, and as is often the case, when the MP gets updated, the permissions need updated as well.
Cloning from the script above seems as though it might be missing a permission: sys.xp_instance_regread
Unless that is supposed to be added with one of the grants to the role, it looks as though it is missing.
Hi Kevin,
Hope you are doing well
I am getting following error message with latest SQL Agnostic MP. Could you please kind enough to help me with fix for same. Even i was trying to share screenshot of whole error message but could do it
Module : Microsoft.SQLServer.Windows.Module.Monitoring.monitors.SPNStatus.Monitor
MP : version 7.0.32.0
message : unable to determine whether the computer is domain joined (i have checked and server is in target domain)
Hi Kevin,
Could you please help us with following unknown issue.
Part I went well and i was able to enable it. However Part – II did not well for us.
Enable HealthService SID and Restart Agent Task
Task Description
Status:Success
Scheduled Time:9/23/2021 12:16:55 PM
Start Time:9/23/2021 12:16:56 PM
Submitted By:Domain\Service account
Run As:
Run Location:
Target:
Target Type:MSSQL on Windows: Local Discovery Seed
Category:Maintenance
Task Output:
Script is starting.
Running as (nt authority\system).
Attempting to enable SID for Healthservice.
[SC] ChangeServiceConfig2 SUCCESS
Restarting SCOM agent now…
Part – II
##########################
Create HealthService Login as Low Priv Task
Task Description
Status:Success
Scheduled Time:9/23/2021 12:18:44 PM
Start Time:9/23/2021 12:18:44 PM
Submitted By:Domain\Service account
Run As:Domain\Service account
Run Location:
Target:
Target Type:MSSQL on Windows: DB Engine
Category:Maintenance
Task Output:
Script is starting.
Running as (Domain\Service account).
SQLServer: (Servername.corp.Domain.com).
Opening Connection using connect string: (Servername.corp.Domain.com).
ERROR opening connection to: (Servername.corp.Domain.com). Will Attempt using Name,Port
Opening Connection using connect string: (Servername.corp.Domain.com,1433).
ERROR opening connection to: (Servername.corp.Domain.com,1433).
TERMINATING.
Error is: (Exception calling “Open” with “0” argument(s): “Login failed for user ‘Domain\Service account’.” Exception calling “Open” with “0” argument(s): “Login failed for user ‘Domain\Service account’.”).
The user you are using to connect to the database to setup the healthservice user doesn’t have the rights to do so. You need to have sysadmin rights.
So make sure that the user you start the action “Create HealthSerivce login” have sysadmin rights. Or you can get help from your DBA:s to run the t-sql script, at the end of the blogpost, on the SQL servers you will monitor.
Hey Kevin,
I imported the pack, but I don’t see the Task options you list. No ability to remediate. I drilled down into the SQL Seed alert and configured it to auto remediate and recalc status after. No change yet however. Any ideas why the tasks won’t show up in my console?
Hi Kevin,
I am facing the same issue with only one of our Production server. Server is discovered under SQL Seed class but not visible under SQL DB Engine on windows. Its been more than 24 hours.
Only one server is having this issue other servers are discovered and executed Login as Low Priv Tasks.
Please help here. Thanks,
Bala
Hi Kevin
the Microsoft System Center Management Pack for SQL Server version 7.0.34.0 update just came out.
Do you know if your SQLRunAsAddendum version 7.0.32.0 still supports this version of the MSSQL management Pack?
Kind Regards,
Niels
Yes it does. I have been running .34 since 12/22/2021 when the MP shipped.
oh my bad ? it’s been awhile since we did an update run.
Thanks for the quick response!
Hi Kevin,
Good day! We are now in the process of upgrading our SCOM to SCOM 2019 and we are using your multihoming MP. Will there be any issue for SQL MP after we multihome this? Do we need to do again the tasks in the new environment even after we have done this already in our old SCOM?. This is considering we already have imported the accounts and profiles in the new SCOM. Thanks in advance.
Hi Kevin,
Good day!
We are currently using version 7.0.36.0 of your MP and we are experiencing few alerts of type MSSQL on Windows: Discovery error.
HealthService SID is enabled and agent is restarted for the instances that report this alert.
My question was the following: is the version 7.0.36.0 that we are using by any chance incompatible with the management of Run as Accounts?
Thanks in advance!
My current MP is https://github.com/thekevinholman/SQLRunAsAddendum 7.0.36.1
There are no incompatibilities – I am not really sure what you mean by that question. The security rights script for configuring a DB engine changes from time to time as the SQL MP version changes. When in doubt – re-run the SQL DB engine low priv task/script
Does the SQL RunAs Addendum management pack work for SCOM 2019 on SQL 2016+ servers? I have an environment of SQL 2016 and above that I would like to install this management pack for, but dont know if it’ll support SQL 2016 and newer versions?
Thanks in advance.
Absolutely.
It works for any version of SCOM and SQL 2012 and later.
Thanks for confirming, Kevin!
Hi,
I imported the MP but I noticed that we already monitor the databases, even if we didn’t configured neither Service SID nor SQL privileges.
We’re not using runas profile.
Is it possible that the agent which uses “local system” as default action account is already able to query (and monitor) all the databases without further privileges?
Thanks,
Jack P.
If you are not using a RunAs account/profile for SQL, and you did not configure Service SID’s and SQL login permissions for the Service SID – then you are using the Default Agent Action account to run all workflows – which should be Local System.
If this works for discovering and monitoring SQL – then it simply means your SQL servers are configured to allow a very high level of privilege to Local System. This is generally considered a worst practice, because it allows people to impersonate local system and then gain access to SQL servers and database information. I’d question your DBA’s if they are granting System Administrator rights to Local System, and/or BUILTIN\Administrators – both of which is not configured by default nor recommended in a very secure environment.
Pingback:SCOM 2022 Installation, gMSA, Standalone Web Console Server - The Monitoring Guys
could you please suggest how to monitor SQL from workgroup machine if other domain SQL server are monitoring through run as account ( less secure )
Tried below steps :
>> Enable the HealthService to be able to use a service SID.
>> Created Login on workgroup – SQL database with sysadmin role.
>> Added login under “logon as a service”
But workgroup server is not showing under SQL seed class / DB engine
Seed class discovery does not require any advanced rights. It should show up if SQL is installed.
Can SCOM 2019 monitor SQL with both below option at same time :
Run as account ( Less secure ) — to Monitor SQL for domain servers
and service SID enable —– To monitor SQL 2016 from Workgroup servers.
Yes. But I am not sure why anyone would choose to use a Runas account for SQL servers in a domain, which is less secure.
Good morning Kevin,
Does this management pack support gMSA accounts? We want to create a SQL specific gMSA account that has all the rights needed on all our SQL Servers and provide it the Log on as a service right on all the SQL servers in order to make it easy for us to implement this MP.
We are currently on SCOM 2019 UR4 so I am on a UR of SCOM 2019 that supports gMSA accounts per Microsoft.
Thanks in advance.
Good morning Kevin,
Just to clarify my last question – the SQL team has a gMSA account that they can use to run these tasks. That account is already able to Log on as a service on all their SQL servers AND the rights to create the healthservice account within the Databases and give it the Sysadmin role. Since this account is a gMSA account, will it work when running the tasks or do you have to use a non-gMSA account?
Ahhh that now makes sense. So the question is – “can we supply a gMSA credential to execute a task in SCOM” ?
I don’t know. I would just simply try it. I know we do support using GMSA as a RunAs account, and you should be able to make that the default RunAs account for running a task with a little modification. But I’d try just inputting the gMSA on a manual run first. What happens when you do?
Hey Kevin, It worked! We tested with a GMSA account and that bad boy went through and performed both tasks without any issue.
Hello Kevin,
we have a SCOM 2019 setup with around 50 SQL servers. I have set everything up like you have described with 2 test machines. Your management pack states “green” on those two machines (SQL Seed Class).
As soon as I take away the former Run-As account, the server throws an error:
——————–
Alert description: An account specified in the Run As profile “Microsoft.SQLServer.DiscoveryAccount” cannot be resolved.
This condition may have occurred because the account is not configured to be distributed to this computer. To resolve this problem, you need to open the Run As profile specified below, locate the account entry as specified by its SSID, and either choose to distribute the account to this computer if appropriate, or change the setting in the profile so that the target object does not use the specified account.
Note: you may use the command shell to get the Run As account display name by its SSID.
Management Group: XYZ
Run As Profile: Microsoft.SQLServer.DiscoveryAccount
Account SSID: (long number) 00000000000000000000000000000000000000
What am I doing wrong?
———————
You need to remove the association in the SQL server Runas Profiles from the RunAs accounts. They are still associated.
Since the Profiles target a class and *all* objects, I have added the two test-machines as single object/windows-computer with the local-system-account (not the RunAs) in the profile.
I hope this addition works as an exception for the test-machines.
No alert message so far.
I will proceed further down the road with your helpful advice.
Thank you for your reply and help.
Hi Kevin,
Great article!
It solved our problems with monitoring SQL servers.
But for some reason, I keep getting this alert on over SCOM server (where the SQL server for SCOM, also is located):
“No access to the following SQL Server securable(s):
1. Server-level permissions:
• VIEW SERVER STATE
• VIEW ANY DEFINITION
2. Tables:
• msdb.dbo.sysjobschedules
• msdb.dbo.log_shipping_primary_databases
• msdb.dbo.log_shipping_secondary_databases
3. Stored procedures:
• sys.xp_readerrorlog
• msdb.dbo.SQLAGENT_SUSER_SNAME”
NT SERVICE\HealthService is created as Low Priv Task on all SQL servers. But on the SCOM server, I have tried to change the SQL server rights to SysAdmin.
But that hasn’t changed anything. We still seeing the alert.
Do you have any thoughts?
Do you have SQL and SCOM Management Server role installed on the Server OS? If that is the case, then the SQL MP will no use Local System, it will use the domain account for Management Server Action account. The Service SID should still work in that case, though, but you’d need to check to make sure it is enabled and restart the MMA service.
Hi
Do you fix this problem?
What happens with SSRS, when SSRS and the databases are on different servers? Presume you have to utilise runas profiles/runas accounts for this purpose and target those runas profiles at the specific SSRS discoveries and monitors?
Yes. Or you can use SSID, but you’d need to apply permissions on the remote SSRS databases to the domain computer accounts. I have not tested the SSRS MP with this model.
So I guess that opens up the risk you’re mitigating with granting the Health Service specific permissions to SQL DB’s via a SID e.g. not allowing local system to be sysadmin. In this case someone on the SSRS server could run processes under the system context and have full rights to the SQL instance.
I deployed the SSRS management pack briefly but saw these issues and figured it needed some deeper thought. We also collect login failures from SQL error logs and raise informational alerts in SCOM and this was quite chatty with SSRS servers failing to talk to SQL instances. Good to see you still bearing the torch of SCOM, I have used your advice and guidance since 2007, invaluable to us on-prem monitoring die hards.
Agreed, thats a risk – however I would never grant sysadmin. Only the lowest priv possible.
In SSRS MP, its likely better to leverage traditional runas accounts.
My customer discussed all this with me and we did a very basic SSRS MP instead, discovering SSRS and just monitoring the core Windows Service.
Hi there, great MP thanks.
For me to get the servers to show in the SQL DB Engine view, was i had to enable to ‘Allow this agent to act as a proxy…’ on the agents
Is this normal practice, as i didnt see it mentioned in the article.
thanks
Not necessary because ALL customers should have done this already:
https://kevinholman.com/2017/03/10/enable-proxy-as-a-default-setting-in-scom-2016/
Leaving agent proxy at the default is silly. Something that should have been changed in the product years ago.
Brilliant, thanks. Hadnt seen this article
Pingback:Why Addendum packs - Kevin Justin's Blog
Morning Kevin,
My org wants to run the script at the bottom of the page manually. Does the script need to be ran in Powershell or does it need to be ran in SQL Server Management Studio?
sorry for the noob question, just wanted to ask and make sure haha.
Will there be an update from SQLRunAsAddendum version 7.0.42.0 for adaption to the latest SQL Server on Windows management pack version 7.2.0.0 that was released in july? Maybe it’s not necessary…(?)
Hi Kevin,
Thanks for sharing this fantastic work. So I´m having a situation where only few Servers don´t show in SQL Seed Class. In the logs in these servers I can see that the management pack “SQLRunAsAddendum” in not been delivered for some reason. I´ve tried to clean the cache on the agent but no luck. How Ca I try to troubleshot this ?
The seed class comes from the SQL MP – so it is normal not to receive the addendum MP if the SQL MP seed class is not first discovered. Troubleshooting would be reviewing the SEED class discovery and see what’s missing. I believe it is looking for a set of reg keys.
Hi Kevin,
Few servers are showing healthy in ‘SQL 2016 Seed’ (HealthService SID Enabled Monitor is healthy), but its not reflecting under ‘SQL 2016 DB Engine’ even after running the task “Enable HealthService sidstate” and clearing the cache on agent server.
Could you please suggest how to fix this.
Look in the event logs on the agent. Likely a permissions issue. Clearing cache almost never solves any issues.
Hi Kevin
We have SQLRunAsAddendum 7.0.42.0 and latest SCOM and SQL2019 Versions (AlwaysOn). Under “SQL DB Engine on Windows” there is a column “MSSQL on Windows Availability Group Health”. This column does use the system policies to get the health, but it executes also on the secondary nodes. 3 of these Policies must be run only on primary Nodes and give error when running on the secondaries. Can you fix this?
Hi
Does this work with the newest SQL MP version (7.4.0.0)?
Thank you.
Hi Kevin,
We have SQL MP version 7.0.42.0 and Run As Addendum MP version 7.0.42.0 in our SCOM 2019 setup, and planning to upgrade the SQL MP to latest version 7.6.5.
Can we use the same Run As Addendum MP ?
Best regards,
Syed
Yes – I do not update the runas addendum unless there is a change that requires it.
Thanks Kevin 🙂