MP to SQL Connectivity

  • Article

A common issue PSS has seen for SMS 2003 is a failure of one or more Servers to connect and authenticate to the SQL Server and Database.

 

There are varied causes for this problem not limited to the following common sources as detailed in KB 832109:

·         The Management Point does not have correct permissions on the SQL server

·         Problems exist with SQL Service Principal Name (SPN) registration.

·         Problems exist with Kerberos or the Domain Name System (DNS) protocol

A common workaround to these has been the configuration of a Named Pipes connection. This is quick and easy but may hide the underlying cause which makes its use necessary. I encourage you to investigate the trigger of SPN or DNS related issues sooner rather than later as while SMS is affected today, if there is a common trigger which has not been addressed it may have a larger impact beyond SMS to your general infrastructure tomorrow.

 

Other Causes

In addition to the checks per KB 832109 other sources of connection problems may involve missing permissions or members for the SMS_SiteSystemToSQLConnection_sitecodegroup .

·         Confirm that the problem server is a member of the group. The absence of expected servers from this group is common. Often it is the Proxy MP which has not been added. The following is taken from Appendix C of the Scenarios and Procedures for Microsoft Systems Management Server 2003: Security posted on TechNet which comments on a likely cause:

o   In advanced security, a secondary site machine account must have security rights to add the proxy management point machine account to the SMS_SiteSystemToSQLConnection_<sitecode> group. Once Site Component Manager on the secondary site fails to add this account to the group it will never attempt to add it again and the management point machine account will have to be added to this group manually. Failure to do so results in the proxy management point failing to deliver policy to the Advanced Clients.

·         Ensure the connection group has necessary access. It is not uncommon that in the process of SQL hardening or through the use of general Best Practice guidelines specific requirements of SMS accounts (and possibly other products) will be negatively impacted by those configurations.

o   You can find more information on this from KB 918911

o   Further information about SMS Database Accounts and Roles can be found in the Systems Management Server 2003 Concepts, Planning and Deployment Guide on TechNet.

·         Table C.5 from Appendix C of the Scenarios and Procedures – SMS Security White Paper provides more details on ensuring MP, SLP and RP’ systems both local and remote have the necessary access to SQL.

Please give this information a try to get that connectivity restored – but don’t forget to investigate the underlying cause.

 

Brent Dunsire

Supportability Program Manager - Systems Management Server