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