System Center Data Access Service Start Up Failure Due to SQL Configuration Change

First published on TECHNET on Oct 04, 2011

One of our Senior Escalation Support Engineers, Richard Usher, has encountered this problem with a few customers now and his written a handy blog post for those of you that may be experiencing this problem. Thanks Richard!

=======================

I’ve seen a couple of instances of this issue recently where following an apparently innocuous change to SQL Server Config, the SDK Service is locked out. So I thought I’d walk through the cause and remedy as well as providing a little background on the problem.

On restart following the change to SQL Server, the SDK Service stops shortly after starting and presents the following three events in the Operations Manager Event Log.

Events

26325 Authorization store exception

26339 Exception thrown while initializing the service container

26380 Unhandled Exception

Each of these events points towards an Authorization issue. Authorization in Service Manager is important as it facilitates the designation of role based access control to the various capabilities of the product.

Authorization Manager

System Center Service Manager 2010 utilises a technology called Authorization Manager (or AzMan as it is commonly known) which is provided by Windows Server. AzMan permits an application to record detailed policy rules in an Authorization store that can be programmatically queried by the application to check access permissions.

One of the new AzMan features of Windows Server 2008 is the provision of SQL-based AzMan stores. In the case of System Center Service Manager 2010 this store is implemented in the form of a series of SQL tables in the ServiceManager database with the dbo.AzMan_ prefix.

Access to the store is available via the MMC Authorization Manager Snap-in, with a slightly tortuous connection string: MSSQL://Driver={SQL Server};Server={MyServerName};/ServiceManager/AzmanStore where MyServerName is the name of the SQL Server hosting the ServiceManager db.

However when attempting to access the store with the server in this state, even access to the store via the SnapIn fails:

SQL Changes

Returning to the “innocuous” changes to SQL Server that coincided with the problem commencing, on each occasion that change was described as “Changing the SQL Server Authentication from Windows to Mixed Mode” This in itself is a non-change, Windows authentication continues to be utilised as part of Mixed-Mode, however the likely motive for changing the Authentication mode is to facilitate further changes. Specifically the reason for changing is to enable the addition of a SQL Server “local” login in addition to the Windows authenticated logins.

Looking at the customer’s example we discovered that in addition to the “innocuous” change of flipping the Authentication mode to Mixed-Mode an additional local Login had been enabled. Checking in SQL Server Management Studio and drilling down to \Security\Logins – we could see that this login had been designated as a db_owner.

It would seem this is a fairly common practice in some Enterprises where SQL Admins choose to add an additional login to facilitate running in-house scripts and jobs.

Solution

Having a non-domain based login as db_owner of an AzMan store prevents access to the AzMan store itself. Flipping Authentication mode back to SQL is insufficient at this stage – the database has this local login defined as a db_owner.

Unless the AzMan store can be accessed the server is effectively down. Restoring to a pre-change state is one answer of course, but all other changes since the backup are also lost. However a simpler resolution is possible. Simply change the role membership for the local Login to something other than db_owner and restart the SDK Service. This time service is restored

Likewise the AzMan store can be accessed via the MMC SnapIn

.

The DataWarehouse in System Center Service Manager 2010 of course is a separate database and implements its own instance of an AzMan store, so if you’re particularly unlucky you may find that your SQL Admin has implemented this same change in each db. In which case then each db must be checked and remedied otherwise you may find SDK Service failures on both your Service Manager server and Data Warehouse Servers.