I have so many ideas from other products which I would like to blog about, but somehow I get stuck with SCOM all the time, like an addict. Well, today I implemented the SQL Server management pack for a large customer and as you might know you need to configure a Run as account and associate this Run as account with the SQL Server Run As Profiles. If you don’t know what I am talking about, check Kevin Holman’s excellent SQL Server MP capabilities and configuration.
The Run as account is going to be configured with distribution security option More Secure. This results in a problem, that we have to add all Health Service objects to the distribution tab – manually. It is not very sexy to do such tasks manually, that’s why we were blessed with PowerShell.
Ok just to show you what I am talking about. When you don’t add any Health Service (agents) object to the distribution tab of the SQL Run as account, there will be no way for the management pack to use this account to discover or monitor SQL Server via Run As Profile. SCOM will send an alert which informs you, that the discovery did not run because of missing credentials…
The following script gets the Run as account and checks SCOM for any of the alerts that are related to missing SQL Server Run as account credentials. For all valid alerts, the related SCOM agent object will be retrieved and added into an array. Finally the agent / Health Service array will be added to the Run as account distribution list. Cool!
After you ran the script, all servers are added to the list…
I think this is sexy, don’t you?
Update 09.02.2014: This script is meant only for a one time run during deployment of the SQL Server management pack. It will help you adding the agent objects to the SQL Server Run as account very easily without having the hassle of searching each SQL Server / Health Service object.
Tip: You could use Orchestrator or SMA to run this script on a periodic schedule BUT in order to do that you need to adjust the alert criteria to get alerts only with resolution state New (=0) like $alerts=Get-SCOMAlert | Where-Object {$_.Name -eq “System Center Management Health Service Credentials Not Found Alert Message” –and $_.ResolutionState –eq 0} this would only check the relevant alerts which have a resolution state of New. There might are also other factors you need to take into account when you are going to automate this process e.g. if you run it on a periodic schedule ALL servers will be added to the Run as account but there might be an exception from business which doesn’t want you to monitor a certain SQL Server etc.
I like the idea automating this process but you have to be very careful and adjust it to your business / technical requirements, that’s why I don’t provide a final solution.
This is AWESOME!!!!
Thank you Kevin!
Great to “read” from you,
Stefan
Stefan – two things –
Your $alerts gets all alerts, even closed ones, which might not be preferred?…. as multiple runs will include agents you already took care of.
Additionally – your script will replace the distribution based on found alerts. If you ran it on a schedule – it would constantly cause issues with creating new alerts each time it ran, until you had alerts for ALL the SQL servers. As it is – it would be good for a brand new deployment, and a single run?
Kevin,
Yes, you are absolutely right. Well, this script is meant only to run once during deployment of the SQL MP. Adding the Health Service objects is a real pain if there is no clear naming convention for SQL Servers, especially if you have 100 and more SQL servers, which was the reason I wrote the few lines of code.
I updated the post accordingly.
Thank you very much for your comments,
Stefan
Excellent stuff; I needed something like this. As stated, though, this relies on waiting until after SQL systems are discovered and alerts are generated. Of course, I would prefer distributing as soon as SQL 2012 is discovered instead of waiting for the alerts. Can’t the get-scomagent command somehow find systems with SQL 2012 discovered, and bypass the need to dig through the alerts altogether? Am I oversimplifying what’s going on here?
Ok, I see the potential flaw in my logic. I have the following script:
$RunAs = Get-SCOMRunAsAccount “”
$DistList = Get-SCOMGroup -DisplayName “SQL Server 2012 Computers” | Get-SCOMClassInstance | Select-Object -Property DisplayName
$Distribution = $DistList | foreach {Get-SCOMAgent -Name $_.DisplayName}
Set-SCOMRunAsDistribution ($RunAs) -MoreSecure -SecureDistribution $Distribution
However, if the RunAs account is required for discovery, then the group in line 2 won’t have the servers in it. 🙂
Still, at least in our instance, new agents installed to SQL 2012 servers did populate the “SQL Server 2012 Computers” group, even before the creds were distributed. We received the Run As alerts until running the script above, which successfully distributed the credentials to all SQL 2012 servers.
Hi John
Using the SQL SCOM Group as source should also work, good idea.
Thanks for the info!
Cheers,
Stefan
So, I am getting this alert for all non-SQL computers. This script will distribute the RunAs account to all these computers. Is it a good practice? Whatif we go to the mentioned Profile properties and changed it managed object from “All Objects” to “SQL Computer” class. For example, most of the time, you get this error for SQL Server Discovery Account profile. So change its managed objects to SQL Computers instead of All object. These errors will go away. Is this recommended?
Hi Stefan,
Nice one. Any possibility to do this on SCOM 2007 R2 ?