When I started to review a SCOM 2012 R2 environment recently I came across an interesting issue I didn’t witness before… Time to blog the solution!
The System Center Data Access Service started successfully but stopped within the minute. After investigating I found out that there were at least 2 events logged during the time when the service crashes that could give us a clue on what is going on.
Event 26380: The System Center Data Access Service failed due to an unhandled exception… Cannot be added to the container…
Event 33333: Data access layer rejected: An entity of type service cannot be owned by a role, a group, or by principals mapped to certificates or asymmetric keys.
Strange… This worked the day before. What was going on?
After my search on the web I found this article of Travis Wright who had a similar problem with SCSM (which share the same code base so a nice entry point to start my troubleshoot).
By now I could pinpoint that there was an issue on the SQL side.
After heading over to the SQL admin with the article we continued our troubleshoot together. Turned out that the issue was not exact what Travis had experienced. In fact the SQL admin had made a review of the SA accounts and removed the SA role from the scom SDK user. No problem so far… But the SDK user was not defined in SQL as a SQL user but just as a member of a group.
Turned out that the SQL user had no rights to create an instance when executing the stored procedure: [p_TypeSpaceSetupBrokerService]
SET @Query = N’CREATE SERVICE [‘ + @ServiceName + N’] ON QUEUE [‘ + @QueueName + N’] ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);';
This was changed by the followin stored procedure to authorize the DBO to execute and after that the issue was resolved.
SET @Query = N’CREATE SERVICE [‘ + @ServiceName + N’] AUTHORIZATION [dbo] ON QUEUE [‘ + @QueueName + N’] ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);';
Hopefully when you have stumbled on this page it has saved you some extra troubleshooting…