I am very close to achieving a fully automated install of SQL Server with TCP/IP enabled, the IPALL TCP/IP port set to my chosen port e.g. "12345".
The script is a batch file that installs SQL using a config file;
echo Silent SQL Install, Please Wait...
echo.
Pushd SQLEXPRADV_x64_ENU
SETUP.EXE /ConfigurationFile=ConfigurationFile.ini
This element is fine.
The broken part is the powershell script being run as a *.PS1 file;
Powershell.exe -executionpolicy bypass -File "PS_Port_12345.ps1"
However, running the code in a powershell console works fine!;
Import-Module SQLPS -DisableNameChecking -Force
($Wmi = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $env:COMPUTERNAME)
($uri = "ManagedComputer[@Name='$env:COMPUTERNAME']/ ServerInstance[@Name='MYSQLTEST']/ServerProtocol[@Name='Tcp']")
# Getting settings
($Tcp = $wmi.GetSmoObject($uri))
$Tcp.IsEnabled = $true
($Wmi.ClientProtocols)
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value="12345"
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties
$Tcp.Alter()
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties
I have (hopefully) settled on the reason being that powershell needs to be opened to detect the modules's environment path after its installation. So, with the script being spawned as a PS1 file, called in the batch, no open powershell console runs, causing the script to fail.
As stated before, the SQL install runs fine, TCP is enabled, the PS1 file element fails. Yet if I load Powershell as admin, then run the code it works fine!
I haven't had much luck finding any ways round this. I spent a lot of time trying to get this to work with path statement changes, copying the module to the systems "C:\Windows\System32\WindowsPowerShell\v1.0\Modules" folder. But, it was a comments section on this site that stated the observed behaviour about the console being opened.
Looking forward to any suggestions to get this working fully, thanks.
Something was missed in my previous attempts but having a copy of the SQLPS moldule copied to the module path before the execution of the PS1 file, has cured the issue. Tested on clean VM's; Server 2019, Win 10 & Win 11.