1

I have an unattended installation of SQL Server 2016 using the .INI answer file. In my answer file, I enable TCP like this:

; Specify 0 to disable or 1 to enable the TCP/IP protocol. 
TCPENABLED="1"

Once the installation is finished, I also create an alias using a PowerShell script. So far so good. However, the SQL Server instance is installed with dynamic ports enabled and I can't see a way to specify a static TCP port (I want to use the standard 1433) in the answer file. Hence the alias won't work.

How can I set a static TCP port, either via the answer file, or using PowerShell?

  • Are you using multiple dedicated IP addresses for the SQL Server instances? i.e. your default instance MSSQLSERVER is running on 10.0.0.1 and your second instance SECRET is going to be running on 10.0.0.2? – John aka hot2use Aug 29 '18 at 12:44
  • There is just one instance on the server. Single DHCP-assigned IPv4 address. – Ondrej Tucny Aug 29 '18 at 13:24
1

After long hours searching for a viable solution I came up with this PowerShell function that can set the TCP port of a specific SQL Server instance:

function SetPort($instance, $port)
{
    # fetch the WMI object that contains TCP settings; filter for the 'IPAll' setting only
    # note that the 'ComputerManagement13' corresponds to SQL Server 2016
    $settings = Get-WmiObject `
        -Namespace root/Microsoft/SqlServer/ComputerManagement13 `
        -Class ServerNetworkProtocolProperty `
        -Filter "InstanceName='$instance' and IPAddressName='IPAll' and PropertyType=1 and ProtocolName='Tcp'"

    # there are two settings in a list: TcpPort and TcpDynamicPorts
    foreach ($setting in $settings)
    {
        if ($setting -ne $null)
        {
            # set the static TCP port and at the same time clear any dynamic ports
            if ($setting.PropertyName -eq "TcpPort")
            {
                $setting.SetStringValue($port)
            }
            elseif ($setting.PropertyName -eq "TcpDynamicPorts")
            {
                $setting.SetStringValue("")
            }
        }
    }
}

Now, this function is used like this:

SetPort "SQLInstance" 1433

The script shall be run elevated, i.e. Run as Administrator.

Credits go to this blog post which pointed me to the right direction.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.