How to install SSMS on a client workstation using Azure Automation DSC?

Question

I found the following post - https://blogs.msdn.microsoft.com/troy_aults_blog/2017/01/13/automating-installation-of-ssms-with-dsc/

Great, all I need to know now is the Product Id of SSMS-Setup-ENU.exe, right?

But how, on Earth, am I supposed to do it? When I installed it on another machine and tried the approach described in https://blogs.msdn.microsoft.com/brian_farnhill/2017/07/04/getting-ids-to-use-with-the-package-dsc-resource/ I got two Product Ids:

PS C:\> $x86Path = "HKLM:\Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\*"
PS C:\> $installedItemsX86 = Get-ItemProperty -Path $x86Path | Select-Object -Property DisplayName, PSChildName
PS C:\> $x64Path = "HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall\*"
PS C:\> $installedItemsX64 = Get-ItemProperty -Path $x64Path | Select-Object -Property DisplayName, PSChildName
PS C:\> $installedItems = $installedItemsX86 + $installedItemsX64
PS C:\> $installedItems | Where-Object -FilterScript { $null -ne $_.DisplayName } | Sort-Object -Property DisplayName | sls 'Management Studio'

@{DisplayName=Microsoft SQL Server Management Studio - 17.4; PSChildName={ac84c935-8f13-4f73-b541-7b09a11bdea8}}
@{DisplayName=SQL Server 2017 Management Studio Extensions; PSChildName={6492E746-1C5D-48C2-A92A-97D431F74664}}
@{DisplayName=SQL Server 2017 Management Studio Extensions; PSChildName={70C24F35-7E36-45FC-B289-3D2849E5556B}}
@{DisplayName=SQL Server Management Studio; PSChildName={F8ADD24D-F2F2-465C-A675-F12FDB70DB82}}
@{DisplayName=SQL Server Management Studio; PSChildName={1B8CFC46-1F08-4DA7-9FEA-E1F523FBD67F}}
@{DisplayName=SQL Server Management Studio for Analysis Services; PSChildName={CC6997A7-1638-4E38-B6CF-E776997036B0}}
@{DisplayName=SQL Server Management Studio for Reporting Services; PSChildName={4DDEB555-26D2-4E68-98AF-8F96232C13F2}}

I actually tried a different approach, which yields the same result (probably because both "sit" on the same data):

PS C:\> get-wmiobject Win32_Product -Filter "Name like '%sql%management%studio%'" | sort -Property Name | Format-Table IdentifyingNumber, Name, LocalPackage -AutoSize

IdentifyingNumber                      Name                                                LocalPackage
-----------------                      ----                                                ------------
{6492E746-1C5D-48C2-A92A-97D431F74664} SQL Server 2017 Management Studio Extensions        C:\Windows\Installer\43f1a.msi
{70C24F35-7E36-45FC-B289-3D2849E5556B} SQL Server 2017 Management Studio Extensions        C:\Windows\Installer\43f16.msi
{F8ADD24D-F2F2-465C-A675-F12FDB70DB82} SQL Server Management Studio                        C:\Windows\Installer\43f23.msi
{1B8CFC46-1F08-4DA7-9FEA-E1F523FBD67F} SQL Server Management Studio                        C:\Windows\Installer\43f27.msi
{CC6997A7-1638-4E38-B6CF-E776997036B0} SQL Server Management Studio for Analysis Services  C:\Windows\Installer\43f43.msi
{4DDEB555-26D2-4E68-98AF-8F96232C13F2} SQL Server Management Studio for Reporting Services C:\Windows\Installer\43f3c.msi

Both methods yield two product Ids:

• F8ADD24D-F2F2-465C-A675-F12FDB70DB82
• 1B8CFC46-1F08-4DA7-9FEA-E1F523FBD67F

So, which one is the correct one? Is there a more deterministic way of coping with the insanity of deducing the right product Id?

Answer 1

I think I have finally figured out what is going on, though I am yet to check whether this will help me to install SSMS using Azure Automation DSC.

Here is what I have done:

1. Renamed SSMS-Setup-ENU.exe to SSMS-Setup-ENU.zip
2. Peeked inside. The only text file is named "0" and this is an XML file.
3. The very first element is <BurnManifest xmlns="http://schemas.microsoft.com/wix/2008/Burn"> from which I concluded that the exe was created with WIX burn.
4. Renamed the file back.
5. Found https://stackoverflow.com/questions/26749207/extract-contents-of-burn-bootstrapper
6. Downloaded and extracted wix311-binaries.zip from https://github.com/wixtoolset/wix3/releases/tag/wix3111rtm
7. Ran dark.exe -x d:\temp SSMS-Setup-ENU.exe, which extracted a lot of msi files from SSMS-Setup-ENU.exe to d:\temp.
8. Found https://stackoverflow.com/questions/31919064/powershell-get-the-msi-product-code-out-of-a-msi-file-without-installing
9. Saved the PowerShell script from the answer and modified it a bit to ease the piping (the script code is below).
10. Piped all the msis through the script and identified the two msis with the Product Codes in question.

The PowerShell script is:

param(
    [Parameter(Mandatory = $true, Position = 0, ValueFromPipeline = $true)]
    [ValidateScript({ ($_ -match "\.msi$") -and (Test-Path $_ -PathType Leaf)})]
    $path)

process
{
    $comObjWI = New-Object -ComObject WindowsInstaller.Installer
    $MSIDatabase = $comObjWI.GetType().InvokeMember("OpenDatabase","InvokeMethod",$Null,$comObjWI,@("$Path",0))
    $props = @{ Path = "$path" }
    @('ProductCode', 'ProductName') |% {
        $Query = "SELECT Value FROM Property WHERE Property = '$_'"
        $View = $MSIDatabase.GetType().InvokeMember("OpenView","InvokeMethod",$null,$MSIDatabase,($Query))
        $View.GetType().InvokeMember("Execute", "InvokeMethod", $null, $View, $null)
        $Record = $View.GetType().InvokeMember("Fetch","InvokeMethod",$null,$View,$null)
        $props[$_] = $Record.GetType().InvokeMember("StringData","GetProperty",$null,$Record,1)
    }
    New-Object psobject -Property $props
}

The final command line is:

PS C:\> $g1 = "1B8CFC46-1F08-4DA7-9FEA-E1F523FBD67F"
PS C:\> $g2 = "F8ADD24D-F2F2-465C-A675-F12FDB70DB82"
PS C:\> dir D:\temp\AttachedContainer\x64\*.msi | .\GetProductCodeFromMSI.ps1 | sls "($g1|$g2)"

@{Path=d:\temp\AttachedContainer\x64\sql_ssms.msi; ProductName=SQL Server Management Studio; 
ProductCode={F8ADD24D-F2F2-465C-A675-F12FDB70DB82}}
@{Path=d:\temp\AttachedContainer\x64\sql_ssms_loc.msi; ProductName=SQL Server Management Studio; 
ProductCode={1B8CFC46-1F08-4DA7-9FEA-E1F523FBD67F}}


PS C:\> 

So, both product codes are found in the same SSMS installation and correspond to sql_ssms.msi and sql_ssms_loc.msi. Both have the same Product Name - SQL Server Management Studio.

Now, I have no idea what is sql_ssms_loc.msi, but I am going to try and use F8ADD24D-F2F2-465C-A675-F12FDB70DB82, because it corresponds to sql_ssms.msi. Will be back with the results.

EDIT 1

Using F8ADD24D-F2F2-465C-A675-F12FDB70DB82 seems to have worked.