Menu Close

SQL MP Run As Accounts – NO LONGER REQUIRED

image

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

 

 

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

 

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

 

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

 

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

 

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

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

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

 

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

 

Essentially – this can be accomplished in two steps:

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

 

That’s it!

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

 

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

sc sidtype HealthService unrestricted

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

sc qsidtype HealthService

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

image

 

 

Management Pack:

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

 

Let me demonstrate:

 

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

 

image

image

 

 

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

 

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

 

image

 

This monitor does not generate any alerts to limit noise, it shows state only.  You will need to enable that if you want alerting on this monitor.

 

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

 

image

 

Here is what it looks like in action:

 

image

 

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

In the Addendum MP for each SQL version – 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 Priviledge mode.  You just need to choose whichever you want to use for your organization.  Low Priv SQL scripts are documented at the end of this article.

 

image

 

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

 

image

 

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

 

image

 

Voila!

 

image

 

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

 

What this actually does – is create this login on the SQL server, and then set it to SysAdmin role (or assign Lowest Priv rights needed for monitoring) depending on which task you chose:

 

image

 

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

 

image

 

To further assist with this configuration, I added two additional monitors:

 

The first monitor turns unhealthy if we cannot connect to SQL at all:

image

 

The second monitor turns unhealthy if we CAN connect to SQL, but we detect that the login for “NT Service\HealthService” does not have the “SysAdmin” role.  This Monitor is disabled by default as many customers are electing to use Lowest Priv.  You should use/enable this monitor if you are granting the SysAdmin role to the Healthservice, and you should disable it if you are using Lowest Priv.  It specifically checks to see if the Healthservice login is configured as a SysAdmin:

image

 

None of the monitors generate alerts by default, to limit adding noise to SCOM.  If you want alerting you can enable that in the MP and configure it as you wish.

 

***NOTE:  These monitors run every 4 hours by default, so after making the changes to add the NT Service\Healthservice to SQL, it will take that long before the monitors change state, unless you bounce the health service on the agent to speed that up.  So be aware.

 

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

 

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

 

 

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

 

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

 

 

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

 

--(SQL2017+) 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 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;
--(SQL2012 - SQL2016) 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;
--(SQL2008) 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;
--(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;

 

 

Known issues and additional configuration:

 

1.  You might get a “sqlcmd.exe is not recognized as an internal or external command, operable program, or batch file”

The tasks to enable the healthservice login into the SQL instance are very simple.  They just call SQLCMD.EXE and pass a command to create the login.  This of course requires that SQLCMD.EXE is present on your SQL server, in the <WorkingDirectory>$Target/Property[Type=”SQL!Microsoft.SQLServer.DBEngine”]/ToolsPath$\Binn</WorkingDirectory>

The above is a discovered property of the SQL DB engine – so it is required that we have discovered this property to pass this directory location.

Another thing that might cause this is UAC.  If User Account Control is enabled on the server with maxed out restrictive settings – this task might fail.  Try opening a command prompt on the server, and type in “sqlcmd.exe” and see if this works.

$Target/Property[Type=”SQL!Microsoft.SQLServer.DBEngine”]/ConnectionString$ is simply the class property of “Connection String” in SCOM for the SQL DB engine:

image

This will vary based on how this is discovered by the SQL MP, if you have a default or named instance, custom ports, etc.

13 Comments

  1. Bijesh NS

    Hi Kevin,

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

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

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

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

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

    Monitoring Failed alerts:

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

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

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

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

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

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

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

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

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

    Please advise us on this

    Regards,

    Bijesh NS

    • Konstantin

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

      Regards
      Konstantin

      • Kevin Holman

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

  2. Patrik

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

    1. Server-level permissions:
    • VIEW ANY DEFINITION

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

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

      • Kevin Holman

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

        The current SQL MP as of this comment is 7.0.7.0

  3. Cyr Az

    Hi Kevin,

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

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

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

    Thanks!

    • Kevin Holman

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

      • Cyr Az

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

  4. David Bowles

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

    • Kevin Holman

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

Leave a Reply

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