SQL Server – Set Static TCP Port in SQL Server Unattended Installation

sql servertcpunattended

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?

Best Answer

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.

Related Topic