Set static TCP port in SQL Server unattended installation

Question

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 K. N., 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

score 1 · Accepted Answer · answered Nov 02 '18 at 23:07

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.

Set static TCP port in SQL Server unattended installation

1 Answers1