Over the years I have written many articles dealing with RunAs accounts. Specifically, the most common need is for monitoring with the SQL MP. I have explained the issues and configurations in detail here: Configuring Run As Accounts and Profiles in OpsMgr – A SQL Management Pack Example
Later, I wrote an automation solution to script the biggest pain point of RunAs accounts: distributing them, here: Automating Run As Account Distribution – Finally!
Now – I want to show a different approach to configuring monitoring for the SQL MP, which might make life a lot simpler for SCOM admins, and SQL teams.
What if I told you – there was a way to not have to mess with RunAs accounts and the SQL MP at all? No creating the accounts, no distributing them, no associating them with the profiles – none of that? Interested? Then read on.
The big challenge in SQL monitoring is that the SCOM agent runs as Local System for the default agent action account. However, Local System does not have full rights to SQL server, and should not ever be granted the SysAdmin role in SQL. This is because the Local System account is quite easy to impersonate to anyone who already has admin rights to the OS.
We can solve this challenge, by introducing Service SID’s. SQL already uses Service Security Identifiers (SID’s) to grant access for the service running SQL server, to the SQL instance. You can read more about that here: https://support.microsoft.com/en-us/kb/2620201
Service SID’s were introduced in Windows Server 2008 and later.
We can do the same thing for the SCOM Healthservice. This idea was brought to me by a fellow MS consultant – Ralph Kyttle. He pointed out, this is exactly how OMS works to gather data about SQL server. We have an article describing this recommended configuration here: https://support.microsoft.com/en-us/kb/2667175
Essentially – this can be accomplished in two steps:
- 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.
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
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 disabled by default. You can enable this and it will automatically configure this as soon as SQL is detected, and will restart the agent.
This monitor is configured to alert, however there is an included override to disable the alerting. You can delete the override, or override it yourself to enforce alerting on this monitor.
Additionally – I wrote a task you can run. The task configures the service SID and then restarts the agent Healthservice. You can multi-select items in this view and run against multiple agents, making this one-time configuration easy.
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 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.
***NOTE – this task will always report success. You NEED to review the output to ensure that it didn’t throw any errors.
What this actually does – is create this login on the SQL server, and then set it to have the rights necessary for the SQL MP to work.
All of these activities are logged for audit in the Task Status view:
The current SQL MP as of this article update (188.8.131.52) 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’s to be available below:
Here are the actual SQL scripts if you prefer to run these manually or have automation already in place:
--(SQL2012 and later)
SET NOCOUNT ON;
DECLARE @accountname sysname = 'NT SERVICE\HealthService';
-- In some cases, admins 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 and grant instance level permissions
-- Create the server role with authorization to the account associated to principal id = 1
-- Create the role 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'
GRANT VIEW ANY DATABASE TO [SCOM_HealthService];
--GRANT ALTER ANY DATABASE TO [SCOM_HealthService]; --Required only for SCOMDB tasks
GRANT VIEW ANY DEFINITION TO [SCOM_HealthService];
GRANT VIEW SERVER STATE TO [SCOM_HealthService];
DECLARE @createLoginCommand nvarchar(200);
SET @createLoginCommand = 'IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE [Name] = '''+ @accountname +''') BEGIN CREATE LOGIN '+ QUOTENAME(@accountname) +' FROM WINDOWS WITH DEFAULT_DATABASE=[master]; END'
-- Add the login to the user defined server role
EXEC sp_addsrvrolemember @loginame = @accountname, @rolename = 'SCOM_HealthService'
-- Add the login and database role to each database
DECLARE @createDatabaseUserAndRole nvarchar(max) SET @createDatabaseUserAndRole = '';
SELECT @createDatabaseUserAndRole = @createDatabaseUserAndRole + ' USE ' + QUOTENAME(db.name) + '; 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; EXEC sp_addrolemember @rolename = ''SCOM_HealthService'', @membername = '+ QUOTENAME(@accountname) + ''
-- 'ALTER ROLE [SCOM_HealthService] ADD MEMBER ' + QUOTENAME(@accountname) + ';'
FROM sys.databases db
LEFT JOIN sys.dm_hadr_availability_replica_states hadrstate ON db.replica_id = hadrstate.replica_id
WHERE db.database_id != 2 AND db.user_access = 0 AND db.state = 0 AND db.is_read_only = 0 AND (hadrstate.role = 1 or hadrstate.role IS NULL);
-- Add database specific permissions to database role
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];
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].[syscategories] 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];
GRANT EXECUTE ON [dbo].[SQLAGENT_SUSER_SNAME] TO [SCOM_HealthService];
EXEC sp_addrolemember @rolename='SQLAgentReaderRole', @membername='SCOM_HealthService';
--End of Query
--(SQL2005 and SQL 2008 on WS2008 and later)
--(Will Not work on WS2003 - but you can use NT AUTHORITY\SYSTEM for the accountname)
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+'];
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';';
EXECUTE sp_executesql @command1;
EXECUTE sp_executesql @command2;
EXECUTE sp_executesql @command3;
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