How to monitor SQL without Run As accounts
The original article explaining this is here:
https://blogs.technet.microsoft.com/kevinholman/2016/08/25/sql-mp-run-as-accounts-no-longer-required-2/
Environment:
My SCOM 2012 R2 lab. I had already installed SQL MP version 7.0.2.0 which covers SQL 2005 - 2012 [need to confirm versions]
Steps in order:
The only instance I had to test with was my SCOM one running SQL 2014 so I had to install version 7.0.2.0 of the 2014 mp.
I installed these MPs (the others were already installed or weren't needed)
Click on the SQL 2014 DB Engine view. There's two tasks that create the SQL login for the HealthService. One gives SA rights and the other is low priv. In this test I used low priv so I ran the Create HealthService Login as Low Priv in SQL 2014 task. If you run this task without enough sql access you will get an error - I'm assuming you need SA to run it. For me it ran without errors but there is no output in the task.
That's it.
Result:
This seemed to work well but it's not a true test as it's not a prod environment. There's also the need to create the NT SERVICE\HealthService on every SQL server so is it really any less pain than using Run As?
Testing on SQL 2008 SP4 instance.
I installed SQL 2008 Enterprise Eval Edition with SP4. I named the instance 'FARMYARD1' and created a domain user account called 'SvcSqlFarmyard' that was used as the logon account for all services.
After about 10 minutes the instance appeared in SCOM and again all monitoring seemed to be working without any Run As stuff configured.
Then I imported Microsoft.SQLServer.RunAs.2008.Addendum.xml and the instance appeared in the SQL 2008 Seed view in a warning state. I ran Enable HealthService SidState Only Task and bounced the HealthService manually. After a few minutes the monitor went green.
Instead of running the task to create the SQL login I ran the SQL script below (2008 version) on the server and it created the NT SERVICE\HealthService login.
Result:
I think it works OK but I did get this alert:
Event ID: 4001. Management Group: MG1. Script: GetSQL2008DBFilesFreeSpace.vbs. Version: 7.0.2.0 : The next errors occurred:
Cannot connect to the target Sql Server instance. Connection log:
Connection to data source 'DC\FARMYARD1' failed: [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Server is in script upgrade mode. Only administrator can connect at this time.
Do some research on it. I think it came thru before I set this up so try to replicate it again.
SQL scripts to create login manually:
For SQL2012 - SQL2016 versions:
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 ['+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 SQL2008 versions:
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=''PolicyAdministratorRole'', @membername='''+@accountname+''';
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';';
EXECUTE sp_executesql @command1;
EXECUTE sp_executesql @command2;
EXECUTE sp_executesql @command3;
For SQL2005 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+'];
USE [msdb];
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';';
EXECUTE sp_executesql @command1;
EXECUTE sp_executesql @command2;
EXECUTE sp_executesql @command3;
https://blogs.technet.microsoft.com/kevinholman/2016/08/25/sql-mp-run-as-accounts-no-longer-required-2/
Environment:
My SCOM 2012 R2 lab. I had already installed SQL MP version 7.0.2.0 which covers SQL 2005 - 2012 [need to confirm versions]
Steps in order:
The only instance I had to test with was my SCOM one running SQL 2014 so I had to install version 7.0.2.0 of the 2014 mp.
I installed these MPs (the others were already installed or weren't needed)
- Microsoft.SQLServer.2014.Discovery
- Microsoft.SQLServer.2014.Monitoring
- Microsoft.SQLServer.2014.Presentation
- Microsoft.SQLServer.2014.Views
I didn't have to bounce the HealthService and the SQL instance appeared straight away in SCOM. It seemed to be monitored OK too. I'm guessing it's because it's the SQL instance for SCOM therefore has more access? I would think on a normal SQL server this wouldn't happen.
I got this alert:
SQL Server cannot authenticate using Kerberos because the Service Principal Name (SPN) is missing, misplaced, or duplicated.
Service Account: LAB\svcsql
Missing SPNs: MSSQLSvc/MS1.lab.com:1433
Misplaced SPNs:
Duplicate SPNs:
Service Account: LAB\svcsql
Missing SPNs: MSSQLSvc/MS1.lab.com:1433
Misplaced SPNs:
Duplicate SPNs:
So I ran this command on the DC, bounced the HealthService on my SCOM server and it went away.
setspn -S MSSQLSvc/MS1.lab.com:1433 LAB\svcsql
Download Kev's latest addendum MP from https://gallery.technet.microsoft.com/SQL-Server-RunAs-Addendum-0c183c32
As of version 6.7.31.0 the zip file contained these files - I only installed highlighted ones.
As of version 6.7.31.0 the zip file contained these files - I only installed highlighted ones.
- Microsoft.SQLServer.RunAs.2005.Addendum.xml
- Microsoft.SQLServer.RunAs.2008.Addendum.xml
- Microsoft.SQLServer.RunAs.2012.Addendum.xml
- Microsoft.SQLServer.RunAs.2014.Addendum.xml
- Microsoft.SQLServer.RunAs.2016.Addendum.xml
- Microsoft.SQLServer.RunAs.Addendum.Library.mp
Click on the SQL 2014 Seed view and if it's green that means the service SID is configured. Mine was in a warning state, meaning it needs to be setup. This doesn't generate an alert by default but you can override the monitor if you want.
I ran the Enable HealthService SidState and Restart HealthService Task which looks to see if
HKLM\SYSTEM\CurrentControlSet\Services\HealthService\ServiceSidType = 1. The value doesn't exist by default and the task just adds the ServiceSidType value and sets it to 1.
HKLM\SYSTEM\CurrentControlSet\Services\HealthService\ServiceSidType = 1. The value doesn't exist by default and the task just adds the ServiceSidType value and sets it to 1.
After a few minutes the monitor went green - I'll dig into the mp one day to see exactly what those tasks do.
Click on the SQL 2014 DB Engine view. There's two tasks that create the SQL login for the HealthService. One gives SA rights and the other is low priv. In this test I used low priv so I ran the Create HealthService Login as Low Priv in SQL 2014 task. If you run this task without enough sql access you will get an error - I'm assuming you need SA to run it. For me it ran without errors but there is no output in the task.
That's it.
Result:
This seemed to work well but it's not a true test as it's not a prod environment. There's also the need to create the NT SERVICE\HealthService on every SQL server so is it really any less pain than using Run As?
Testing on SQL 2008 SP4 instance.
I installed SQL 2008 Enterprise Eval Edition with SP4. I named the instance 'FARMYARD1' and created a domain user account called 'SvcSqlFarmyard' that was used as the logon account for all services.
After about 10 minutes the instance appeared in SCOM and again all monitoring seemed to be working without any Run As stuff configured.
Then I imported Microsoft.SQLServer.RunAs.2008.Addendum.xml and the instance appeared in the SQL 2008 Seed view in a warning state. I ran Enable HealthService SidState Only Task and bounced the HealthService manually. After a few minutes the monitor went green.
Instead of running the task to create the SQL login I ran the SQL script below (2008 version) on the server and it created the NT SERVICE\HealthService login.
Result:
I think it works OK but I did get this alert:
Event ID: 4001. Management Group: MG1. Script: GetSQL2008DBFilesFreeSpace.vbs. Version: 7.0.2.0 : The next errors occurred:
Cannot connect to the target Sql Server instance. Connection log:
Connection to data source 'DC\FARMYARD1' failed: [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Server is in script upgrade mode. Only administrator can connect at this time.
Do some research on it. I think it came thru before I set this up so try to replicate it again.
SQL scripts to create login manually:
For SQL2012 - SQL2016 versions:
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 ['+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 SQL2008 versions:
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=''PolicyAdministratorRole'', @membername='''+@accountname+''';
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';';
EXECUTE sp_executesql @command1;
EXECUTE sp_executesql @command2;
EXECUTE sp_executesql @command3;
For SQL2005 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+'];
USE [msdb];
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';';
EXECUTE sp_executesql @command1;
EXECUTE sp_executesql @command2;
EXECUTE sp_executesql @command3;
Comments
Post a Comment