PowerShell - Replacing Entries Of A Config File Automatically

Question

.

Hey Guys,

in due of a script I wrote to automatically install Microsoft SQL Server with PowerShell, i also have to replace a few parapeter in the config file. The config file looks like this :

IACCEPTSQLSERVERLICENSETERMS="True"
IACCEPTPYTHONLICENSETERMS="True"
ACTION="Install"
SUPPRESSPRIVACYSTATEMENTNOTICE="True"
IACCEPTROPENLICENSETERMS="True"
PID="11111-00000-00000-00000-00000"
MRCACHEDIRECTORY="C:\Software\Offline Packages"
ENU="True"
QUIET="True"
QUIETSIMPLE="False"
UpdateEnabled="False"
USEMICROSOFTUPDATE="False"
UpdateSource="MU"
FEATURES=SQLENGINE,REPLICATION,ADVANCEDANALYTICS,SQL_INST_MR,SQL_INST_MPY,FULLTEXT,CONN,BC,SDK,SNAC_SDK,LOCALDB
HELP="False"
INDICATEPROGRESS="False"
X86="False"
INSTANCENAME="SQLNEU"
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
INSTANCEID="SQLEXPRESS"
SQLUSERDBDIR="C:\DB\Databases"
SQLUSERDBLOGDIR="C:\DB\Logs"
SQLTELSVCACCT="NT Service\SQLTELEMETRY$SQLEXPRESS"
SQLTELSVCSTARTUPTYPE="Automatic"
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
AGTSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"
AGTSVCSTARTUPTYPE="Disabled"
COMMFABRICPORT="0"
COMMFABRICNETWORKLEVEL="0"
COMMFABRICENCRYPTION="0"
MATRIXCMBRICKCOMMPORT="0"
SQLSVCSTARTUPTYPE="Automatic"
FILESTREAMLEVEL="0"
ENABLERANU="True"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSVCACCOUNT="NT Service\MSSQL$SQLEXPRESS"
SQLSVCINSTANTFILEINIT="True"
SQLSYSADMINACCOUNTS="IMWST01\Administrator" "AVENGERS\Tony.Stark"
SECURITYMODE="SQL"
SAPWD="Test1234!"
SQLTEMPDBFILECOUNT="1"
SQLTEMPDBFILESIZE="200"
SQLTEMPDBFILEGROWTH="64"
SQLTEMPDBLOGFILESIZE="8"
SQLTEMPDBLOGFILEGROWTH="64"
ADDCURRENTUSERASSQLADMIN="False"
TCPENABLED="1"
NPENABLED="0"
BROWSERSVCSTARTUPTYPE="Disabled"
EXTSVCACCOUNT="NT Service\MSSQLLaunchpad$SQLEXPRESS"
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$SQLEXPRESS"

Therefore i wrote following line to receive the config entries and replace them with the ones of the hash :

$SQL_Server_Files_DB = "C:\DBTest\Databases"
$SQL_Server_Files_Logs = "C:\TestDB\Logs"

$SQL_Config = "C:\Scripts\SQLConfig.ini"

$SQL_Server_Config_Parameters = @{ 

"IACCEPTSQLSERVERLICENSETERMS"  = "TESTNEU";
"IACCEPTPYTHONLICENSETERMS"     = "Cool" ;
"SUPPRESSPRIVACYSTATEMENTNOTIC" = "True" ;
"IACCEPTROPENLICENSETERMS"      = "True" ;

"MRECACHEDIRECTORY" = "$SQL_Server_Software_Basic\$SQL_Server_Software_Directory\Offline Packages";

"SQLUSERDBDIR"    = $SQL_Server_Files_DB;
"SQLUSERDBLOGDIR" = $SQL_Server_Files_Logs;

"PID"  = "11111-00000-00000-00000-00000";
"ENU"  = "True";
"QUET" = "True";
"X86"  = "False"; 

"INSTANCENAME" = "SQLNEU"
"INSTANCEID"   = "SQLEXPRESS"

"SECURITYMODE" = "SQL"
"SAPWD" = "Test1234!"
}

Copy-Item -Path $SQL_Config -Destination $SQL_Config_TMP -Force

Get-Content -path C:\Scripts\SQLConfig.ini |

foreach-object -begin { $Server_Config = @{ } } -process { 
                                                            $Config_Entries = [regex]::split( $_ , '=' ); 
                                                            $Config_Entries = $Config_Entries -replace '["''"]'
                                            
                                                            $Config_Entries_Parameter = $Config_Entries[ 0 ]
                                                            $Config_Entries_Value     = $Config_Entries[ 1 ]

                                                            if( ( $Config_Entries_Parameter.CompareTo( "" ) -ne 0 ) -and ( $Config_Entries_Parameter.StartsWith( "[" ) -ne $True ) ) 
                                                            { 
                                                                if( $SQL_Server_Config_Parameters.Contains( $Config_Entries_Parameter ) )
                                                                {
                                                                    if( !( $SQL_Server_Config_Parameters[ $Config_Entries_Parameter ] -eq $Config_Entries_Value ) )
                                                                    {
                                                                        Write-Host "The Detected Value Of The SQL Config Parameter '$Config_Entries_Parameter' Differs From The User - Defined Value ! [ $Config_Entries_Value ]"

                                                                        $neu = $( $SQL_Server_Config_Parameters[ $Config_Entries_Parameter ] ) 

                                                                        $SQL_Config_Entries_Standard = $Config_Entries_Parameter + "=" + '"' + $Config_Entries_Value + '"'
                                                                        $SQL_Config_Entries_User_Defined = $( $Config_Entries_Parameter ) + "=" + '"' + $( $neu ) + '"'
                                                                        
                                                                        Write-Host "OLD : " $SQL_Config_Entries_Standard
                                                                        Write-Host "NEW : " $SQL_Config_Entries_User_Defined 

                                                                        Write-Host "Changing Value Of The SQL Config Parameter '$Config_Entries_Parameter' To The User - Defined Value ! [ $( $SQL_Server_Config_Parameters[ $Config_Entries_Parameter ] ) ]"
                                                                        
                                                                        $SQL_Config_TMP  = "C:\Temp\SQL_Config_Standard.ini"
                                                                        $test = ( Get-Content -Path $SQL_Config_TMP ) -replace $SQL_Config_Entries_Standard , $( $SQL_Server_Config_Parameters[ $Config_Entries_Parameter ] )
                                                                        $test | Set-Content -Path 'C:\Temp\SQL_Config_Standard.ini' -Force
                                                                    }
                                                                }
                                                            } 
                                                         }


#Copy-Item -Path "C:\Temp\SQL_Config_Standard.ini" -Destination "C:\Scripts" -Force
#Remove-Item -Path "C:\Temp\SQL_Config_Standard.ini" -Force

Unfortunately this doesn't work as expected. It's replacing the first paramters like IACCEPTSQLSERVERLICENSETERMS & IACCEPTPYTHONLICENSETERMS to the values of the hash, but it fails if it has to replace the folder paramters like SQLUSERDBDIR & SQLUSERDBLOGDIR :

The Detected Value Of The SQL Config Parameter 'IACCEPTSQLSERVERLICENSETERMS' Differs From The User - Defined Value ! [ False ]
OLD :  IACCEPTSQLSERVERLICENSETERMS="False"
NEW :  IACCEPTSQLSERVERLICENSETERMS="TESTNEU"
Changing Value Of The SQL Config Parameter 'IACCEPTSQLSERVERLICENSETERMS' To The User - Defined Value ! [ TESTNEU ]
The Detected Value Of The SQL Config Parameter 'IACCEPTPYTHONLICENSETERMS' Differs From The User - Defined Value ! [ True ]
OLD :  IACCEPTPYTHONLICENSETERMS="True"
NEW :  IACCEPTPYTHONLICENSETERMS="Cool"
Changing Value Of The SQL Config Parameter 'IACCEPTPYTHONLICENSETERMS' To The User - Defined Value ! [ Cool ]
The Detected Value Of The SQL Config Parameter 'INSTANCENAME' Differs From The User - Defined Value ! [ SQLEXPRESS ]
OLD :  INSTANCENAME="SQLEXPRESS"
NEW :  INSTANCENAME="SQLNEU"
Changing Value Of The SQL Config Parameter 'INSTANCENAME' To The User - Defined Value ! [ SQLNEU ]
The Detected Value Of The SQL Config Parameter 'SQLUSERDBDIR' Differs From The User - Defined Value ! [ C:\DB\Databases ]
OLD :  SQLUSERDBDIR="C:\DB\Databases"
NEW :  SQLUSERDBDIR="C:\DBTest\Databases"
Changing Value Of The SQL Config Parameter 'SQLUSERDBDIR' To The User - Defined Value ! [ C:\DBTest\Databases ]
The Detected Value Of The SQL Config Parameter 'SQLUSERDBLOGDIR' Differs From The User - Defined Value ! [ C:\DB\Logs ]
OLD :  SQLUSERDBLOGDIR="C:\DB\Logs"
NEW :  SQLUSERDBLOGDIR="C:\TestDB\Logs"
Changing Value Of The SQL Config Parameter 'SQLUSERDBLOGDIR' To The User - Defined Value ! [ C:\TestDB\Logs ]
The regular expression pattern SQLUSERDBLOGDIR="C:\DB\Logs" is not valid.
At C:\Scripts\filetest.ps1:72 char:162
+ ... andard , $( $SQL_Server_Config_Parameters[ $Config_Entries_Parameter  ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (SQLUSERDBLOGDIR="C:\DB\Logs":String) [], RuntimeException
    + FullyQualifiedErrorId : InvalidRegularExpression

Can you please give me a hint, why this is happening and how to modify the code that this works like expected ?

Thanks a lot.

Best regards NumeroUnoDE

Bernard Moeskops · Answer 1 · 2020-09-09T11:18:07.987

I personally found the script a bit complex so I made a simpler one.

$SQL_Server_Files_DB = "C:\DBTest\Databases"
$SQL_Server_Files_Logs = "C:\TestDB\Logs"

$SQL_Config = "C:\Scripts\SQLConfig.ini"

$SQL_Server_Config_Parameters = @{ 

"IACCEPTSQLSERVERLICENSETERMS"  = "TESTNEU";
"IACCEPTPYTHONLICENSETERMS"     = "Cool" ;
"SUPPRESSPRIVACYSTATEMENTNOTIC" = "True" ;
"IACCEPTROPENLICENSETERMS"      = "True" ;

"MRECACHEDIRECTORY" = "$SQL_Server_Software_Basic\$SQL_Server_Software_Directory\Offline Packages";

"SQLUSERDBDIR"    = $SQL_Server_Files_DB;
"SQLUSERDBLOGDIR" = $SQL_Server_Files_Logs;

"PID"  = "11111-00000-00000-00000-00000";
"ENU"  = "True";
"QUET" = "True";
"X86"  = "False"; 

"INSTANCENAME" = "SQLNEU"
"INSTANCEID"   = "SQLEXPRESS"

"SECURITYMODE" = "SQL"
"SAPWD" = "Test1234!"
}

$content = Get-Content -path $SQL_Config
$newFile = @()
foreach($line in $content){
    if($line){
        $key = '"' + $line.Split("=")[0] + '"'
        $value = $line.Split("=")[1]
        $matchParam = $SQL_Server_Config_Parameters.($key.Replace('"',""))
        if($matchParam){
            $value = '"' + $matchParam + '"'
        }
        $newFile += @{
             $key = $value
        }
    }
}
foreach($line in $newFile){
    $newLine = ($line.Keys -replace '"') + $line.Values -join "="
    $newLine | Out-File "C:\Scripts\SQLConfigNew.ini" -Encoding utf8 -Append
}

This script takes the ini file and creates a new one at "C:\Scripts\SQLConfigNew.ini". So you should change the path at the bottom for Out-File to the correct location. But I hope this helps!

Hey Bernard, thanks a lot. This works perfectly. But i now want to replace the temp path "C:\Temp\SQL.ini" with the global temp directory so i changed the entry to : $content = Get-Content -path "$env:TEMP\SQL_Config_Standard.ini" — NumeroUnoDE, Sep 09 '20 at 11:15
Hey Bernard, yes indeed. See my post below for my question concerning the TEMP directory. Best regards Dennis — NumeroUnoDE, Sep 10 '20 at 06:23

PowerShell - Replacing Entries Of A Config File Automatically

1 Answers1