Hello, my name is Chris Vetter and I am a Premier Field Engineer with Microsoft. I am here to discuss Installing SQL Server 2016 for Configuration Manager in a PubSec Environment. The intent of this guide is to install SQL 2016 to the guidelines provided by the quarterly DISA (Defense Information Systems Agency) STIGS (Security Technical Implementation Guides). While this guide will not cover all of the STIGS, it will cover a select few that will prevent you from having to perform a Site Recovery or any Database Migrations after standing up your environment and getting surprised by an inspection that tells you are not STIG compliant. The STIG’s I will reference in guide are from January of 2020.
Prerequisites (Pre-Installation)
When provisioning your server for install of SQL 2016, you will need to provision additional disk volumes if on a physical server or add additional virtual disks if on a VM. This is to comply with STIG Vul ID: V-79169 which is a CAT II severity the rule is that “Database software, including DBMS configuration files must be stored in dedicated directories, separate from the host OS and other applications”. We will need separate drives for not only Configuration Manager but for SQL as well. In this guide I will use a separate drive not only for my SQL DB but I also host my temp DB and my logs on separate drives as seen in the screen shot below.
For the next STIG, we will need to perform some prework. Vul ID: V-79245 which states, “SQL Server services must be configured to run under unique dedicated user accounts”. That means we will need 3 separate service accounts for our SQL Instance one for the SQL Database Service, one for the SQL Server Reporting Services Service if you plan on using reporting with Configuration Manager and one for the SQL Agent service which will be used to run SQL jobs within SQL for maintenance and auditing.
I have created the following accounts in Active Directory
CM_SQLDB – SQL Database Service
CM_SSRS – SQL Reporting Service * The account you specify must have Log on locally permissions on the computer hosting the SQL Reporting Services database.
CM_SQLAgent – SQL Agent Service
The last thing we need to do before installing SQL Server 2016 is to make sure .NET Framework 3.5 is installed. .NET 3.5 is a built-in feature of Windows server and can be installed via server manager or PowerShell. After you have verified and configured all the above configurations, let’s get started with the installation.
Installation
First, we need to mount the .ISO for SQL Server 2016
Right click the .ISO and select Mount which should bring up the following screen…
Right click setup and click Run as Administrator.
Select Installation and then select New SQL Server stand-alone installation or add features to an existing installation.
Enter your License key, then click Next.
Accept the License Terms, then click Next.
If you are on an internet connected machine, select Use Microsoft Update to check for Updates, then click Next.
Select the following features then select next
- Database Engine Services
- Reporting Services – Native
Change the Drive letter of the root and shared feature directories to the drive letter you have designated for SQL Server. In my case it is F:
Under Instance Configuration, select Default Instance (Configuration Manager supports Names Instances if you prefer them but for this guide, I am just using the default). Click Next.
On the Server Configuration screen, we will enter our created service accounts that we created earlier.
Just select the account name then the drop-down arrow and select browse and you can search AD for the service accounts you created then enter the respective passwords for each account.
Click on the Collation tab and make sure SQL_Latin1_General_CP1_CI_AS Database Engine is selected.
Click Next.
On the Database Engine Configuration page, we will select Windows Authentication Mode. (Vul ID V- 79121 wants Windows Authentication selected if you need to use Mixed Mode it will need to be documented and approved per the STIG).
In the Specify SQL Server administrators, add the security group that contains the admin accounts that will be responsible for maintaining the SQL server.
Click the Data Directories tab.
Verify the correct drive letter is assigned to each directory. Since I am using a different drive for my logs, I have changed the drive letter to H:.
There is nothing to do on the TempDB tab if you are not using separate drives like I am. If you are separating your TempDB to a different drive you will have to create the drive path on the drive and add it in the Data Directories and change the drive letter on the Log directory.
Once completed click Next.
On the Reporting Services Configuration page, you will select the Install only radio button.
Click Next.
Verify the information is correct on the Ready to Install page, then click Install.
This will take a few minutes to complete. Once the installation has completed verify all the components installed successfully. Click Close.
Install SQL Server Management Studio
Next, we will Install SQL Server Management Studio. This is no longer included in the install for SQL server and is a separate download you can use the link from SQL server Installation options or download from here
Right click the SSMS-Setup and click Run as Administrator.
Change the drive letter of the install location to that of your application drive, then click Install.
You will see the progress screen like this…
When the install is finished, you will need to restart your machine.
After a restart we can now access the Database and configure it for use with Configuration Manager.
DISCLAIMER
The sample files/scripts are not supported under any Microsoft standard support program or service. The sample files are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample files and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the files be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.