The proxy management points of the secondary site in Systems Management Server 2003 cannot access SQL Server after you move SQL Server to new hardware (916904)

SYMPTOMS

After you move the Microsoft Systems Management Server (SMS) 2003 central primary site database to new hardware, the secondary site cannot access SQL Server. This behavior occurs because the secondary site proxy management points cannot connect to the primary site database.

Additionally, the following error messages are logged on the log files in the secondary sites.

Mpcontrol.log

Http verification .sms_aut failed with no header recieved
Failed to receive http response. Error 12152
Http verification .sms_aut failed with no header recieved

Ccmexec.log

Request failed: 401 Access Denied
Request failed: 401 Access Denied
Could not load logging configuration for component PolicyAgent_ReplyAssignments. Using default values.

Mp_Getauth.log

CMPDBConnection::Init(): IDBInitialize::Initialize() failed with 0x80040e4d
CMPDBConnection::Init(): IDBInitialize::Initialize() failed with 0x80004005
CMPDBConnection::Init(): IDBInitialize::Initialize() failed with 0x80040e4d
CMPDBConnection::Init(): IDBInitialize::Initialize() failed with 0x80040e4d

Mp_Policy.log

CPolicyManagerHandler::HandleMessage(): SetComplete(DISCARD) called.
CMPDBConnection::Init(): IDBInitialize::Initialize() failed with 0x80040e4d
CPolicyManagerHandler::HandleMessage(): SetComplete(DISCARD) called.
CMPDBConnection::Init(): IDBInitialize::Initialize() failed with 0x80004005
CPolicyManagerHandler::HandleMessage(): SetComplete(DISCARD) called.

In some cases, the following error message is logged in the Mp_Getauth.log:

CMPDBConnection::ExecuteSQL(): ICommandText::Execute() failed with 0x80040E09

CAUSE

This problem occurs because the security identifier (SID) of the SMS_SitetoSQLConnection group is changed during the restore process of the SQL Server database. However, the SID of the SMS_SitetoSQLConnection group user in the database is not changed. SQL Server does not allow for authentication of the Site System Database account, the SMS_SQL_RX_sitecode or the alternate user account in Standard security, or the site system computer account in Advanced security.

RESOLUTION

To resolve this problem, remove and then add the SMS_SiteSystemtoSQLConnection_sitecode group in the SQL Server Enterprise Manager logins. To do this, follow these steps:

Open SQL Server Enterprise Manager.
In the SQL Enterprise Manager snap-in, expand Microsoft SQL Servers, expand SQL Server Group, expand ServerName, expand Databases, expand SMS Database, and then click Users.
Right-click SMS_SiteSystemToSQLConnection_sitecode in the details pane, and then click delete.
Expand Security, right-click Logins, and then click New Login.
On the General tab, type SQLServer\SMS_SiteSystemToSQLConnection_sitecode in the Name box, and then click the Database Access tab.
Click to select the Permit check box for the SMS_site code database, and then click to select the public database role check box.
Click OK
In the SQL Server Enterprise Manager view pane, click SMS DatabaseUsers.
Right-click the SMS_SiteSystemToSQLConnection_sitecode user in the details pane, click Properties, and then click Permissions.

In the Object list, click to select the appropriate check box for each permission in the following table.

Table	Permission
DMP_GetDiscoveryTranslation	EXEC
DMP_GetFqdns	EXEC
DMP_GetHinvTranslations	EXEC
DMP_GetMachinePolicies	EXEC
DMP_GetPackageVersion	EXEC
DMP_GetSettings	EXEC
DMP_GetSoftwareDistBody	EXEC
DMP_GetSoftwareDistIDs	EXEC
MP_GetAllInventoryClassses	EXEC
MP_GetContentDPInfoProtected	EXEC
MP_GetContentDPInfoUnprotected	EXEC
MP_GetHINVLastUpdateTime	EXEC
MP_GetInventoryClassProperties	EXEC
MP_GetListOfMPsInSite	EXEC
MP_GetLocalSitesFromAssignedSite	EXEC
MP_GetMPListForSite	EXEC
MP_GetMPSitesFromAssignedSite	EXEC
MP_GetMachinePolicyAssignments	EXEC
MP_GetPolicyBody	EXEC
MP_GetSiteInfoFromADSite	EXEC
MP_GetSiteInfoFromIPAddress	EXEC
MP_GetUserAndUserGroupPolicyAssignments	EXEC
RoamingBoundaryADSite	SELECT
RoamingBoundaryIPRange	SELECT
RoamingBoundaryIPSubnet	SELECT
SiteBoundaryADSite	SELECT
SiteBoundaryIPSubnet	SELECT
Sites	SELECT
SysResList	SELECT
Sp_GetPublicKeySMSUID	EXEC

Click OK two times to close the User Properties.

WORKAROUND

To work around this problem, use one of the following methods.

Note Use these methods as temporary solutions because both methods work around the SitetoSQLConnection group.

Method 1

Add the Site System Database account to the local administrators group of the parent site.

Method 2

Open the SMS Administrator console
Navigate to the secondary site that is the proxy management point.
Click Site Systems, right-click the proxy management point server, and then click Properties.
On the Management Point tab, change the Database drop-down list box from Use Parent Database to Use a Different Database.
Type the applicable database server name, the database name, and then the authentication information.

Note This can be either SQL Server or Windows authentication. We recommend that you use Windows authentication as the best practice. For more information, see the "SMS 2003 Security Best Practices" section in the Scenarios and Procedures for Microsoft Systems Management Server 2003: Security white paper. To view this white paper, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?familyid=3d81b520-a203-4376-a72d-fd34a6c4a44c&DisplayLang=en
Click OK, and then close the SMS Administrator console.
Restart the SMS Executive service on the secondary site server.