Category Archives: System Center

Installation of SMA Runbook Worker fails (Unable to communicate with SQL Server)

When installing a new SMA (Service Management Automation) runbook worker or web service it might fail with the following error message in the log:
“Product: System Center 2012 R2 Service Management Automation Runbook Worker — Unable to communicate with SQL Server using database information provided.”

If you are doing a manual installation using the wizard it will look like this:
sma_installation_failed

Not sure if this matters, but in my case, the database is hosted in a SQL AlwaysOn Availability Group on a non-default port (not 1433), and we are using “Windows Authentication”, or a “trusted” connection to log into the database.

After investigating this issue and looking at the network communication I realized that the installation actually tries to validate the connection on the database-settings page, but when it’s finally time to start the installation, it just fails right away. Also, I found that the connection at the “verify sql settings”-step is established via a service (svchost.exe or CcmExec.exe), which could explain why this workaround actually works (it’s probably using the same component in the OS).

I finally found a workaround for this issue though, which is pretty weird, but it got me through the installations of all my runbook workers and web services so I thought I’d share it if anyone else is experiencing this issue.

Workaround using temporary ODBC-connection
We will not actually create the connection, just fill in enough information to be able to do a test.

Fill in all the settings in the SMA Runbook Worker-wizard but do not click “Install” at the last page.

Instead, start the “ODBC Data Sources (64-bit)” (%windir%\system32\odbcad32.exe) using the same account as your installation wizard is running with and click “Add…”, see below:
odbc1

Then click “Finish”:
odbc2

Fill in the details of your database for SMA (the first two fields can be anything):
odbc3

Fill in the name of your sql server, click next, and choose “Client Configuration” if you are using a non-default port and fill in the one you are using:
odbc4

Click next, and choose to change default database to master (not 100% sure this is needed, but a thread @technet suggested this), like this:
odbc5

Press “Finish” at the next step, but instead of pressing “OK” you choose “Test Data Source…” and you should see a successful test:
odbc6

Immediately switch back to your SMA Runbook Worker wizard and press Install, it should now go through fine!

When the installation has finished, go back to your “ODBC connection test” and choose OK, then Cancel three times to exit the wizard for creating a ODBC-connection without actually creating it.

I hope this helps someone else!

Automate SCOM Gateway Certificate Renewal

When deploying SCOM (System Center Operations Manager) in a multi-forest environment, you use certificates to establish the trust between the servers. Since we have CA Servers in every domain, we started up with configuring autoenrollment for all the SCOM Gateway Servers, and made sure all the different CA servers were added to the trust-store of the central servers. (I will not go through that process now, if you want me to, leave a comment).

So autoenrollment now works, but that isn’t really enough, is it? We still need to configure the Gateway Server to actually switch to the new certificate when it arrives.

The tool Microsoft has given to us to do this is MOMCertImport.exe, but that tool gives you a pop-up that you actually need to click on… Not very “automatable”.

After some research, we could find that all this tool seems to do is to add the certificates serial number, backwards (in pairs), as a binary key in the registry. That is very automatable! 🙂

Before you start, you should know that this method is probably NOT supported by Microsoft, on the other hand, if it fails, you could run MOMCertImport.exe and see if that helps…

A code walkthrough follows:

Let’s start with setting up some user controlled variables, like what Certificate Template is used and where the registry key is located:

# Specify SCOM Template name
$SCOMTemplateName="SCOM Template"

# Specify SCOM Certificate Registry Key Path
$SCOMCertRegPath="HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Machine Settings"

# Specify SCOM Certificate Registry Value Name
$SCOMCertRegValueName="ChannelCertificateSerialNumber"

We then need a way of going through the certificates on the server to see if a new certificate has arrived:

# Initialize new array
$ParsedCertificates=@()

# List all local certificates
$LocalCertificates=Get-ChildItem Cert:\LocalMachine\My

# Go through the certificate and parse them to get the certificate template information out
foreach ($LocalCertificate in $LocalCertificates) {

$ParsedCertificates+= $LocalCertificate | Select `
Friendlyname,
Thumbprint,
SerialNumber,
NotAfter,
NotBefore,
@{Name="Template";Expression={($_.Extensions |
Where-Object {$_.oid.Friendlyname -match "Certificate Template Information"}).Format(0) -replace "(.+)?=(.+)\((.+)?", '$2'}},
@{Name="Subject";Expression={$_.SubjectName.name}}
}

As you can see, you need some regex to get the actual Certificate Template name. This should probably be turned into an advanced function! I might put that on a ToDo-list…

Now we have all the information we need to check if a new SCOM Gateway certificate has arrived.

I thought the easiest way of doing that was by getting the serial number of the latest certificate from that template, like this:

# Load the serial number of the newest SCOM Certificate into a new variable
$SerialNumber=($ParsedCertificates | Where-Object { $_.Template -eq $SCOMTemplateName } | Sort-Object NotAfter -Descending | select -First 1).SerialNumber

It’s now time for some regex-magic again, we want to pair this number up (2 and 2), and then reverse those pairs. I must confess I did a couple of rewrites of this before I found one that seems quite effective:

# Reverse the serial number to match the format in the registry
$ReversedPairs=[regex]::Matches($SerialNumber,'..','RightToLeft') | ForEach-Object { $_.Value }

The two dots (‘..’) tells powershell to group them, and the ‘RightToLeft’ reverses them. The last foreach is to get only the values and nothing else.
But it needed to be in binary format aswell, we achieve that by doing this:

# Convert string to binary
$ReversedPairsInBinary=$ReversedPairs | ForEach-Object { [convert]::ToByte($_,16) }

We now have something that we can compare with the current registry value, so let’s load the current one:

# Load current serial number into variable
$CurrentSCOMCertificate=Get-ItemProperty -Path $SCOMCertRegPath | Select-Object $SCOMCertRegValueName -ExpandProperty $SCOMCertRegValueName

And now let’s join the arrays and compare them, and based on the results update the registry if needed and restart the SCOM Gateway Service.

# Check if we have a new certificate
if (($ReversedPairsInBinary -join "") -eq ($CurrentSCOMCertificate -join "")) {
Write-Output "The current certificate is the latest."
}
else {
Write-Output "New certificate found. Changing registry..."
# Write to registry key
New-ItemProperty -Path $SCOMCertRegPath -Name $SCOMCertRegValueName -Value $ReversedPairsInBinary -Type Binary -Force

Write-Output "Restarting health service..."
# Restart the Health Service
Restart-Service -Name HealthService -Force
}

And we are done!

The complete and uncut code for this script is available here.

Good luck! 🙂