Service Broker External Activator – Be Prepared

In building a solution for a client there was a need to react to any changes to the structure of the database. My client is a software company, and their database is implemented at their customer sites, and those customers are allowed to make changes and additions to the database, specific to their businesses. The solution I’m building needs to react to these changes as well as changes sent out by my client. I looked at DDL triggers, but I found that Event Notifications meets the needs of my solution rather well.

Event Notifications, like the solution I developed, uses SQL Server Service Broker, an asynchronous messaging technology first introduced in SQL Server 2005. One of the features introduced around the time SQL Server 2008 came out was the External Activator, a service that notifies an external (to SQL Server) process that a message has been sent, and can be processed.

Since my solution has to evaluate the metadata for the changes to the DDL, and then update objects on multiple servers, I wrote a PowerShell script to shred the XML sent from the DDL event, parse the object using SMO, build new process objects for handling those changes, and then implement them on the respective servers. External Activation cause that PowerShell script to run automatically when changes are made.

My test environment is running SQL Server 2017 on Windows Server 2016, a pretty vanilla environment. After downloading the appropriate installer for the server where the service was to run, I installed it, made the necessary changes to the config file per the documentation provided after installation, assigned the service account with the necessary privileges, and attempted to start the service.

In the Windows System error log, I got three messages.

1. The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
   {D63B10C5-BB46-4990-A94F-E40B9D520160}
   and APPID
   {9CA88EE3-ACB7-47C8-AFC4-AB702511C276}
   to the user SQLTBWS\Administrator SID (S-1-5-21-1206419648-957611843-3521861610-500) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.
2. A timeout was reached (30000 milliseconds) while waiting for the Service Broker External Activator service to connect.
3. The Service Broker External Activator service failed to start due to the following error:
   The service did not respond to the start or control request in a timely fashion.

I searched in Component Manager under the DCOM apps for the APPID 9CA88EE3-ACB7-47C8-AFC4-AB702511C276 and came up with nothing.

After a lot of searching, but not a lot of finding, for any solution, it occurred to me that in earlier versions of SQL Server there was a strong dependency on .NET 3.5, but from SQL Server 2012 forward, the SQL Server installation process does not automatically install .NET 3.5. SQL Server 2017, in fact, no longer has that dependency on .NET 3.5.

But the External Activator does.

So, I tried it, and it worked. I installed the .NET 3.5 using the following PowerShell command (after inserting the Windows Server install media on the D: drive)

Add-WindowsFeature -name NET-Framework-Features -source D:\sources\sxs

Then, after rebooting the server, the service started right up, and runs without fail.

Be prepared.