The System Center Data Access Service may not start when SQL Server is used as the backing store for Authorization Manager

Applies to: Windows 7 Service Pack 1

Symptoms


Consider the following scenario. Microsoft System Center Operations Manager (SCOM) or Microsoft System Center Service Manager is deployed. Microsoft SQL Server is used as the backing store for Authorization Manager (AzMan). In this scenario, the System Center Data Access Service may not start. Additionally, you receive the following event messages:

Event 26325



Event 26339



Event 26380



To learn more about this problem and these event messages in the System Center Service Manager Engineering Blog, see System Center Data Access Service Start Up Failure Due to SQL Configuration Change.

Cause


Each of these event messages points toward an Authorization issue. Authorization in Service Manager is important because it helps the designation of role-based access control to the various capabilities of the product.

If you use local (non-Windows) accounts on the instance of SQL Server, and if the local account must be able to have the db_owner role for the database that contains the AzMan tables, AzMan at some point tries to enumerate all users who have the db_owner role. Then, it assumes that each item of the enumeration will have an associated SID. (This is not the case for local SQL accounts.) This, then causes the System Center Data Access Service not to start and results in an error status of ERROR_INVALID_SID.

Resolution


To resolve this problem, run the following script. This script changes the AzMan stored procedures so that local (non-Windows) accounts are not enumerated when the users in the db_owner role are enumerated. In this case, the local SQL accounts that don’t have a valid SID associated with them are skipped.

You have to run this script against the SQL Server database that contains the AzMan tables for System Center Operations Manager.

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'AzMan_Sp_Help_Role_Member' and type = 'P')
DROP PROCEDURE AzMan_Sp_Help_Role_Member
GO
CREATE PROCEDURE AzMan_Sp_Help_Role_Member
(
@RoleName sysname = NULL
)
AS
begin
-- RESULT SET FOR SINGLE ROLE
select DbRole = @RoleName, MemberName = name, MemberSID = sid from sys.database_principals
where type = 'U'
and principal_id in
(
select member_principal_id
from sys.database_role_members drm
inner join sys.database_principals dp
on drm.role_principal_id = dp.principal_id
where dp.name = @RoleName
)
order by 1, 2, 3

end
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'AzMan_SPS_Get_DBOwners' and type = 'P')
DROP PROCEDURE AzMan_SPS_Get_DBOwners
GO
CREATE PROCEDURE AzMan_SPS_Get_DBOwners
(
@Return [int] output,
@ID [int]
)
AS
SET NOCOUNT ON
-- @ID is not used. But our internal implementation requires non-empty
-- parameter set where @Return is not considered one of them.

EXEC @Return = AzMan_Sp_Help_Role_Member 'db_owner'
SET NOCOUNT OFF

Return @Return
GO