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!

Problem

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…

sql02

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.

sql01

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).

http://blogs.technet.com/b/servicemanager/archive/2011/10/04/system-center-data-access-service-start-up-failure-due-to-sql-configuration-change.aspx

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.

Solution

Turned out that the SQL user had no rights to create an instance when executing  the stored procedure: [p_TypeSpaceSetupBrokerService]

Original

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…

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.