Tag Archives: MimboloveSecondary Site

Installing SCCM 2012 SP1 Secondary Site with a Pre-Configured SQL 2012 Instance

Written by Tao Yang

Over the last week, I’ve been re-installing my SCCM lab environment to SCCM 2012 SP1. I’m using Windows Server 2012 as the base OS for all site system roles and all database engines and SQL reporting server run on SQL 2012.

I got stuck few days ago when I was building my first secondary site. I was trying to use a pre-installed SQL 2012 Express With SP1 instance for the secondary site database. I followed the instruction that I have previously blogged for SQL Express 2008 R2: http://blog.tyang.org/2012/04/09/installing-sccm-2012-rtm-secondary-site-using-a-pre-installed-sql-express-2008-r2-instance/

After I installed and configured the SQL express instance for the secondary site, I started the secondary site install (from the parent primary site). However, I was keep getting this error during the prerequisites check:

SQL server sysadmin rights:

Either the user account running Configuration Manager Setup does not have sysadmin SQL Server role permission on the SQL Server instance selected for site database installation, or the SQL Server instance could not be contacted to verify permissions. Setup cannot continue.

Prerequisite check result:

clip_image001

ConfigMgrPrereq.log:

clip_image001[6]

The error suggested that my account does not have sysadmin rights. In fact, both my user account and the site server computer account have sysadmin and dbcreator rights in that SQL 2012 instance.

I then tried few different SQL configurations, including using default instance rather than named instance (CONFIGMGRSEC), and using SQL 2012 Enterprise rather than Express edition, they made no difference. I then installed SQL 2008 R2 Express With SP2 (with exact same configuration in terms of security, collation, using named instance, enabling SQL Server Browser service, etc). and the pre-requisite checks passed and secondary site got successfully installed.

After I compared settings in SQL 2008 R2 and the SQL 2012 Express instance I had installed on another secondary site server, I found the issue:

During SQL 2012 install, the sysadmin rights was not granted to the local system account (NT AUTHORITY\SYSTEM). In SQL 2008 R2, “NT AUTHORITY\SYSTEM” account by default has sysadmin rights. During the prerequisites check, SCCM installs a series of services on the target secondary site server to perform the checks. these services are installed to run under LOCALSYSTEM account. The SQL sysadmin rights check failed because the LOCALSYSTEM account does not have sysadmin rights as it was running under LOCALSYSTEM account. To a degree, the error message is somewhat misleading in my opinion.

i.e. system event log entry for one of the services installed by prerequisites check:

image

So to fix the issue, I simply gave “NT AUTHORITY\SYSTEM” account the same access in SQL 2012 as in SQL 2008 R2:

sysadmin and securityadmin role:

clip_image001[8]

To summarise, when installing SCCM 2012 SP1 secondary site on a pre-configured SQL 2012 instance regardless which SQL edition is being used, “NT AUTHORITY\SYSTEM” account needs to be given securityadmin and sysadmin rights. If SQL Express is used, there are few additional steps need to be carried out to configure the SQL TCP connection as documented in my previous blog: http://blog.tyang.org/2012/04/09/installing-sccm-2012-rtm-secondary-site-using-a-pre-installed-sql-express-2008-r2-instance/

Installing SCCM 2012 RTM Secondary Site using A Pre-Installed SQL Express 2008 R2 Instance

Written by Tao Yang

Since System Center 2012 was RTM’d few days ago, I have started updating / migrating my home environment. After I migrated my 2 Hyper-V servers from VMM 2008 R2 to VMM 2012, I have started building a brand new SCCM 2012 environment so I can migrate SCCM 2007 to it. My plan is to install a Central Admin site, a child primary site and a Secondary site so I have a simple 3-tier hierarchy like my existing 2007 and 2012 Beta 2 environments.

The Central Admin site and the child primary site installation all went pretty smoothly. But I had some issues when installing the secondary site.

When installing Secondary Site from it’s parent primary, There are two options available for the database:

  1. Install and Configure a local copy of SQL Server Express on the secondary site computer
  2. Use an existing SQL Server instance.

I wanted to install SQL Express myself so I can control where it’s installed to and locations for data, log and backup files. – This is pretty common and most of SQL DBAs would configure to install SQL on a volume other than C:\ and place data / logs / backups on dedicated and separate disks. By using SCCM to install SQL express for you, you don’t get to choose any of this, which can be pretty annoying.

According to Supported Configurations for Configuration Manager, secondary sites supports SQL Server Express 2008 R2 with SP1 and Cumulative Update 4. So I downloaded SQL Server 2008 R2 Express With SP1 with Tools (SQLEXPRWT_x64_ENU.exe) and SQL 2008 R2 Service Pack 1 Cumulative Update 4 and installed them in order on my secondary site site server.

Below is what I have customised during the SQL express install:

  • I configured the location for SQL, SQL instance, data files, log files and backup files the way I wanted it.
  • I selected the SQL instance to use the collation “SQL_Latin1_General_CP1_CI_AS because it is the only collation that SCCM supports.
  • I kept the default secondary site SQL instance name “CONFIGMGRSEC” (this name is what’s used if you choose SCCM to install SQL Express for you).
  • I have given a pre-configured AD group called “ConfigMgr2012 Servers” which contains all SCCM 2012 site servers sysadmin rights in SQL Express.

After the install, I applied CU4 and all went pretty smoothly.

Now, I tried to push Secondary Site install from the primary site. Under SQL Server setting step, I selected “Use an existing SQL Server instance” option and enter the secondary site server’s FQDN under “SQL server fully qualified domain name” and “CONFIGMGRSEC” under “SQL server instance name, if applicable”. After finishing the wizard, the secondary site install failed during prerequisite checks. I got few errors in regards to the SQL collation is not set to SQL_Latin1_General_CP1_CI-AS:

image

This is very strange because all my SQL instances in this hierarchy are set to this collation, and because of this, the setup did not even get kicked off.

Additionally, I also found the following:

  • On the primary site server, in the ConfigMgrSetup.log under System root, I get the following errors:
    • CSql Error: Cannot find type data, cannot get a connection.
    • *** [08001][17][Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
    • I could use the SQL management studio from Secondary site server to connect to the SQL express instance, but I couldn’t use the SQL management studio from a remote machine to connect to it:

image

After spending some time troubleshooting, I got it going. Below is what I have done on the SQL Express instance:

1. I’ve assign “ConfigMgr2012 Servers” group (which I created myself and it contains the primary site server’s computer account) “dbcreator” role on top of sysadmin role it already had.

image

2. I realised by default, after I installed SQL express, TCP/IP protocol is disabled. So I went to SQL Server Configuration Manager, under SQL Server Network Connection —> Protocols for CONFIGMGRSEC—>TCP/IP, enabled it. I also had to configure the ports for this connection:

I removed 0 from “TCP Dynamic Ports” for each IP and added static port 1433 under “TCP Port”

image

After you enabled TCP/IP and changed the port, you will be prompted that you have to restart SQL server service for the change to take effect, so I restarted the SQL service.

After these steps, the prerequisite checks were passed and the Secondary site installation finished successfully.

In summary below are the steps I took to pre-configure a SQL Express instance for SCCM 2012 secondary site:

  1. Install SQL Express 2008 R2 with SP1 with Tools
  2. Configure SQL express install directory as per my standard (not on C:\ drive)
  3. Configure SQL Express instance name as “CONFIGMGRSEC” as it is default to SCCM secondary site and there’s no reason to change it.
  4. Select “SQL_Latin1_General_CP1_CI_AS” as SQL server collation.
  5. Configure data/logs/backups directory
  6. add primary site server’s computer account (or a group containing primary site server’s computer account) as administrator during install
  7. Apply SQL Server 2008 R2 Service Pack 1 Cumulative Update 4 after SQL Express install
  8. Set a limit for amount of memory SQL express can use.
  9. Reboot secondary site server (just to be safe)
  10. give the parent primary site server’s computer account dbcreator access in SQL Express instance.
  11. Enable TCP/IP for the SQL express instance.
  12. Configure TCP/IP connection port settings.
  13. Restart SQL service.
  14. Initiate Secondary Site install from Primary site (via SCCM console). – Unlike SCCM 2007, secondary site install can no longer be performed by running SCCM setup from secondary site servers.
  15. During setup wizard, choose “Use an existing SQL Server instance”, enter secondary site server’s FQDN and SQL instance name (“CONFIGMGRSEC”). leave site database name and SQL broker port as default.
  16. monitor install status using the SCCM console:

image

image

You can also check:

  • C:\ConfigMgrSetup.log on Primary Site server (contains details for Secondary Site install’s prerequisite checks).
  • C:\ConfigMgrSetup.log on Secondary Site server (contains details for the actual setup).

Now, instead of having SQL Express installed and configured by SCCM, I have more control of it so I can align the configuration with my organisation’s standard (if it’s in a real production environment Smile).

In this case, I have my SQL data file located under F:\SQL_Data\Microsoft SQL Server\MSSQL10_50.CONFIGMGRSEC\MSSQL\DATA:

image

And log files under G:\SQL_Logs\Microsoft SQL Server\MSSQL10_50.CONFIGMGRSEC\MSSQL\Data:

image