SQLSetup - InstallSQLDataDir fails if set to the root of a drive

[dmcafe]

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

When using a drive root (D:\) for the InstallSQLDataDir parameter, the script is inserting an extra double quote that causes errors

Verbose logs showing the problem

From Detail.txt:
(01) 2018-11-15 09:07:30 Slp: Setting: INSTALLSQLDATADIR
(01) 2018-11-15 09:07:30 Slp: Value specified: D:" /UPDATEENABLED=False /ACTION=Install
(01) 2018-11-15 09:07:30 Slp: New setting source: CommandLine; previous setting source: Default
...
(01) 2018-11-15 09:07:55 Slp: The input setting "INSTALLSQLDATADIR" has invalid character " " ". Retry setup with valid value for "INSTALLSQLDATADIR".

From the console:

VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Using path 'C:\DevOps\Install\Standard\setup.exe'.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Starting setup using arguments: /ACTION="Install" /INSTANCEID="MSSQLSERVER" /INSTANCEDIR="C:\Program
Files\Microsoft SQL Server" /SQLUSERDBDIR="D:\MSSQL13.MSSQLSERVER\MSSQL\Data" /PID="*****-*****-*****-*****-*****" /AGTSVCSTARTUPTYPE="Automatic" /QUIET="True"
/SQLTEMPDBLOGDIR="O:\MSSQL13.MSSQLSERVER\MSSQL\Log\TempDB" /SQLBACKUPDIR="D:\MSSQL13.MSSQLSERVER\MSSQL\Backup" /INSTANCENAME="MSSQLSERVER" /IACCEPTSQLSERVERLICENSETERMS="True"
/SQLUSERDBLOGDIR="L:\MSSQL13.MSSQLSERVER\MSSQL\Log" /SQLSYSADMINACCOUNTS="*****\******" /SQLTEMPDBDIR="O:\MSSQL13.MSSQLSERVER\MSSQL\Data\TempDB" /SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
/FEATURES=SQLENGINE /INSTALLSQLDATADIR="D:\" /UPDATEENABLED="False"
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Started the process with id 2504 using the path 'C:\DevOps\Install\Standard\setup.exe', and with a timeout
value of 7200 seconds.
WARNING: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Setup exited with code '-2061893624'. Please see the 'Summary.txt' log file in the 'Setup Bootstrap\Log'
folder.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Using path 'C:\DevOps\Install\Standard\setup.exe'.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Detecting Database Engine feature.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Database Engine feature not detected.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Detecting Full-text feature.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Full-text feature not detected.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Detecting Reporting Services feature.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Reporting Services feature not detected.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Detecting Analysis Services feature.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Analysis Services feature not detected.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Detecting Integration Services feature.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Integration Services feature not detected.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Detecting Documentation Components feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL
Server\130\ConfigurationState).
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Documentation Components feature not detected.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Detecting Client Connectivity Tools feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL
Server\130\Tools\Setup\Client_Components_Full).
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Client Connectivity Tools feature detected.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Detecting Client Connectivity Backwards Compatibility Tools feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL
Server\130\Tools\Setup\Client_Components_Full).
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Client Connectivity Backwards Compatibility Tools feature detected.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Detecting Client Tools SDK feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL
Server\130\Tools\Setup\Client_Components_Full).
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Client Tools SDK feature not detected.
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Detecting Master Data Services (MDS) feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL
Server\130\ConfigurationState).
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] 'Master Data Services (MDS) feature not detected.'
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Features found: CONN,BC
VERBOSE: [MYHOSTNAME]:                            [[SqlSetup]InstallDefaultInstance] Unable to find feature 'SQLENGINE' among the installed features: 'CONN,BC'.
VERBOSE: [MYHOSTNAME]: LCM:  [ End    Set      ]  [[SqlSetup]InstallDefaultInstance]  in 82.8130 seconds.
PowerShell DSC resource MSFT_SqlSetup  failed to execute Set-TargetResource functionality with error message: System.Exception: Test-TargetResource returned false after calling Set-TargetResource.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : MYHOSTNAME

VERBOSE: [MYHOSTNAME]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : MYHOSTNAME

Suggested solution to the issue

My current workaround is to escape the trailing slash escape

InstallSQLDataDir    = 'D:\\'

The DSC configuration that is used to reproduce the issue (as detailed as possible)

# insert configuration here
Configuration SQLTest
{
    [CmdletBinding()]
    Import-DscResource -ModuleName SqlServerDsc

    node MYNODE
    {
        #region Install SQL Server
        SqlSetup 'InstallDefaultInstance'
        {
            InstanceName         = 'MSSQLSERVER'
            SourcePath           = 'C:\DevOps\Install\Standard'
            InstallSQLDataDir    = 'D:\'
            SQLUserDBDir         = 'D:\MSSQL13.MSSQLSERVER\MSSQL\Data'
            SQLUserDBLogDir      = 'L:\MSSQL13.MSSQLSERVER\MSSQL\Log'
            SQLTempDBDir         = 'O:\MSSQL13.MSSQLSERVER\MSSQL\Data\TempDB'
            SQLTempDBLogDir      = 'O:\MSSQL13.MSSQLSERVER\MSSQL\Log\TempDB'
            SQLBackupDir         = 'D:\MSSQL13.MSSQLSERVER\MSSQL\Backup'
	    ForceReboot          = $false
            Features             = 'SQLENGINE,BC,Conn'
	    InstanceID           = 'MSSQLSERVER'
	    ProductKey           = '*****-*****-*****-*****-*****'
            UpdateEnabled        = 'False'
            InstallSharedDir     = 'C:\Program Files\Microsoft SQL Server'
            InstallSharedWOWDir  = 'C:\Program Files (x86)\Microsoft SQL Server'
            InstanceDir          = 'C:\Program Files\Microsoft SQL Server'
            SQLCollation         = 'SQL_Latin1_General_CP1_CI_AS'
        }
    }
}

SQL Server edition and version the target node is running

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)

SQL Server PowerShell modules present on the target node

SQLPS 1.0 C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

The operating system the target node is running

Microsoft Windows Server 2016 Standard

Version and build of PowerShell the target node is running

5.1.14393.2485

Version of the DSC module that was used ('dev' if using current dev branch)

SqlServerDsc 12.1.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\SqlServerDsc.psd1

[johlju]

This is strange one. So something is parsing the single backslash wrongly? 🤔 Labeled it as a bug and help wanted.

[Teutenberg]

In SQL Server configuration.ini files, ini have escape characters, so the \ is to cancel the escape character.
I use this bit of code before supplying the DataDir as a work around:

if ($InstallSQLDataDir.Length -lt 3 -and $InstallSQLDataDir[-1] -ne '') {
$InstallSQLDataDir = $InstallSQLDataDir + '\'
}
elseif ($InstallSQLDataDir.Length -lt 4 -and $InstallSQLDataDir[-1] -eq '' -and $InstallSQLDataDir[-2] -ne '') {
$InstallSQLDataDir = $InstallSQLDataDir + ''
}

[mdaniou]

@dmcafe Could you please try with no \ at all in the config ?

InstallSQLDataDir = 'D:\'

If it works the resource could be updated to remove any extra \ .
I have tested a common installation with /INSTALLSQLDATADIR="C:" and it works

[dmcafe]

Same error when I use this:
InstallSQLDataDir = 'D:'

Here's the relevant blocks from the Summary.txt log file showing that the InstallSQLDataDir isn't properly parsed.

INSTALLSHAREDDIR:              C:\Program Files\Microsoft SQL Server\
INSTALLSHAREDWOWDIR:           C:\Program Files (x86)\Microsoft SQL Server\
INSTALLSQLDATADIR:
INSTANCEDIR:                   C:\Program Files\Microsoft SQL Server

[johlju]

This block is removing the slash... and adding it. This was added in PR #404 to handle bugs using cluster, and then updated in various other PR's (after looking at the blame history).

But looking at the code it seem to contradict it self. 🤔 Are we removing trailing backslashes, and then adding them back... This seems strange.
The whole point of this block of code must be to add missing trailing backslashes to paths? Is there a scenario where it is not correct, like in clustering?

https://github.com/PowerShell/SqlServerDsc/blob/155b7abf26f9ab1656411be6c1b7de7018c5dd66/DSCResources/MSFT_SqlSetup/MSFT_SqlSetup.psm1#L994-L1031

[johlju]

Aha, is it that this code is suppose to remove backslashes from D:\SqlFolder\, but not from D:\. 🤔 It makes more sense.

I see this property is not used in the integration tests. We should add that to the integration tests to test this.

[nesith]

@johlju
As mentioned earlier the issue is that SQL config file treats single \ as an escape character for quotation mark. So this issue apply to all configurable parameters which a root directory can be specified.

So there are couple of ways to fix this bug

        if ($parameterValue -and $parameterValue -notmatch '^[a-zA-Z]:\\$') 
         { 
             Set-Variable -Name $parameterName -Value $parameterValue.TrimEnd('\') 
         } 
  
         # If the path only contains a qualifier but no backslash ('M:'), then a backslash is added ('M:\'). 
         if ($parameterValue -match '^[a-zA-Z]:$') 
         { 
             Set-Variable -Name $parameterName -Value "$parameterValue\" 
         }

Change the above logic to make sure if root directory is specified an additional \ is added so that config file will not treat it as an escape character.

or change the logic below to make sure that any argument with root directory specified does not get quotes around it i.e. INSTANCEDIR=D:\ rather than "INSTANCEDIR=D:\"

I feel approach one is simpler and cleaner, thoughts ?

# Build the argument string to be passed to setup
    $arguments = ''
    foreach ($currentSetupArgument in $setupArguments.GetEnumerator())
    {
        if ($currentSetupArgument.Value -ne '')
        {
            # Arrays are handled specially
            if ($currentSetupArgument.Value -is [System.Array])
            {
                # Sort and format the array
                $setupArgumentValue = ($currentSetupArgument.Value | Sort-Object | ForEach-Object { '"{0}"' -f $_ }) -join ' '
            }
            elseif ($currentSetupArgument.Value -is [System.Boolean])
            {
                $setupArgumentValue = @{ $true = 'True'; $false = 'False' }[$currentSetupArgument.Value]
                $setupArgumentValue = '"{0}"' -f $setupArgumentValue
            }
            else
            {
                # Features are comma-separated, no quotes
                if ($currentSetupArgument.Key -eq 'Features')
                {
                    $setupArgumentValue = $currentSetupArgument.Value
                }
                else
                {
                    $setupArgumentValue = '"{0}"' -f $currentSetupArgument.Value
                }
            }

            $arguments += "/$($currentSetupArgument.Key.ToUpper())=$($setupArgumentValue) "
        }

    }

[nesith]

I will go ahead and fix it based on approach 1 and make a pull request. we can discuss if that's not the best way to fix it. @johlju

[nesith]

@johlju Approach 1 doesn't work well, it still had issues with escape characters, I've gone with approach 2 and it now works well in my test environment. I will make a pull request with this.