SQL MP Run As Accounts – NO LONGER REQUIRED


 

image             image

 

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!  Then – took it a step further, and built this automation into a management pack here:  Update-  Automating Run As Account distribution dynamically

 

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 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.

 

I even 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.  You will need to enable that if you want alerting on this monitor.

 

 

 

Additionally – I wrote two tasks you can run – the second one configures the service SID, but will wait for the next reboot (or service restart) before this actually becomes active.  The first task configures the service 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 Low Priv mode.  You just need to choose whichever you want to use for your organization.

 

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 set it to SysAdmin role:

 

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.  You should use 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:

 

--(SQL 2005 - SQL 2008) 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 WHERE database_id <> 2; 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;

 

--(SQL 2012 - SQL 2016) 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 (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;

 

 

Known issues and additional configuration:

 

1.  You might see the following for SQL servers participating in a SQL Always On Availability Group:

 

Alert:  MSSQL 2012: Discovery failed

Event ID: 7103. Management Group: SCOMA. Script: ModuleAction Module: GeneralAlwaysOnDiscovery.ps1 Version: 6.7.7.0 : Error occured during Always On discovery.
Computer: SQL2A
Reason: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Position:272
Offset:13
Instance:MSSQLSERVER
Detailed error output: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
——–
Property _CreationTime does not exist at path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Modules\{D89D9334-B5EF-9E25-B666-B42F0FC7C792}\S-1-5-18\Script\SQLMPSP1\SqlHostNames.

 

This is because of a change in version 6.7.7.0 of the SQL MP – to support cluster servers with names longer than 15 characters.

Each primary of an availability group will connect via WMI to read the registry of every replica server in the availability group.  The two discovery scripts that fail are GeneralAlwaysOnDiscovery.ps1 and DatabaseReplicaAlwaysOnDiscovery.ps1

You will see event ID 7103 logged in the OpsMgr event log on the primary, which triggers the alert.

 

This happens when running the SQL discoveries as Local System, because the computer account of the Primary does not have access to the replica servers in order to read the registry.

To easily resolve this issue:  Add the computer account of each primary to the Local Administrators group of each replica server in the availability group.  Since a failover can occur by design, you should also add each replica’s computer account to the Local Administrators group of the Primary and any other Replica servers.  This only affects rights to the Operating System – not SQL server security itself, as we are simply reading the registry via WMI.  You must also ensure that any firewalls are open between these servers for remote WMI calls over RPC.

Example:

I have two SQL servers in an AlwaysOn configuration – SQL2A and SQL2B.  I will add the Computer Account of SQL2A to the Local Administrators group on SQL2B:

 

image

and repeat for SQL2A’s local admins group:

image

 

This will immediately allow the error to go away, and they will discover the “Availability Replicas” and the “Database Replicas” for the Availability Group.

 

Placing Local System of one computer as Administrator over another should truly give no cause for alarm.  Especially since these computers share a common role and rights would already be the same between them.  However, if there is some extreme pushback for granting this – there are lowest priv configurations documented here:  https://blogs.msdn.microsoft.com/sqlreleaseservices/released-system-center-management-pack-for-sql-server-and-dashboards-6-7-7-0/#comment-19656

 

 

2.  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.

 

These tasks run simply the following, which could also be run manually:

 

To grant the HealthService SID SA role:

sqlcmd.exe -E -S $Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$ -Q "sp_addsrvrolemember 'NT SERVICE\HealthService', 'sysadmin'"

 

To grant the HealthService SID Low Priv rights on SQL 2005 – SQL 2008R2:

sqlcmd.exe -E -S $Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$ -Q "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 WHERE database_id <> 2;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;"

 

To grant the HealthService SID Low Priv rights on SQL 2012 – SQL 2016 (this script accounts for AlwaysOn databases that are not readable):

sqlcmd.exe -E -S $Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$ -Q "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 (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;"

 

 

$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.


Comments (73)

  1. Finally! Thanks for the article Kevin.

  2. Classy mate…..as always. Thanks for another invaluable contribution.

  3. Juliën M says:

    Very nice article, thanks Kevin! Does the login as low priv have enough rights to read and collect the information needed? In other words, sa account is not necessary?

    1. Kevin Holman says:

      Yes – low priv is the minimal rights needed to fully monitor SQL with the MP. The only reason I didn’t recommend Low Priv in the past, was because it was a main to administer. With these new addendum MP’s that makes it simpler.

      1. Juliën M says:

        Great! Thanks Kevin

  4. Thanks for another Excellent contribution Kelvin. When SCOM 2016 will be going to released officially ?

  5. Alexander says:

    Good afternoon.
    Following the action SCOM ceased to be friends with the agents on servers with SQL.
    Detail Error: “System Center Management Health Service Credentials Not Found Alert Message”
    Alert Monitor: “System Center Management Health Service Credentials Not Found”

  6. Philippe MERIGOT says:

    Hi all,
    I’m sorry for this possible stupid question but it’s a little confused for me.
    I’ve installed this MP without any issue (cool) on my Test Environment and for 2 SQL 2014 instances.
    Everything work well.

    But I’ve also installed the SSRS 2014 MP …. and I want to know if I need to follow the RunAsAccount Procedure dedicated of this MP ? or not ?
    Thanks for your help

    1. Kevin Holman says:

      I haven’t played with the SSRS MP – so I dont know if this runas account model will work for that or not. Normally to monitor SSRS we just need local administrator, which Local System has – but I’d have to research that first. So for SSRS – I’d keep doing whatever you are doing today.

      1. Philippe MERIGOT says:

        Tks Kevin.
        Always helpfull to read you

        1. NBM says:

          Hey Kevin,

          Did you ever get a chance to further explore this with SSRS (any version) to see if it worked or not? We’re getting conflicting results in our lab by year 2012 vs 2014 vs 2016.

  7. Tomas Rybnicky says:

    Hello Kevin I got strange error during Always On discovery after deploying your solution with running monitoring agent using ServiceSID, SQL discovery itself is working properly but I got following error in AG environents.

    Management Group: XXXXXXXXX. Script: ModuleAction Module: DatabaseReplicaAlwaysOnDiscovery.ps1 Version: 6.7.7.0 : Error occurred during Always On Database Replica discovery.
    Computer: XXXXXXXXX
    Reason: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
    Position:210
    Offset:26
    Instance:DB_PR01
    Detailed error output: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
    ——–
    Property _CreationTime does not exist at path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Modules\{597EDA61-F992-03CB-5EAD-95FFE9169439}\S-1-5-18\Script\SQL2014MP\SqlHostNames.
    ——–
    Property _CreationTime does not exist at path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Modules\{597EDA61-F992-03CB-5EAD-95FFE9169439}\S-1-5-18\Script\SQL2014MP\SqlHostNames.

    Registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Modules\{597EDA61-F992-03CB-5EAD-95FFE9169439}\S-1-5-18\Script\SQL2014MP\SqlHostNames exists, but value mentioned “Property _CreationTime” is not there but there is something like this “SQLFQDN”_CreationTime is there some more rights needed on OS level to discover proper Registry values or it is problem of SQL MP itself.

    Same problem with SQL 2012 and SQL 2014.

    Looking forward to your answer.

    1. Kevin Holman says:

      I’m investigating a suitable configuration workaround for this now.

      They changed the MP and added a workflow that does a remote connection to other servers in the AG. This didnt exist in previous versions of the MP.

    2. Kevin Holman says:

      I documented the fix at the bottom of the article. It’s simple

  8. Jesse says:

    Hi Kevin, has this been tested against SQL Clusters? Does it works the same way?

    1. Kevin Holman says:

      Yes, and it does for me, no complaints from my customer testing as well.

      1. Thanks. And what are the steps here? Enable SID on each node. Create login on active node. Then?

  9. new2scom says:

    Hi Kevin,

    Had hoped your new addendum MP was finally going to resolve my ongoing headache of administering my RunAs account distribution config for monitoring our SQL estate. However I’m having ongoing issues esp Re discovery of SQL 2012 which appears to be a common problem as per a number of posts on https://social.technet.microsoft.com/Forums/systemcenter/en-US/a7d7d4cc-ba08-4263-b08a-6455d75b4e1c/sql-mp-6770-access-denied-for-always-on?forum=operationsmanagermgmtpacks

    So was wondering if you could clarify a few queries:

    1. Does your addenum config using SSID negate the need for the additional steps Dmitriy Kalashnakov mentions in the blog post for all the additional config of WMI security on all other replicas in availability group – or do we still meet to perfom these additional WMI config steps on replica machines. Dimitriy also mentions “While we do not support the ServiceSID for HealthService….” – so not sure how that stacks up?
    2. If I create the HealthService login as Low Priv, must I then continue to configure the low priv environment on the agent machine – just confused on this as the MP guide describes this in terms of the RunAs config/distribution using the SQLDefaultAction, SQLDiscovery and SQLmonitoring accounts which we are not now using if going with SSID config.
    3. If I create HealthService Login as SA, is it doing anything different regarding the WMI/registry permission stuff (as per Low Priv) or just giving higher SQL level permissions (apologies for my confusion but I’m def not a SQL guy)
    4. Should you Addendum MP work OK with SQL MP ver 6.7.7.0, which is the version I upgraded to

    Just for clarification I’m using Local system as default action account and i have removed the RunAs configuration which was previously used (but was not working particularly well ever since previous upgrade to 6.7.2.0 which I hoped the SSID/Addendum MP was going to resolve).

    Cheers

    1. Kevin Holman says:

      1. No – but I documented a simpler solution in the article now.

      2. The are two privs – OS rights, and SQL rights. No configuration is required for the OS – since Local System has everything needed. For SQL – we are using the Service SID which is more secure and much easier to configure and maintain.

      3. No, just giving higher level SQL permissions (not necessary, but fewer configuration items to troubleshoot)

      4. My addendum MP is updated all the time – based on customer feedback and issues I see. It is now 6.7.7.2 at the time of this posting – and works fine with any SQL server MP version from 6.5.1.0 onward.

  10. John F says:

    Kevin,

    As usual, your mastry of all things SCOM comes to the rescue! (assuming I get it working in my lab first). Do you know if this also works with SQL 2016? I’m having issues getting the RA account to distribute to some new instances.

    Also, is it possible to do something like this for AD? 😀

    1. Kevin Holman says:

      Yes it works with SQL 2016. But I do away with all that distribution mumbo jumbo. 🙂

      AD??? There is no need for RunAS with the ADMP. The ADMP runs under Local System and local system has rights to do just about anything on the domain controller. I have never configured RunAs with the ADMP.

  11. John F says:

    Question for post deployment of the credential trick above. I’ve had our DBA run the scripts to change the accounts and then removed the Run As from our SCOM instance. I then received a flood of credentials not found alerts. Not unexpected. We ran a test against an availability group to make sure that we would get alerts, which we did. Is it best practice at this point to disable that rule?

    Also, you are correct on AD Run As. I had set up the account for an added layer of security. However, I’ve found that it doesn’t work too well cross domain… 🙂

    1. Kevin Holman says:

      Absolutely do NOT disable that rule.

      You should not have seen any alerts. The proper method to remove a previous RunAs configuration – is to remove the ASSOCIATIONS from the PROFILEs *first*. Then once those association have been removed – the agents no longer try to even use the runas account. Next – you would remove the DISTRIBUTION of the credentials. Lastly – once the agents got updated that they no long need the credential distributed – you delete the credential from SCOM. It is easy – you just got in the exact opposite order as you would when setting up RunAs accounts.

      1. John F says:

        Thanks for the clarification, Kevin.

  12. new2scom says:

    Hi Kevin,

    Thanks for answering/resolving an issue I posted on the SCOM forum which your updated MP has largely resolved.

    Just a couple of additional questions on this Addendum MP

    1. I have created my HealthService login in SQL login as Sysadmin (by running your MP task). If our SQL team insist I need to use Low Priv instead, can I just run that task from the SCOM console to overwrite existing account, or do I (or SQL team) need to manually delete the account that had been created as SA role first.
    2. If I need to revert agents that have had SID State Enabled (again from running the console task) to use the ‘standard’ agent setup of Local System which I may then need to distribute a RunAs profile to, how do I go about this – do I need to change a regkey/value or something. The reason being is that the addendum pack and config has resolved 99% of my agent issues but I have a few machines still with discovery monitoring failures. I think the possible cause of this is that they are running SQL 2008 but have some custom scripted mirroring solution which according to the SQL guys replicates (pardon the pun) an Always On type solution on SQL 2008 with mirroring. One of these pairs was being monitoried OK using the ‘old’ method but since changing to used SID its now failing discovery and therefore not monitoring – and the SQL team advised the other pair happens to use this custom mirroring scripted solution. So was just gonna try and see what happens if I revert then back to not longer use SID…

    Thanks again…

  13. Martin S says:

    Hi Kevin,
    First of all – thank you for sharing your hard work.
    I followed one of your posts on the initial scom install, and I used a domain user account as the default run as account. When I manually install scom agent I have the choice to use the “system” account or the domain user account. So far, I have used the domain user account. So, in the context of this post – Does it matter which one I use?
    Thank you,
    Martin

    1. Kevin Holman says:

      I always recommend deploying agents using Local System as the default agent action account.

  14. OdgeUK says:

    Hi Kevin

    Implemented this with great success. Thank you! One thing I have noticed is that servers with SSRS DBs are appearing in the Seed view, but not being picked up in the DBEngine view. Does the monitor not see SSRS DBs as ‘proper’ engines?

    1. Kevin Holman says:

      Correct. I cannot control whats in the “seed” class – because this class is in the sealed SQL MP’s, and will pick up ANYTHING with a registry entry for a SQL component. It will contain SSRS servers, servers with SQL client components, servers with SQL management studio installed, etc.

      It wont hurt anything to enable the service SID on SEED class instances that don’t contain SQL DB engine…. which will set the monitor healthy, or you can just disable the monitor for those instances.

  15. Oli says:

    Hello, how i could fix that/adding Healthservice on SQL which runs on cluster (agentless)? There a nt service\healthservice does not exists

    1. Kevin Holman says:

      I dont understand your question.

  16. Martin S says:

    Hi Kevin,
    As you pointed out, in order to use SCOM Healthservice to be able to access SQL server we need to:
    1.Enable the HealthService to be able to use a service SID.
    2.Create a login for the HealthService SID to be able to access SQL server.

    I usually install the Local Agent manually. During the wizard I have two choices for the “Agent Action Account”. So far, I have used a Domain Account (which is my default RunAs Account); however, it appears I needed to choose “Local System” instead. Could you confirm it?
    Thank you Kevin!

    Martin

    1. Kevin Holman says:

      Technically – for THIS solution to work – you can use a domain account OR local system – because the whole purpose of using Service SID – is that the SID is used for authentication – it doesnt matter what account is running the service.

      That said – I never use a domain account for the default agent action account, and always recommend using Local System by default for agents. The only time I’d ever use a domain account for the default agent action account is in a highly secure environment with lowest priv required, and VERY limited monitoring.

      1. Martin S says:

        Thank you Kevin! I really appreciated your timely response. I am in the middle of adding a good number of Windows Servers to SCOM monitoring, and I was in the dilemma on what to use for the “Agent Action Account”.
        With Respect,
        Martin

  17. Odgeuk says:

    Hi Kevin
    If you configure a DB Engine to make Healthservice Low Priv only, will this turn the “Heathservice is SA” monitor Healthy?

    1. Kevin Holman says:

      No – if you are using low priv you should disable the monitor that checks to see if it has SA rights.

      1. Odgeuk says:

        Gah, now I have to ‘remember’ which were done as Low Priv and which simply aren’t done at all (other than checking for Discovery Failed alerts). Be great to see a monitor which confirmed that HealthService had been added.

        We are really finding this has helped us in our environment, previously ASGs just didn’t bother configuring this as it was too much hassle. Many Thanks for the solution.

  18. RaimondsB says:

    This package works for SCOM 2016 with SQL MP 6.7.15.0?
    After imorting addendum 6.7.7.2 I didn’t see nothing in “Monitoring” section …

    1. Kevin Holman says:

      Yes, it does.

      Did you look under SQL server as shown in the images above??

      1. RaimondsB says:

        Yes, same level as “SQL Server database Engines”, and “SQL Server Reporting Services”.
        Addendum MP only visible under “Installed management Packs” in “Administration” section.

        1. RaimondsB says:

          I understood. For each SQL version, in addition, is necessary to import of individual MP, which is an separate XML file …

          1. Dail Davis says:

            Re: “Yes, same level as “SQL Server database Engines”, and “SQL Server Reporting Services”.
            Addendum MP only visible under “Installed management Packs” in “Administration” section.”

            I’m having this same issue in SCOM 2012 R2. Is there a resolution?

  19. NBM says:

    Kevin is their anything unique about SQL Server Reporting Services? We seem to have this working everywhere but on SSRS and we keep getting the following failures:

    Event ID: 4001. Management Group: XXXXXXX. Script: DiscoverSQL2012DB.vbs. Version: 6.7.15.0 : Unable to get database exclude list registry key. Permission denied

    Event ID: 4001. Management Group: XXXXXXXX. Script: DiscoverSQL2012DBEngineDiscovery.vbs. Version: 6.7.15.0 : The next errors occured:
    Access denied while reading registry value [\\XXXXXXXXXXXXXXXXXXXXXX\HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\Parameters\(Default)]:

    This monitor uses the System.OleDbProbe and runs the query: select count(*) from syslogins where sysadmin = 1 AND Name = ‘NT SERVICE\HealthService’
    The expected value is “1”

    When we run this directly in SQL against the Report Server we do indeed get back a value of 1.

    Any thoughts?

    1. Kevin Holman says:

      NBM – your issue is that your default discovery runs account is getting access denied. Either you are not using local system (which has access to the registry) or you still have runas accounts in your profile associations which are breaking it….. using an account for discovery that doesn’t have enough rights. Using this model I prescribe in this article – you should not use any profile associations for the SQL MP’s.

      1. NBM says:

        Thanks Kevin!

        We got this resolved and cleaned up on our end. Since we originally had 4 SQL LowPriv RunAs accounts (we haven’t migrated to our Production environment yet) you can imagine we had these scattered across all the different Profiles for SQL.

        We removed all the RunAs accounts from these and did another double check against the SQL side and everything is now working at intended. We’re currently formalizing a plan now to migrate this to Production.

        Thanks for this great solution!

  20. Bob Higgins says:

    How do I setup the SID user (NT SERVICE\HealthService) in the Run As Account from the console? When I try to create the Run As account, it requires a password, which the SID doesn’t have. So I can’t create the Run As account. I’m missing something here?

    1. Bob Higgins says:

      Ok. Ignore my last question. I just realized that the whole point of this is not to use a defined Run As account. Once the SQL portion is setup correctly, the service will have permissions. So no need to add a run as account at all. Thanks for the great article.

  21. Birdal says:

    Hi Kevin,
    thank you for your article.
    I could import your MP, but no folder named “SQL Server RunAs Addendum” under “MONITORING > Microsoft SQL Server”.
    Best Regards
    Birdal

    1. Kevin Holman says:

      Did you import the library? That contains the views.

      1. Birdal says:

        Hi Kevin,
        I had only imported Library, but not xml. Now I exported also xml, I can see folders..
        Thank you.
        Regards
        Mustafa

  22. Wes S says:

    Hi Kevin –
    Your SQL RunAs addendum MP works great for SQL 2008/2012 but once I try to use it for SQL 2014 I get the SQLCMD.exe error.

    I have been trying to figure this out on my own and in my case I think it’s due to the fact that the SQL Server 2014 DB Engine (Discovery) seems to be looking at the registry value of SQLPath when actually the path to the SQLCMD.exe is under the registry value ODBCToolsPath. So my for ToolsPath points to (SQLPath) F:\Program Files\Microsoft SQL Server\120\Tools\Binn\ instead of (ODBCToolsPath) F:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\

    I reviewed the SQL Server 2014 DB Engine Discovery Source for 6.7.15.0 MP from http://systemcentercore.com/?GetElement=Microsoft.SQLServer.2014.DBEngineDiscovery&Type=DataSourceModuleType&ManagementPack=Microsoft.SQLServer.2014.Discovery&Version=6.7.15.0

    and searched for ODBCPath (which didn’t return anything) then SQLPath and located
    Public Property Get ToolsPath()
    If (m_bIs64Bit) Then
    ToolsPath = g_oSQL.ReadRegistryStringValue(g_oSQL.SQL_TOOLS_PATH_WOW64, “SQLPath”)
    Else
    ToolsPath = g_oSQL.ReadRegistryStringValue(g_oSQL.SQL_TOOLS_PATH, “SQLPath”)
    End If

    If (IsNull(ToolsPath)) Then
    ToolsPath = “”
    End If
    End Property

    I am not sure but it really does seem to me that this is why I am getting the sqlcmd.exe error.

    I haven’t seen any other posts with my issue and at this point I am not even sure if I am looking in the right place or if there is anything that I can do from my side to get this to work outside of having the SQL DBA’s run the HealthService SID task manually.

    Any guidance/suggestions would be appreciated.

    Thanks,
    Wes

    1. Kevin Holman says:

      Hi Wes,

      You are right. It looks like SQL 2014 made a change here….. https://msdn.microsoft.com/en-us/library/ms143547(v=sql.120).aspx

      SQLCMD.exe and BCP.exe are now located at \Client SDK\ODBC\110\Tools\Binn

      However, this all worked in my environment anyway, because the above location is in the PATH environment variable, which makes SQLCMD available from any location.

      So it looks like you guys are missing that directory from your path environment variable, which is allowing this to break.

      Your options are to:

      1. Fix the PATH environment variable to include this location like default installations will.
      2. Hard code the path (assuming you ALWAYS installed SQL 2014 to the same location)
      3. COPY SQLCMD.exe to the original path,
      4. Write a new task, that is powershell script based, that gathers the location from the registry in the script, then calls SQLcmd.exe in the script.

      1. Wes S says:

        Thanks Kevin, I appreciate the explanation and the options.

      2. Jason H says:

        You could update the working directory on the tasks like this.
        2014:
        $Target/Property[Type=”SQL2014Disc!Microsoft.SQLServer.2014.DBEngine”]/ToolsPath$\..\..\Client SDK\ODBC\110\Tools\Binn

        2016:
        $Target/Property[Type=”SQL2016Disc!Microsoft.SQLServer.2016.DBEngine”]/ToolsPath$\..\..\Client SDK\ODBC\130\Tools\Binn

  23. Tobias says:

    Low priv
    SQL 2016 MP need rights in Model DB
    Script: DiscoverSQL2016FileGroups.js. Version: 6.7.15.0.
    (USE [model] 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’ )

    I added this
    USE [model]
    EXEC sp_addrolemember @rolename=db_datareader, @membername=[NT SERVICE\HealthService]

    Don´t know if it´s right way to go?

    1. Kevin Holman says:

      Not sure I understand – the script I provided already does this for the model DB.

      1. Tobias says:

        Perhaps i’m missing something but i can only find Changes on Master and MSDB can´t find anything on model.

        From your MP
        USE [master]
        GRANT VIEW ANY DATABASE TO [NT SERVICE\HealthService]
        GRANT VIEW ANY DEFINITION TO [NT SERVICE\HealthService]
        GRANT VIEW SERVER STATE TO [NT SERVICE\HealthService]
        GRANT SELECT on sys.database_mirroring_witnesses to [NT SERVICE\HealthService]
        USE [msdb]
        EXEC sp_addrolemember @rolename=PolicyAdministratorRole, @membername=[NT SERVICE\HealthService]
        EXEC sp_addrolemember @rolename=SQLAgentReaderRole, @membername=[NT SERVICE\HealthService]

        Event ID: 7105.
        Error Description : [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal “DOMAIN\COMPUTER$” is not able to access the database “model” under the current security context.

        I don´t get the error then im using the not so low priv with sysadmin for the HelathService.

      2. Tobias says:

        Sorry my bad. Account was created in the database in the wrong schema.

  24. Roland says:

    Hi Kevin,

    Thanks for the great mp. Any chance you will make the monitor to check if the healthservice has the appropiate Low Priv rights?

    1. Kevin Holman says:

      Funny – I was just working on that…. it is a lot more complicated!!!

      1. OdgeUK says:

        I was just about to ask about this. If we use the Low Priv task, will the “is SA?” monitor every show healthy? I think not at the moment? Can be tricky to look back and see what’s been done and what remains.

  25. Andreas Ramseier says:

    Our Domain Accounts with SysAdmin Roles are only with SmartCard-Logins. And it seems that SA Account is not working with your solution…. So basically the only way to use your “Create HealthService Login” Task In the MP is to temporary enable SysAdmin Role to the local System account….

    It would be great if you could manage to user Domain Accounts with SmardCard and/or SA Account

    Thank’s
    Andy

  26. Morning, Quick question please, as I am still learning all of this, as I go…Do I need to undo the Run as Account and Run as Profile associations I have previously done (as well as the MP created to hold changes) in accordance with your earlier articles before implementing and testing the SID solution, as well as??
    Thanks again!
    Tony

    1. Kevin Holman says:

      Yes, that is recommended to ensure service SID auth is working.

  27. Juliën M says:

    Hi Kevin,

    Maybe I am missing the point here but when will the new account (NT Service\Health Service) become active? Or how am I able to check if the Health Service account is used or not? I get the feeling that it still uses the standard Local System account.

  28. Janez says:

    HI all,
    the MP is great and really facilitates the work of SCOM Admin. Has anyone tried to create an OLE DB check and run it under NT SERVICE\HealthService account? I give HealthService SA rights and create OLE DB to measure some performance in SQL. The Connection Time counter is available and it is showing data but under monitor I’m getting error “Data Source could not be initialized – Login failed for user DOMAIN\COMPUTERNAME$ (the watcher node for OLE DB). So how to configure the OLE DB profile to use HealthService for running query against SQL? Is it possible or we must create another user to use it in OLE DB profile?
    Thanks,
    Janez

  29. mitheun says:

    Hi Kevin,
    I imported your pack and provided the NT/Healthservice account low priv rights in sql. Post that I removed the association of the existing sql run as accounts (action, discover and monitor) from the existing run as profiles (action, discover and monitor). How can I now ensure that the SCOm agent uses this newly created NT/Healthservice for monitoring the SQL parameters. Do I have to make any changes to the existing default scom_action accounts?

  30. Kevin, thank you for this great article. I had some trouble getting this to work though. I followed the steps described above, but I kept getting an error on the HealthService can connect to SQL 2014 Monitor. After some troubleshooting I found that the problem was the presence of the local system account in SQL, even though the account login was disabled and there were no roles mapped. It looks like the agent has a preferred order for using accounts. So if the local system account is present in SQL it will try to use this account instead of the HealtService. The moment I deleted the System account, everything worked fine. Do you know if this order of using accounts is the case?

  31. Robert Pollock says:

    Hi Kevin,

    Great article so much better not using the runas account however I have one issue with SQL always on Availability groups. I keep getting the below alerts for the replica:
    Event ID: 4001. Management Group: ASH-MGMTGroup. Script: GetSQL2012DBFreeSpace.vbs. Version: 6.7.15.0 : The next errors occured:

    Error 1:
    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: MSSQLSERVER

    ERROR2:
    Event ID: 7103. Management Group: ASH-MGMTGroup. Script: DiscoverSQL2012FileGroups.js. Version: 6.7.15.0. Instance: MSSQLSERVER : File Groups Discovery script ‘DiscoverSQL2012FileGroups.js’ for instance ‘MSSQLSERVER’ failed.
    Inner exception:
    Error Number :
    Error Code : 0
    Win32 Facility : 0
    Error Description :
    Call stack:Exception.constructor(File Groups Discovery script ‘DiscoverSQL2012FileGroups.js’ for instance ‘MSSQLSERVER’ failed.,Can’t execute query ‘ SET NOCOUNT ON; USE [model] 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 “model” 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 “NT AUTHORITY\SYSTEM” is not able to access the database “model” under the current security context.
    Call stack:Exception.constructor(Can’t execute query ‘ SET NOCOUNT ON; USE [model] 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 “model” under the current security context.,[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.
    Error Number : -2147467259
    Error Code : 16389
    Win32 Facility : 0
    Error 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.
    ),
    ADODB.ExecuteQuery( SET NOCOUNT ON; USE [model] 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({D6B26EFE-E183-24E9-DD23-F165CB716A28},{EAD99108-074D-E517-FAD7-1FE79E15CC99},GBLONUSQLTAGV01.firm.local,GBLONUSQLTAGV01,MSSQLSERVER,1433),
    ),
    Main({D6B26EFE-E183-24E9-DD23-F165CB716A28},{EAD99108-074D-E517-FAD7-1FE79E15CC99},GBLONUSQLTAGV01.firm.local,GBLONUSQLTAGV01,MSSQLSERVER,1433),

    I’ve granted admin right to each server computer on each node and followed your article is there anything else I’m missing

    1. mitheun says:

      Hi Robert,

      I had all these errors previously. But after resorting to this single run as account approach , all the outstanding errors have now vanished and my scom sql console looks great

  32. Ben says:

    What if the SQL Instance comes up as “Not Monitored” in the SQL Seed section ?
    – for me this seems to be for clustered SQL instances only.

  33. Gerald Versluis says:

    Hi Kevin,

    As to all the praise you get for this, I just want to say “dito” 🙂
    I’ve made a small adjustment to the queries, making them tidier when the login or user exists.
    For Command1, instead of
    USE [master]; CREATE LOGIN
    I put in
    USE [master];IF NOT EXISTS(SELECT * FROM sys.server_principals WHERE name = ”’+@accountname+”’) CREATE LOGIN
    Similarly, for Command2 I changed:
    ‘USE [‘+db.name+’]; CREATE USER
    to
    ‘USE [‘+db.name+’];IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = ”’+@accountname+”’) CREATE USER

    Works on my end, makes our rollout tidier, but the usual caveat: no warranties…

Skip to main content