Fix: Unable to connect CAS or Primary to the SQL database during the System Center 2012 Configuration Manager setup

Fix: Unable to connect CAS or Primary to the SQL database during the System Center 2012 Configuration Manager setup

  • Comments 3
  • Likes

Toolbox3Hi everyone, Dennis Donahoe here with a couple tips on how to fix an issue you may see setting up a new install of Configuration Manager. In a new installation of System Center 2012 Configuration Manager (ConfigMgr), you may find that you cannot connect to SQL when installing with a remote SQL 2008 R2 SP1 named instance. If this happens, some of the issues that you should check are listed below. They may not all completely prevent communication with SQL but they are all base requirements or strong recommendations for proper installation and should be checked during troubleshooting.

1. Check the SQL Server Configuration Manager named instance network configuration.

a. Open SQL server 2008 Configuration Manager  in the start menu

b. Select SQL server Network Configuration

c. Select the instance the database is expected to use

d. In the right pane right click on TCP/IP and select Properties

clip_image001

If the named instance is using dynamic ports then the port shown will be blank.   Using dynamic ports is not supported in System Center 2012 Configuration Manager as it requires a fixed port.   You can manually select a port or you can check the SQL error log and see what port it is currently using and set that as the port if no other application databases are in that instance.

2. The SQL Server Service Broker must be enabled for the Configuration Manager database.

clip_image002

The image above shows the Service Broker disabled (Broker Enabled set to False).   This needs to be set to True on the ConfigMgr database only. To do this, right-click on the database and select Properties –> Options (SQL 2008 R2) or run Select * from sys.databases to see service broker status (1=enabled).

3. Microsoft recommends using a domain account for the SQL server service account. With a Domain account as a service account, we must insure that SPN's are configured for the service account for Netbiosname:port and fqdn:port. Both Netbios and FQDN must be configured.

4. Check to see if any other Configuration Manager databases are installed on this instance in SQL. Having another System Center Configuration Manager 2007 database in the same instance is not recommended and having another System Center 2012 Configuration Manager instance is unsupported.

5. Verify SQL has the correct Cumulative Update. SQL Server 2008 R2 SP1 Requires CU4.  Information for all Supported SQL versions and their required updates are contained in the links below:

How to determine the version and edition of SQL Server and its components : http://support.microsoft.com/kb/321185

Configurations for the SQL Server Site Database : http://technet.microsoft.com/en-us/library/gg682077.aspx#BKMK_SupConfigSQLDBconfig

Supported Configurations for Configuration Manager : http://technet.microsoft.com/en-us/library/gg682077.aspx

Dennis Donahoe | Senior Support Escalation Engineer

Get the latest System Center news on Facebook and Twitter:

clip_image001 clip_image002

App-V Team blog: http://blogs.technet.com/appv/
ConfigMgr Support Team blog: http://blogs.technet.com/configurationmgr/
DPM Team blog: http://blogs.technet.com/dpm/
MED-V Team blog: http://blogs.technet.com/medv/
Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/
Operations Manager Team blog: http://blogs.technet.com/momteam/
SCVMM Team blog: http://blogs.technet.com/scvmm
Server App-V Team blog: http://blogs.technet.com/b/serverappv
Service Manager Team blog: http://blogs.technet.com/b/servicemanager
System Center Essentials Team blog: http://blogs.technet.com/b/systemcenteressentials
WSUS Support Team blog: http://blogs.technet.com/sus/

The Forefront Server Protection blog: http://blogs.technet.com/b/fss/
The Forefront Endpoint Security blog : http://blogs.technet.com/b/clientsecurity/
The Forefront Identity Manager blog : http://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog: http://blogs.technet.com/b/isablog/
The Forefront UAG blog: http://blogs.technet.com/b/edgeaccessblog/

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • The supported configurations have been updated recently. It's highly recommended to use CU6 for SQL Server 2008 R2 SP1 (not CU4). See technet.microsoft.com/.../gg682077.aspx

  • I would like to add that the SQL server instance's configuration for "TCP Dynamic Ports" should be set to static for ALL IPs, especially for "IPAll", too.

  • Thanks! Like Klaus said, you'll need to set this for ALL ip's including the "IPAll". It will not work if you don't set this right.