SQL Monitoring set up in SCOM with Minimum rights

 

SQL Monitoring set up in SCOM with Minimum rights:

I will discuss Today here how we can provide minimum permission to execute the SQL workflows in SCOM. Ideally it would be good to have Sysadmin rights on DB and be a member of the local administrator. But due to security policy and other challenges, SQL team would not be able to provide full access to the DB servers and DBs. So here is the plan how we can mitigate the situation.

1. Add the Account domain\test under Local administrator of the SQL server

2. Configure the “Allow log on locally” and “Log on as a service” local security policy setting to allow the Account domain\test to log on locally and log on as a service

Log on Service is needed to discover the seed class else it will fail with the below error. You can configure a GPO to automate this.

 

Log Name: Operations Manager
Source: HealthService
Date: 5/18/2019 3:11:33 PM
Event ID: 1102
Task Category: Health Service
Level: Error
Keywords: Classic
User: N/A
Computer: server.domain.root
Description:
Rule/Monitor “Microsoft.SQLServer.2016.DBEngineDiscoveryRule.Server” running for instance “server.domain.root” with id:”{AACE2E35-D0C2-5F6A-7DD5-6E33C9310AA1}” cannot be initialized and will not be loaded. Management group “SCOM2016”

 


Log Name: Operations Manager
Source: HealthService
Date: 5/18/2019 3:11:17 PM
Event ID: 7002
Task Category: Health Service
Level: Error
Keywords: Classic
User: N/A
Computer: server.domain.root
Description:
The Health Service could not log on the RunAs account domain\test for management group SCOM2016 because it has not been granted the “Log on as a service” right.

 


LAB\test account is my SQL account here.


3. From DB end, we need to grant the below access. Please replace the account domain\test before running the query in the following article.

https://github.com/souravmahato7/SQL/blob/SCOM/SQLDatabasePermission.sql


You just need to replace the account name here: SET @accountname = ‘domain\test’;


For SQL  2017+, you need to run the below Script. This is when User account is not created.

SET NOCOUNT ON;
DECLARE @accountname nvarchar(128);
DECLARE @command1 nvarchar(MAX);
DECLARE @command2 nvarchar(MAX);
DECLARE @command3 nvarchar(MAX);
SET @accountname = 'domain\test';
SET @command1 = 'USE [master];
CREATE LOGIN ['+@accountname+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master];';
SET @command2 = '';
SELECT @command2 =@command2 + 'USE ['+db.name+'];
CREATE USER ['+@accountname+'] FOR LOGIN ['+@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);
SET @command3 = 'USE [master];
GRANT VIEW ANY DATABASE TO ['+@accountname+'];
GRANT VIEW ANY DEFINITION TO ['+@accountname+'];
GRANT VIEW SERVER STATE TO ['+@accountname+'];
GRANT EXECUTE ON xp_readerrorlog TO ['+@accountname+'];
GRANT SELECT on sys.database_mirroring_witnesses to ['+@accountname+'];
USE [msdb];
GRANT EXECUTE ON msdb.dbo.sp_help_job to ['+@accountname+'];
GRANT EXECUTE ON msdb.dbo.sp_help_jobactivity to ['+@accountname+'];
GRANT SELECT ON sysjobs_view to ['+@accountname+'];
GRANT SELECT ON sysschedules to ['+@accountname+'];
GRANT SELECT ON sysjobschedules to ['+@accountname+'];
GRANT SELECT ON log_shipping_monitor_history_detail to ['+@accountname+'];
GRANT SELECT ON log_shipping_monitor_secondary to ['+@accountname+'];
GRANT SELECT ON log_shipping_secondary_databases to ['+@accountname+'];
GRANT SELECT ON log_shipping_monitor_primary to ['+@accountname+'];
GRANT SELECT ON log_shipping_primary_databases to ['+@accountname+'];
EXEC sp_addrolemember @rolename=''PolicyAdministratorRole'', @membername='''+@accountname+''';
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';';
EXECUTE sp_executesql @command1;
EXECUTE sp_executesql @command2;
EXECUTE sp_executesql @command3;

 

For (SQL 2012 – SQL 2016), you need to run the below Script. This is when User account is not created.

SET NOCOUNT ON;
DECLARE @accountname nvarchar(128);
DECLARE @command1 nvarchar(MAX);
DECLARE @command2 nvarchar(MAX);
DECLARE @command3 nvarchar(MAX);
SET @accountname = 'domain\test';
SET @command1 = 'USE [master];
CREATE LOGIN ['+@accountname+']
FROM WINDOWS WITH DEFAULT_DATABASE=[master];';
SET @command2 = '';
SELECT @command2 =@command2 + 'USE ['+db.name+'];
CREATE USER ['+@accountname+']
FOR LOGIN ['+@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);
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=''PolicyAdministratorRole'', @membername='''+@accountname+''';
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';';
EXECUTE sp_executesql @command1;
EXECUTE sp_executesql @command2;
EXECUTE sp_executesql @command3;


For SQL 2008, you need to run the below Script. This is when User account is not created.

 

SET NOCOUNT ON;
DECLARE @accountname nvarchar(128);
DECLARE @command1 nvarchar(MAX);
DECLARE @command2 nvarchar(MAX);
DECLARE @command3 nvarchar(MAX);
SET @accountname = 'domain\test';
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=''PolicyAdministratorRole'', @membername='''+@accountname+''';
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';';
EXECUTE sp_executesql @command1;
EXECUTE sp_executesql @command2;
EXECUTE sp_executesql @command3;

 

If User account is already present, then you need to run the following:

For SQL 2017+, you need to run the below Script. This is when User account is already created.


SET NOCOUNT ON;
DECLARE @accountname nvarchar(128);
DECLARE @command3 nvarchar(MAX);
SET @accountname = 'domain\test';
SET @command3 = 'USE [master];
GRANT VIEW ANY DATABASE TO ['+@accountname+'];
GRANT VIEW ANY DEFINITION TO ['+@accountname+'];
GRANT VIEW SERVER STATE TO ['+@accountname+'];
GRANT EXECUTE ON xp_readerrorlog TO ['+@accountname+'];
GRANT SELECT on sys.database_mirroring_witnesses to ['+@accountname+'];
USE [msdb];
GRANT EXECUTE ON msdb.dbo.sp_help_job to ['+@accountname+'];
GRANT EXECUTE ON msdb.dbo.sp_help_jobactivity to ['+@accountname+'];
GRANT SELECT ON sysjobs_view to ['+@accountname+'];
GRANT SELECT ON sysschedules to ['+@accountname+'];
GRANT SELECT ON sysjobschedules to ['+@accountname+'];
GRANT SELECT ON log_shipping_monitor_history_detail to ['+@accountname+'];
GRANT SELECT ON log_shipping_monitor_secondary to ['+@accountname+'];
GRANT SELECT ON log_shipping_secondary_databases to ['+@accountname+'];
GRANT SELECT ON log_shipping_monitor_primary to ['+@accountname+'];
GRANT SELECT ON log_shipping_primary_databases to ['+@accountname+'];
EXEC sp_addrolemember @rolename=''PolicyAdministratorRole'', @membername='''+@accountname+''';
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';';
EXECUTE sp_executesql @command3;

 

For (SQL 2012 – SQL 2016), you need to run the below Script. This is when User account is already created.

SET NOCOUNT ON;
DECLARE @accountname nvarchar(128);
DECLARE @command3 nvarchar(MAX);
SET @accountname = 'domain\test';
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=''PolicyAdministratorRole'', @membername='''+@accountname+''';
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';';
EXECUTE sp_executesql @command3;


For SQL 2008, you need to run the below Script. This is when User account is already created.

SET NOCOUNT ON;
DECLARE @accountname nvarchar(128);
DECLARE @command3 nvarchar(MAX);
SET @accountname = 'domain\test';
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=''PolicyAdministratorRole'', @membername='''+@accountname+''';
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';';
EXECUTE sp_executesql @command3;


I hope this will help.