PowerShell - Automate SQL Install - SCCM PreReqs

Written on January 19, 2019

This is a continuation of automating Sccm prerequisites part 1, part 2 and part 3.

In this post I will be covering how you can automate the installation of Microsoft Sql Server. We will only install the Sccm required Sql components, Reporting Services and the Sql Database Engine.

Automatically installing Sql server can be accomplished in a few different ways. You can use the command line supported options of the setup.exe file that comes with the Sql installation media. You can also create a Sql install configuration file, and run the same setup.exe file pointing to that configuration file.

The alternative to these options, is to use Desired State Configuration (DSC). If you are not familiar with this product I would highly recommend looking into it. In essence, DSC is a technology that can automate infrastructure. Using DSC, you will be writing code that can configure, build, and maintain your environment (it can also manage Linux systems). Think infrastructure as code. I will be posting a lot more on DSC, and hopefully this has peaked your interest if you are not familiar.

What You Need

  1. PowerShell v5
    • Default on Win10 and Server 2016
  2. SqlServerDsc PowerShell Dsc Module
    • Download from PowerShell Gallery, directly from PowerShell
  3. Sql Installation Media
    • Mount or extract ISO on target server

Check your PowerShell version:


Name                           Value
----                           -----
PSVersion                      5.1.17134.407
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}

# if you see PSVersion start with 5, you are on v5

If you aren’t on PSv5, download and install WMF 5.1 here and download and install the PowerShell package manager here

Get the SqlServerDsc module

You should be able to run 'Find-Module -Name SqlServerDsc' and see something like below.

Find-Module -Name SqlServerDsc

Version    Name            Repository     Description
-------    ----            ----------     -----------   SqlServerDsc    PSGallery      Module with DSC Resources...

If the server you are installing Sql on has internet access, you can simply pipe that command to Install-Module.

Find-Module -Name SqlServerDsc | Install-Module 

# Or just -> Install-Module -Name SqlServerDsc

If you need to download it first and copy to another computer:

# Choose where to save the module

Save-Module -Name SqlServerDsc -Path $path

Now you can copy the SqlServerDsc folder location to your server and place it here: C:\Program Files\WindowsPowerShell\Modules\

# Should look something like this
C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc
   - DSCResources
   - en-US
   - Examples
   - sv-SE
   - Tests

Test SqlServerDsc module installation

Get-DscResource -Module SqlServerDsc

ImplementedAs   Name                      ModuleName                     Version
-------------   ----                      ----------                     -------
PowerShell      SqlAG                     SqlServerDsc         
PowerShell      SqlAGDatabase             SqlServerDsc         
PowerShell      SqlAGListener             SqlServerDsc         
PowerShell      SqlAGReplica              SqlServerDsc         
PowerShell      SqlAlias                  SqlServerDsc         
PowerShell      SqlAlwaysOnService        SqlServerDsc         
PowerShell      SqlDatabase               SqlServerDsc         
PowerShell      SqlDatabaseDefaultLoca... SqlServerDsc         

The Code

I have broken the code into it's different components. First is the 'configuration' which is just like a PowerShell function. It has parameters that you can specify and you run it the same as you would a function.

Next is the Configuration Data. Using Configuration Data is not mandatory, but can be required under certain circumstances. In this example, if you want to use credentials (IE: configure SQL Services with service accounts) then you will need to use the Configuration Data.

Lastly, the MOF file generation and the 'PUSH'; sending the MOF to the 'Local Configuration Manager' so it can do your bidding.


configuration SccmSqlInstallation {





    [String]$instanceDir="$ENV:ProgramFiles\Microsoft SQL Server",


    [String]$sharedDir="$ENV:ProgramFiles\Microsoft SQL Server",

    [String]$sharedWOWDir="${ENV:ProgramFiles(x86)}\Microsoft SQL Server",

  Import-DscResource -ModuleName SqlServerDsc
  Import-DscResource -ModuleName PSDesiredStateConfiguration

  node $computerName {

    WindowsFeature 'NetFramework' {
      Name = 'Net-Framework-45-Core';
      Ensure = 'Present';

    SqlSetup 'SqlInstall' {
      InstanceName = $sqlInstanceName;
      SourcePath = $sqlSourceFiles;
      Action = 'Install';
      Features = $features;
      InstanceDir = $instanceDir;
      InstallSQLDataDir = $dataDir;
      SQLSvcStartupType = 'Automatic';
      AgtSvcStartupType = 'Automatic';
      AgtSvcAccount = $agentSvcCredential;
      SQLSysAdminAccounts = $sysAdminAccounts;
      SQLSvcAccount = $sqlSvcCredential;
      SQLCollation = 'SQL_Latin1_General_CP1_CI_AS';
      DependsOn = '[WindowsFeature]NetFramework';

    SqlServerNetwork 'SqlStaticTcp' {
      InstanceName = $sqlInstanceName;
      ProtocolName = 'TCP';
      IsEnabled = $true;
      TcpPort = "$sqlPortNumber";
      DependsOn = '[SqlSetup]SqlInstall';

    # This part can be removed 
    # If you aren't using credentials, OR
    # If you have opted to store credentials in Plain Text
    LocalConfigurationManager {
      CertificateId = $node.Thumbprint
    # Remove to here

Configuration Data

If you plan on using credentials in your configuration, you will need to use this. There is the right way to do this (using certificates to encrypt your credentials), and there is the wrong way to do this (storing your credentials in plain text). To do the insecure thing, you can uncomment out the PsDscAllowPlainTextPassword key below and remove the CertificateFile and Thumbprint entries.

Doing it the right way takes more work of course. There is a great document that explains how to set this up here.

$config = @{
  AllNodes = @(
      NodeName = $ENV:COMPUTERNAME 
      #PsDscAllowPlainTextPassword = $true;
      #PsDscAllowDomainUser = $true;
      CertificateFile = 'C:\cert\cert.cer';
      Thumbprint = '4B1EF9E6C194098257E93120A4A39DA853F23434'

Generate the MOF

It is here where you can customize how Sql will install. You can remove the service accounts if you want the sql services running as default. If you don't use credentials at all, you do not need the ConfigurationData parameter.


SccmSqlInstallation -OutputPath C:\temp\sqlInstall `
  -computerName $ENV:COMPUTERNAME `
  -ConfigurationData $config `
  -sqlInstanceName cm `
  -sqlSourceFiles 'D:\' `
  -sqlSvcCredential $svcCred `
  -sysAdminAccounts 'codeAndKeep\cmAdmin' `
  -agentSvcCredential $svcCred `
  -features "SQLENGINE,RS"

# SQLENGINE to install the database engine
# RS will install the reporting services

# You do not need to specify service account credentials 
# for Sccm to work

Push it real good

Finally we can push the configuration to the server and watch the magic happen.

# This step is only required if you used credentials 
# with certificate encryption
Set-DscLocalConfigurationManager -Path C:\temp\sqlInstall

Start-DscConfiguration -Path C:\temp\sqlInstall -Wait -Force -Verbose

This is prone to have errors if you have misconfigured something. You can check the Sql Installation Summary file for insight into any errors you get. Generally located here: %ProgramFiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log\Summary.txt where nnn is the SQL version (ex: 130).

Thanks for reading

PS> exit