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

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.logCcmexec.logMp_Getauth.logMp_Policy.logIn some cases, the following error message is logged in the Mp_Getauth.log:

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.

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:

1. Open SQL Server Enterprise Manager.
2. 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.
3. Right-click SMS_SiteSystemToSQLConnection_sitecode in the details pane, and then click delete.
4. Expand Security, right-click Logins, and then click New Login.
5. On the General tab, type SQLServer\SMS_SiteSystemToSQLConnection_sitecode in the Name box, and then click the Database Access tab.
6. Click to select the Permit check box for the SMS_site code database, and then click to select the public database role check box.
7. Click OK
8. In the SQL Server Enterprise Manager view pane, click SMS DatabaseUsers.
9. Right-click the SMS_SiteSystemToSQLConnection_sitecode user in the details pane, click Properties, and then click Permissions.
10. In the Object list, click to select the appropriate check box for each permission in the following table.
    Collapse this tableExpand this 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

11. Click OK two times to close the User Properties.

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

1. Open the SMS Administrator console
2. Navigate to the secondary site that is the proxy management point.
3. Click Site Systems, right-click the proxy management point server, and then click Properties.
4. On the Management Point tab, change the Database drop-down list box from Use Parent Database to Use a Different Database.
5. 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 (http://www.microsoft.com/downloads/details.aspx?familyid=3d81b520-a203-4376-a72d-fd34a6c4a44c&DisplayLang=en)
6. Click OK, and then close the SMS Administrator console.
7. Restart the SMS Executive service on the secondary site server.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.