SqlSetup: Feature CONN is not detected correctly

[johlju]

Details of the scenario you tried and the problem that is occurring

This issue seems to happen for SQL Server 2017. This issue did not happen on SQL Server 2016. It was noticed when trying to change the integration tests from using SQL Server 2016 to SQL Server 2017 in PR #1103.

This happens only when CONN is already installed on the target node. On AppVeyor build worker there are already a Developer edition installed, hence CONN exist on the target node. When our integration test configuration runs with Features = 'SQLENGINE,AS,CONN,BC,SDK', the Set-TargetResource function sees that CONN is already installed, and will change the feature list to 'SQLENGINE,AS,BC,SDK'.
Setup starts, and apparently uninstalls CONN, either because it is not in the list of features any longer, or it is still installed but the the registry entries are changed/removed so is looks like it no longer is installed (just as bad as uninstalling).

Below verbose output shows that first the Test-TargetResource function finds it, and also the Set-TargetResource finds it, but after Set-TargetResource installed the rest of the features CONN ('Client Connectivity Tools feature') can no longer be detected.

VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Set      ]
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Resource ]  [[SqlSetup]Integration_Test]
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Test     ]  [[SqlSetup]Integration_Test]
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Using path 'D:\setup.exe'.
...
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Detecting Client Connectivity Tools feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup\Client_Components_Full).
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Client Connectivity Tools feature detected.
...
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Features found: CONN
...
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Test     ]  [[SqlSetup]Integration_Test]  in 0.4900 seconds.
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Set      ]  [[SqlSetup]Integration_Test]
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Using path 'D:\setup.exe'.
...
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Detecting Client Connectivity Tools feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup\Client_Components_Full).
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Client Connectivity Tools feature detected.
...
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Using path 'D:\setup.exe'.
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Starting setup using arguments: /ACTION="Install" /ASCOLLATION="Finnish_Swedish_CI_AS" /AGTSVCACCOUNT="APPVYR-WIN\svc-SqlAgentPri" /ASSVCACCOUNT="APPVYR-WIN\svc-SqlPrimary" /SQLSVCPASSWORD="********" /AGTSVCSTARTUPTYPE="Automatic" /ASSVCPASSWORD="********" /QUIET="True" /SECURITYMODE="SQL" /ASSYSADMINACCOUNTS="APPVYR-WIN\SqlAdmin" /AGTSVCPASSWORD="********" /INSTANCENAME="DSCSQLTEST" /IACCEPTSQLSERVERLICENSETERMS="True" /SQLSYSADMINACCOUNTS="APPVYR-WIN\SqlAdmin" "APPVYR-WIN\SqlInstall" /SQLSVCACCOUNT="APPVYR-WIN\svc-SqlPrimary" /SQLCOLLATION="Finnish_Swedish_CI_AS" /FEATURES=SQLENGINE,AS,BC,SDK /ASSERVERMODE="MULTIDIMENSIONAL" /UPDATEENABLED="False" /BROWSERSVCSTARTUPTYPE="Automatic" /SAPWD="********"
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Started the process with id 616 using the path 'D:\setup.exe', and with a timeout value of 7200 seconds.
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Setup exited with code '0'. Setup finished successfully.
...
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Detecting Client Connectivity Tools feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup\Client_Components_Full).
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Client Connectivity Tools feature not detected.
..
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Features found: SQLENGINE,AS,BC,SDK
VERBOSE: [APPVYR-WIN]:                            [[SqlSetup]Integration_Test] Unable to find feature 'CONN' among the installed features: 'SQLENGINE,AS,BC,SDK'.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Set      ]  [[SqlSetup]Integration_Test]  in 135.7980 seconds.

The DSC configuration that is using the resource (as detailed as possible)

Any configuration not installing CONN when CONN is already installed.

Version of the operating system and PowerShell the target node is running

AppVeyor Build worker: Windows Server 2016, SQL Server 2017

SQL Server edition and version the target node is running

SQL Server 2017 RTM

What SQL Server PowerShell modules, and which version, are present on the target node.

SQLPS

Version of the DSC module you're using, or write 'dev' if you're using current dev branch

Dev

[johlju]

I can replicate this, and I think this is bug in the SQL Server 2017 setup. 🤔

I can replicate this behavior locally, both with DSC and installing manually. I have used SQL Server 2017 RTM, but adding CU 5 during setup (through UpdateSource).

That the correct value exist can be tested with the following code

(Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup\Client_Components_Full').FeatureList

TL;DR

When installing CONN together with all other features the correct registry entry is added. If feature 'CONN' is installed first, and then other Tools components are added, like the feature 'SDK' or feature 'BC', then the registry entry for the 'CONN' feature is removed.

Test run 1

If I install a instance with just 'SQLENGINE,CONN', the correct value Connectivity_FNS=3 exist in the registry path HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup\Client_Components_Full.

Using command line

setup.exe /UPDATEENABLED="True" /UPDATESOURCE=".\Updates\SQL2017CU5" /ACTION="Install" /INSTANCENAME="TEST2017" /BROWSERSVCSTARTUPTYPE="Automatic" /FEATURES=SQLENGINE,CONN /QUIET="True" /IACCEPTSQLSERVERLICENSETERMS="True" /SQLSVCACCOUNT=COMPANY\SqlSvc /SQLSVCPASSWORD=******* /SQLSysAdminAccounts=COMPANY\SqlAdmin COMPANY\SqlSvc

Using DSC

SqlSetup 'Sql2017'
{
    InstanceName          = 'TEST2017'
    Features              = 'SQLENGINE,CON'
    SQLCollation          = 'SQL_Latin1_General_CP1_CI_AS'
    SQLSvcAccount         = $SqlServiceCredential
    AgtSvcAccount         = $SqlAgentServiceCredential
    ASSvcAccount          = $SqlServiceCredential
    SQLSysAdminAccounts   = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
    SourcePath            = '\\fileserver.compant.local\images$\SQL2016RTM'
    UpdateEnabled         = 'True'
    UpdateSource          = '.\Updates\SQL2017CU5'
    ForceReboot           = $false
    BrowserSvcStartupType = 'Automatic'
}

Result

After the above configuration, the FeatureList in registry is:

Connectivity_Full=3 Connectivity_FNS=3

Test run 2

After the servers was rebooted from "Test run 1", the configuration was ran again, but this time adding 'SDK' to the list of features so it contains 'SQLENGINE,CONN,SDK', then the value Connectivity_FNS=3 is removed from the registry path HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup\Client_Components_Full.

Using command line

setup.exe /UPDATEENABLED="True" /UPDATESOURCE=".\Updates\SQL2017CU5" /ACTION="Install" /INSTANCENAME="TEST2017" /BROWSERSVCSTARTUPTYPE="Automatic" /FEATURES=SQLENGINE,CONN,SDK /QUIET="True" /IACCEPTSQLSERVERLICENSETERMS="True" /SQLSVCACCOUNT=COMPANY\SqlSvc /SQLSVCPASSWORD=******* /SQLSysAdminAccounts=COMPANY\SqlAdmin COMPANY\SqlSvc

Using DSC

SqlSetup 'Sql2017'
{
    InstanceName          = 'TEST2017'
    Features              = 'SQLENGINE,CONN,SDK'
    SQLCollation          = 'SQL_Latin1_General_CP1_CI_AS'
    SQLSvcAccount         = $SqlServiceCredential
    AgtSvcAccount         = $SqlAgentServiceCredential
    ASSvcAccount          = $SqlServiceCredential
    SQLSysAdminAccounts   = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
    SourcePath            = '\\fileserver.compant.local\images$\SQL2016RTM'
    UpdateEnabled         = 'True'
    UpdateSource          = '.\Updates\SQL2017CU5'
    ForceReboot           = $false
    BrowserSvcStartupType = 'Automatic'
}

Result

After the above configuration, the FeatureList in registry is:

Connectivity_Full=3 SDK_Full=3 SDK_FNS=3

Test run 3

On a clean server the configuration was ran for the first time with 'SDK' to the list of features so it contains 'SQLENGINE,CONN,SDK', then the value Connectivity_FNS=3 does get added to the path HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup\Client_Components_Full.

Using DSC

SqlSetup 'Sql2017'
{
    InstanceName          = 'TEST2017'
    Features              = 'SQLENGINE,CONN,SDK'
    SQLCollation          = 'SQL_Latin1_General_CP1_CI_AS'
    SQLSvcAccount         = $SqlServiceCredential
    AgtSvcAccount         = $SqlAgentServiceCredential
    ASSvcAccount          = $SqlServiceCredential
    SQLSysAdminAccounts   = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
    SourcePath            = '\\fileserver.compant.local\images$\SQL2016RTM'
    UpdateEnabled         = 'True'
    UpdateSource          = '.\Updates\SQL2017CU5'
    ForceReboot           = $false
    BrowserSvcStartupType = 'Automatic'
}

Result

After the above configuration, the FeatureList in registry is:

Connectivity_Full=3 SDK_Full=3 Connectivity_FNS=3 SDK_FNS=3

Summary

When installing CONN together with all other features the correct registry entry is added. If CONN is first installed, and then other Tools components are added, like the feature SDK, then the registry entry for the CONN feature is removed.

[johlju]

I'm trying to wrap my head around how SQL Server detect how CONN is installed. There is no easy answer. It is not as easy as just a registry key obviously.
We need a better way of detecting if CONN is installed.

[johlju]

In my tests, for SQL Server 2017, the feature CONN (Client Tools Connectivity, and SQL Client Connectivity SDK) install state is controlled by

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\140\ConfigurationState]
"Connectivity_Full"=dword:00000001 = Installed

If the 'Connectivity_Full' is 1 then CONN is installed, if any value or the key is not present, the CONN is not installed.

I thinking of adding this new check under a feature flag to not break any older SQL Server edition.