Automating installation of SSMS with DSC

Over the past few months I've had a couple of customers ask me, "Now that SQL 2016 doesn't include SSMS how am I supposed to automate the installation?".  Typically my response is you shouldn't be installing it on your servers as its just an additional item that will require patching.  However there are times its necessary and often times customers have dedicated management servers where they want to automate the installation or you want to automate it on your workstation.  Either way DSC is here to assist, installing any .exe or .msi with DSC is relatively simple with the Package resource which is included with DSC.  There are likely other pieces of software you may want to automate on your SQL installations also.  Here I am providing a code example of specifically how SSMS can be installed but it can be modified for other uses.  This is again "Sample Code" so you will notice I am allowing for the use of plain text passwords, not something you would want to do in a production environment.


This is a simple configuration, its only intended purpose is to install SSMS.  To do this there are only a couple of parameters you will need to set.
Name: Actual file name of the package we want to install
Path: Full path to the executable or msi we want to install.
Arguments:  This is an optional parameter but required for SSMS so it doesn't generate prompts on install
ProductId: This is the GUID found in the uninstall key of the registry.  This allows DSE to remove the software if we set Ensure to "Absent"
Credential:  Credential of the user which has the privileges to install the software.
Adding a link to the file as formatting is getting lost when pasted into the blog.
For reference below is the link for additional details on the Package Resource.
DSC has a lot of flexibility for automating your infrastructure stay tuned next I will be talking about modifying environment Variables and running PowerShell scripts with DSC.

Comments (6)

  1. Thankyou says:

    Not sure I understand the line ” Import-DscResource -ModuleName xCertificateNode $NodeName” It’s giving an error that he parameter “$NodeName” is incorrect. Any idea ?

  2. Troy Ault says:

    I have added a link to the configuration file so that formatting is not lost. Let me know if this doesn’t resolve your error.

  3. Is this script posted somewhere on GitHub?

  4. I got your script to work with some modifications. But now I’m getting:
    SSMS-Setup-ENU.exe was installed, but the specified ProductId and/or Name does not match package details

    Which package is it checking?


    Configuration LCM_Push
    node $computername
    AllowModuleOverwrite = $true
    ConfigurationMode = ‘ApplyOnly’
    RefreshMode = ‘Push’
    RebootNodeIfNeeded = $true

    $password = ‘password’ | ConvertTo-SecureString -AsPlainText -Force
    $domCred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList ‘domain\user’, $password
    Lcm_push -computername localhost -outputpath c:\temp\SSMS
    Set-DscLocalConfigurationManager -CimSession localhost -path c:\temp\SSMS -verbose
    configuration DevConfig
    Import-DscResource -ModuleName xCertificate
    Import-DscResource –ModuleName ‘PSDesiredStateConfiguration’
    node $NodeName
    Package SSMS
    Ensure = ‘Present’
    Name = “SSMS-Setup-ENU”
    path = “\\server\dba\Microsoft SQL Server\2016 SSMS Setup\SSMS-Setup-ENU.exe”
    Arguments = “/install /passive /norestart”
    ProductId = “31769AA7-DDF3-463E-9E25-752362EAA5B2”
    Credential = $domCred
    $cd =
    Allnodes =
    NodeName = ‘localhost’
    PsDscallowDomainUser = $true
    PSDscAllowPlainTextPassword = $true
    RebootNodeIfNeeded = $true

    Devconfig -configurationdata $cd -nodename localhost -domainCred $domCred -outputpath c:\temp\ssms
    Start-DscConfiguration -ComputerName Localhost -path c:\temp\SSMS -wait -force -verbose

    1. This one seems to have worked:

      by using this query of wmi I found it:
      (Get-WmiObject -Class Win32_Product) | ?{$_.packagename -like ‘*ssms*’}

Skip to main content