0

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.

Tika9o9
  • 405
  • 4
  • 22
  • tcp/12345 is probably a poor choice as it's likely to flag alerts and/or interference from firewalls/antivirus packages assuming it to be NetBus traffic or similar. – AlwaysLearning Jul 28 '23 at 06:55
  • it's just for example, i'll be using a different one – Tika9o9 Jul 28 '23 at 07:00
  • what failure do you actually get when you run the ps file – siggemannen Jul 28 '23 at 08:47
  • Import-Module : The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory. Just done a test run and that is the main culprit, however, I went to run the script again (after changing nothing) at all and it works fine. run the ps1 file as part of a bat file - fail. Right click ps1 file, run with powershell - works. – Tika9o9 Jul 28 '23 at 09:07
  • Bittiness? Have you checked which specific powershell.exe executable your CMD script is launching? e.g.: If you installed the x64 version of PowerShellTools.msi then SQLPS isn't likely going to be in the module paths of a 32-bit PowerShell prompt. As an example, [Microsoft® SQL Server® 2016 SP3 Feature Pack](https://www.microsoft.com/en-us/download/details.aspx?id=103444) lists two sets of downloadables for all items but, in a display of infinite wisdom, doesn't indicate which ones are x86 and which are x64. – AlwaysLearning Jul 28 '23 at 10:11
  • See following : https://dba.stackexchange.com/questions/54622/using-sql-server-smo-with-a-port-number – jdweng Jul 28 '23 at 12:50
  • Bittiness, good shout. I'll have a look in this, if there are more than one module path i might so how I go copying the SQLPS module to the locations before executions. – Tika9o9 Jul 28 '23 at 13:31

1 Answers1

0

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.

xcopy /qisey SQLEXPRADV_x64_ENU\SQLPS C:\Windows\System32\WindowsPowerShell\v1.0\Modules\SQLPS
Tika9o9
  • 405
  • 4
  • 22