How to configure configure a SQL Server 2014 SP1 to support a SCCM (System Center Configuration Manager) collocate deployment.
There are several strategies around SQL Server deployment for System Center Configuration Manager (SCCM), in this Tutorial we are going over the basics to deploy SQL on the same server as SCCM, and this kind of deployment is more for simple scenarios of the solution. Since SQL Server requires a lot of planning which includes features to be installed, service accounts and etc, our recommendation is to get acquainted with the process by reading this Tutorial, and then work on the requirements to speed up your installation process.
For starters, I’m not an SQL expert, however I was able to read the SQL and Configuration Manager best practices, Microsoft KBs and Technical articles before writing this Tutorial. Please feel free to drop any comments or suggestions on the comment area and we will update this Tutorial with your points.
Configuration Manager can be installed on a variety of SQL Server versions (2008, 2012 and 2014) and for this Tutorial we are going to install SQL 2014 with Service Pack 1. If you are collocating SQL and SCCM Server then an extra configuration is required after the SQL installation to preserve memory for SCCM and we will cover this on this Tutorial as well.
There are a few key points that I would like to address before we start the SQL deployment, as follows:
- SQL may require different disks for better performance including different type of RAIDs
- Backup should be considered after the installation process, you can use SCDPM (System Center Data Protection Manager) to protect SQL workloads
- If using Antivirus, make sure that you AV has exceptions for SQL and Configuration Manager before deploying the product
Before installing SQL Server we need to make sure that we have .NET Framework 3.5.1 installed, and we can accomplish that using the Add Roles and Features Wizard as shown in the image below.
We will need the Operating System media to complete that installation, click on Specify an alternate source path, and on the new page add the location of the installation media X:\Sources\sxs (X: is the DVD drive on this example).
Wait for the completion of the .NET Framework 3.5, and then restart the computer.
Before moving to the next section, run a Windows Update if you haven’t done so, and make sure that the server is up to date.
SQL Server Installation..
The SQL Server installation requires a lot of steps however it is not rocket science. We will focus on the portion of the configuration that the administrator must change something, if it is just default settings, then we will just mention it.
In order to start the SQL installation process, open the ISO file, or insert the media on the drive. The first screen will be the SQL Server Installation Center. Click on Installation and then on New SQL Server stand-alone installation or add features to an existing installation and use default values.
In the Product Key page. Enter your product key, or use Evaluation and click Next.
In the License Terms page. Accept the terms if you agree with them, and click Next.
In the Microsoft Update page. Define if the you are going to use Windows Update to update the server, and click Next.
In the Install Rules page. You should have something similar to the image below (the firewall one we will take an additional step after the deployment of the product). Click Next.
In the Setup Role page. Select SQL Server Feature Installation and click Next.
In the Feature Selection page. Select Database Engine Services, Reporting Services – Native, Management Tools – Basic, and Management Tools – Complete should be the options selected. Click Next.
In the Instance Configuration page. We will define a instance for SCCM, as shown in the image below. Click Next.
In the Server Configuration page. For now we will keep default values, however we will change later on to use either a service account or a Managed Service Account. Click on Collocation tab.
In the Collocation tab. Make sure that the option selected is SQL_Latin1_General_CP1_CI_AS as shown in the image below.
In the Database Engine Configuration. In this page we can add the current user, and the most important is a Global Group that represents all your SQLAdmins of your production environment (if you don’t have it, that is a good time to create one), and the SCCM-Admins group (if you don’t have it, create one as well).
In the Reporting Services and configuration page. Select Install and configure and click Next.
In the Ready to install page. A summary of all definitions that the administrator has done so far will be listed, click on Install to start the installation process.
In the Complete page. A list of all features and the installation status will be displayed, make sure that everything was installed successfully and click on Close to continue the SQL Server configuration.
SQL Service Account(s)…
The best practice is to use Managed Service Account (also known as sMSA) with the minimum privileges for each SQL Service, and that can be used if you already have your Active Directory running at Windows Server 2008 R2 level or higher. There is also gMSA (Group Managed Service Accounts) and those are for farm and cluster of servers. If you are deploying a single server the sMA is your first choice, and the regular service accounts are also doable (a lot of administrator still rely on regular accounts as service accounts).
For this Tutorial we are going to create a regular service account (old school style!), and it will be called svc.sql, and the the image below summarizes the service account configuration.
It seems that service account is a simple topic, but if you want a secure environment there are a few key take away from this section that will help to secure your environment, as follows:
- By default, do not assign any SQL Service account as administrator of the server
- It is better (more secure) to create a service account for each SQL service, instead of a single account
- The SQL Service account should be by server/application, after all we don’t want an issue in one of the service account and by that bringing down several SQL servers on your production environment at the same time. My suggestion is to add the server name on the naming convention, this way is easy to find out any locked accounts in the future
- Service Accounts and their use
- Service Account and their permissions for SQL Server
Even leaving default settings during the SQL Server installation, the administrator can change the Service Accounts to use either a sMSA or a regular service account. Basically, we need to open SQL Server Configuration Manager, and then click on SQL Server Services. Double click on the desired service and change the account, a restart of the service is required to apply the changes.
SQL Memory Configuration
Because we are collocating SQL Server with SCCM, the recommendation is to allocate between 50% to 80% for SQL Server. In order to do that, open SQL Management Studio, right-click on the first item on the left side, and click Properties. In the new window, click on Memory tab, and define the minimum and maximum memory for the server based on the server’s memory.
SQL Firewall Settings…
By default, the Firewall Rules related to the SQL Service are not created on the local Firewall Policies. Open a PowerShell as administrator and run the following cmdlet:
New-NetFirewallRule -Profile Domain -DisplayName “SQL – Inbound Ports” -Direction Inbound -Action Allow -LocalPort 1433,4022 -Protocol TCP
The results can be seen on the image below, where a new Inbound Rule was created to support the existent SQL Server.
SPN Automatic registration..
This configuration relies on your decision with the Service Accounts, using default settings will create the SPN automatically, however that is not the most secure deployment available. Our recommendation (which is based on Microsoft best practices) is to use service accounts (either manual or Managed are better than built-in accounts). Using a service account requires that specific account to have a special permission to register SPN, and SQL performs the SPN registration and deregistration during the SQL Server service star/stop process.
In order to allow a service account to be able to manage SPNs, requires opening ADSIEdit.msc, then find the account on the Active Directory structure and get Properties. In our example below is svc.sql account.
Click on Security tab, and then Advanced. In the new page, click on Add.
In the new page, select SELF, Allow and This object only.
In the section at the bottom, select only Read servicePrincipalName and Write servicePrincipalName and click OK on all windows.
In order to test if the account has the proper permission, wait for the Active Directory replication (depending of your environment it may take a couple of minutes when all servers are on the same site, and hours if we have servers in different locations). Open the Command Prompt as administrator and run the following command:
dsacls “CN=serviceaccount,OU=OUName,DC=domain,DC=ca” | findstr “service principal”
If there is an entry similar to the one listed below we are golden and the SPN registration will succeed.
In order to test the permission we can run the following command using either command prompt or PowerShell:
setspn –L <account-Name>
Before assigning the permission that was the result:
After assigning the permissions, we got this result. That should be the output on your environment as well.
If you want to dig deeper on the SPN registration, and see if that is the right fit for your environment, we listed a couple of important Microsoft documentation, as follows:
- Register a Service Principal Name for Kerberos Connections
- How to use Kerberos authentication in SQL Server