none
Code to change TCP port for SQL works locally but not remotely RRS feed

  • Question

  • Hi All.

    I have the piece of code below, which works fine when run locally however when run using PowerShell remoting/credssp, it comes up with an error.

    $MachineObject = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') 
    $ProtocolUri = "ManagedComputer[@Name='" + (get-item env:computername).Value + "']/ServerInstance[@Name='DEV01']/ServerProtocol"
    $tcp = $MachineObject.getsmoobject($ProtocolUri + "[@Name='Tcp']")
    $np = $MachineObject.getsmoobject($ProtocolUri + "[@Name='Np']")
    $sm = $MachineObject.getsmoobject($ProtocolUri + "[@Name='Sm']")
    $MachineObject.getsmoobject($tcp.urn.Value + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = "2222"
    $tcp.alter()
    
    
    $service_name = 'MSSQL$DEV01'
    $service = Get-Service -Name $service_name -ErrorAction SilentlyContinue
      if ($service.Length -gt 0){
            Write-host "Restarting the service - $service_name"
            Restart-Service -Name 'MSSQL$DEV01' -Force 
            Start-Sleep -s 10
        
      }
    
    
    
     
     

    When executed from a remote host, I get the error.

    Exception calling "GetSmoObject" with "1" argument(s): "Attempt to retrieve data for object failed for ManagedComputer 'MY-Server_Name."

    Any help or explanation of what could be causing this would be appreciated.

    • Moved by jrv Sunday, July 21, 2019 4:05 PM Better forum
    Saturday, July 20, 2019 12:22 AM

Answers

  • I decided that the code deserved a CmdLet that would fail friendly.

    function Set-SqlServerTcpPort{
    [CmdLetBinding()] Param( [string]$ServerInstance = 'MSSQLServer', [string]$TcpPort = '1433', [string]$ComputerName = $env:COMPUTERNAME ) Begin{ $id = [Security.Principal.WindowsIdentity]::GetCurrent() $sp = [Security.Principal.WindowsPrincipal]::New($id) if($sp.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)){ }else{ Write-Warning 'This command must be run from an elevated account' break } if([System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement')){ Write-Verbose 'Microsoft.SqlServer.SqlWmiManagement assembly loaded' }else{ Throw 'Load failed for "Microsoft.SqlServer.SqlWmiManagement"' } } Process{ Try{ $mc = new-object Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer($ComputerName) $tcpProtocols = $mc.ServerInstances[$ServerInstance].ServerProtocols['Tcp'] $tcpProtocols.IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value = $TcpPort # stop service and apply new settings then start service $mc.Services[$ServerInstance].Stop() $tcpProtocols.Alter() $mc.Services[$ServerInstance].Stop() } Catch{ Throw $_ } } }


    \_(ツ)_/



    • Edited by jrv Sunday, July 21, 2019 9:46 PM
    • Marked as answer by MrFlinstone Tuesday, July 23, 2019 8:01 PM
    Sunday, July 21, 2019 9:41 PM

All replies

  • Your code never connects to any remote system.


    \_(ツ)_/

    Saturday, July 20, 2019 12:58 AM
  • Here - this will do all of it for you in one command:

    Set-Sql​Network​Configuration


    \_(ツ)_/

    Saturday, July 20, 2019 1:06 AM
  • Also the command does its own remoting and restarts the service.


    \_(ツ)_/


    • Edited by jrv Saturday, July 20, 2019 1:10 AM
    Saturday, July 20, 2019 1:10 AM
  • Hi,

    In order to use the command the SQL server cloud adapter needs to be in place, this is not an Azure VM, but an on-premise VM with SQL 2016, how they can make a very useful command only work in Azure is rather weird in my opinon.

    Sunday, July 21, 2019 3:39 PM
  • The command works on on-premise SQLServer.  It uses the same code you were trying to write.

    An adapter must be in place.  Perhaps it has an issue with your VM configuration.


    \_(ツ)_/

    Sunday, July 21, 2019 4:04 PM
  • This is the error i get.

    Set-SqlNetworkConfiguration : The operation failed on target server 'xxxxxxxxxxxxxxxx\DEV01,0 Version:'. Verify that the
    target server is accessible and that the SQL Server Cloud Adapter service is running.
    At line:1 char:1
    + Set-SqlNetworkConfiguration -Protocol TCP -Port 2222 -ServerInstance  ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : ResourceUnavailable: (xxxxxxxxxxxxx\DEV01,0 Version::SqlInstanceInfo) [Set-SqlNetworkCon
       figuration], SqlPowerShellRemoteOperationFailedException
        + FullyQualifiedErrorId : RemoteOperationFailedError,Microsoft.SqlServer.Management.PowerShell.IaaS.SetSqlNetworkC

    Sunday, July 21, 2019 4:17 PM
  • I have moved this topic to the SQLServer forum where you may get more help with your exact issue.

    Also read this: https://docs.microsoft.com/en-us/sql/database-engine/cloud-adapter-for-sql-server?view=sql-server-2014

    \_(ツ)_/

    Sunday, July 21, 2019 4:22 PM
  • Still not having much luck with the below.

    $scriptblock = 
     {   
     
    $x = (get-item env:computername).Value
    write-host $x
    $MachineObject = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') 
    $ProtocolUri = "ManagedComputer[@Name='" + (get-item env:computername).Value + "']/ServerInstance[@Name='DEV01']/ServerProtocol"
    $tcp = $MachineObject.getsmoobject($ProtocolUri + "[@Name='Tcp']")
    $np = $MachineObject.getsmoobject($ProtocolUri + "[@Name='Np']")
    $sm = $MachineObject.getsmoobject($ProtocolUri + "[@Name='Sm']")
    $MachineObject.getsmoobject($tcp.urn.Value + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = "2222"
    $tcp.alter()
    
    
     }
     
            
    $server = "xxxxxxxxxxxxxxxxxxxxxxxxx"
    $username = "username"
    $pass= Get-Content "C:\cred\credential.txt"
    $password = ConvertTo-SecureString -String $pass -AsPlainText -Force
    $credential = new-object -typename System.Management.Automation.PSCredential -argumentlist $username,$password
    $session = new-pssession -ComputerName $server -credential $credential -authentication credssp
    
    invoke-command -Session $session -Scriptblock $scriptblock 

    If i take the code and run it on the actual host, it works perfectly fine.

    I get the error below if executed using PsRemoting.

    Exception calling "GetSmoObject" with "1" argument(s): "Attempt to retrieve data for object failed for ManagedComputer 'xxxxxxxxxxxx1'."


    Sunday, July 21, 2019 5:20 PM
  • Here is the code in SMO/WMI converted from C# examples:

    Try{
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement')
    
        $mc = new-object Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer('ALPHA')
        $mc.ConnectionSettings.ProviderArchitecture = 'Use64bit'
    
        $sqlService = $mc.Services['MSSQLServer'] # default instance name
    
        if ($sqlservice.ServiceState -eq 'Running') {
            $sqlservice.Stop()
        }
    
        $tcpProtocols = $mc.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
        $tcpProtocols.IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value = '1433'
        $tcpProtocols.Alter()
        $sqlservice.Start()
    }
    Catch{
        Throw $_
    }


    \_(ツ)_/

    Sunday, July 21, 2019 5:51 PM
  • I decided that the code deserved a CmdLet that would fail friendly.

    function Set-SqlServerTcpPort{
    [CmdLetBinding()] Param( [string]$ServerInstance = 'MSSQLServer', [string]$TcpPort = '1433', [string]$ComputerName = $env:COMPUTERNAME ) Begin{ $id = [Security.Principal.WindowsIdentity]::GetCurrent() $sp = [Security.Principal.WindowsPrincipal]::New($id) if($sp.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)){ }else{ Write-Warning 'This command must be run from an elevated account' break } if([System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement')){ Write-Verbose 'Microsoft.SqlServer.SqlWmiManagement assembly loaded' }else{ Throw 'Load failed for "Microsoft.SqlServer.SqlWmiManagement"' } } Process{ Try{ $mc = new-object Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer($ComputerName) $tcpProtocols = $mc.ServerInstances[$ServerInstance].ServerProtocols['Tcp'] $tcpProtocols.IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value = $TcpPort # stop service and apply new settings then start service $mc.Services[$ServerInstance].Stop() $tcpProtocols.Alter() $mc.Services[$ServerInstance].Stop() } Catch{ Throw $_ } } }


    \_(ツ)_/



    • Edited by jrv Sunday, July 21, 2019 9:46 PM
    • Marked as answer by MrFlinstone Tuesday, July 23, 2019 8:01 PM
    Sunday, July 21, 2019 9:41 PM
  • Works a treat, thanks.
    Tuesday, July 23, 2019 8:01 PM