Creating Run As Accounts for the SQL MP using PowerShell

The title of this post is probably a little misleading because you can apply these cmdlets to any RunAs Account and Profile. So today I’ve been out at a customers site and was setting up the SQL Management Pack for them. Since this is something I’ll probably be doing a lot, I figured let’s just script it. Now I’m not going to write some long fancy script when really a few one-liners will suffice…so let’s see how we can create Run As Accounts from PowerShell and use this account in a profile…we’ll even see how we can set the target for this profile.

So firstly I want to create a new Run As Account which will be based on a Windows Account. When I run this, by calling the (Get-Credential) cmdlet it will pop up a Windows Dialog box so that I can add the Username and Password which will belong to this Run As Account so that way, I don’t have to show the password in clear text on the PowerShell Console.

SQL Run As Credentials SQLRunAs

 

 

 

 

 

 

 

 

 

 

 

We’ll add in a description of course as its always a good practice to fill in the description fields…and finally, I’m going to set this account to have a More Secure Distribution Type.

Add-SCOMRunAsAccount -Windows -Name “SQL Server Run As” -Description “Used for the SQL Server Management Pack” -RunAsCredential (Get-Credential) | Set-SCOMRunAsDistribution -MoreSecure

SQL Run As More Secure

 

 

 

 

 

 

 

 

Ok, so we now have our Run As Account. So let’s retrieve our new Run As account and pop it into a variable for later.

$sqlRunAsAccount = Get-SCOMRunAsAccount | ? {$_.Name -eq “SQL Server Run As”}

 

Next we’ll fetch our Run As Profile for SQL. Now there are three of them, so we’ll configure the Monitoring one first.

$sqlMonitoringProfile = Get-SCOMRunAsProfile | ? {$_.DisplayName -eq “SQL Server Monitoring Account”}

Now back in the earlier versions of the SQL MP’s, we’d only want to target specific classes, for example the SQL Computers Class. We can get this class by issuing a command like this.

$sqlComputersClass = Get-SCOMClass | ? {$_.Name -eq “Microsoft.SQLServer.ComputerGroup”}

Ok so now let’s put all of this together. We’ll use the Set-SCOMRunAsProfile cmdlet and add to the SQL Monitoring Profile our Run As Account we just created and set it to only target the SQL Computers Class.
Set-SCOMRunAsProfile -Action “Add” -Profile $sqlMonitoringProfile -Account $sqlRunAsAccount -Class $sqlComputersClass

 

SQL Run As Targetting SQL Computer Group

 

 

 

 

 

 

 

 

 

 

 

 

Now let’s do this for the SQL Discovery Profile too…but this time we’ll not set the -Class option and we’ll leave it out altogether. This will result in the account targeting All Targeted Objects.

$sqlDiscoveryProfile = Get-SCOMRunAsProfile | ? {$_.DisplayName -eq “SQL Server Discovery Account”}
Set-SCOMRunAsProfile -Action “Add” -Profile $sqlDiscoveryProfile -Account $sqlRunAsAccount

 

SQL Run As Targetting All Objects

 

 

 

 

 

 

 

 

 

 

 

 

Finally we’ll do this one more time, this time for the SQL Default Profile too…and like before we’ll not set the -Class option and we’ll leave it out altogether therefore targeting All Targeted Objects.

$sqlDefaultProfile = Get-SCOMRunAsProfile | ? {$_.DisplayName -eq “SQL Server Default Action Account”}
Set-SCOMRunAsProfile -Action “Add” -Profile $sqlDefaultProfile -Account $sqlRunAsAccount

So there you have it. It’s unlikely that you’ll choose to use PowerShell like this if you’re simply setting up a single Run As Profile for your own use. But if you’re a consultant that does this regularly…a little PowerShell will go a long way 🙂

Enjoy.

 
Comments

Hey,

Thanks for this post. You are a star!

Haha. Thank you 🙂

We need to distribute the Run-as account to required computer too, right?

Yes that is correct as using this code as it is will mean that the Run As account is set to “More Secure”.

Add-SCOMRunAsAccount -Windows -Name “SQL Server Run As” -Description “Used for the SQL Server Management Pack” -RunAsCredential (Get-Credential) | Set-SCOMRunAsDistribution -MoreSecure (< -- This last part here sets the distribution to More Secure). So in the SCOM Console if you go to Administration --> Run As Configuration –> Accounts and double click on the Run As Account. On the Distribution tab, it will show More Secure. So click “Add” and search for and add those computers in there.

But you could populate this using PowerShell too if you like…something like this.

$sqlrunas = Get-SCOMRunAsAccount | ? {$_.Name -eq “SQL Server Run As Account”}
Set-SCOMRunAsDistribution -RunAsAccount $sqlrunas -SecureDistribution (Get-SCOMAgent | ? {$_.Name -like “SQL*”}) -Security MoreSecure

This will add every server that starts with the name “SQL” into this More Secure Distribution for this Run As Account.

Leave a Reply