SQL Server - TCP/IP Properties in SQL Server

Instead of specifying 1433 in the TCP Port text box of each IP type section, you can only specify it in IPAll section.

You cannot specify 1433 in the TCP Port text box in the named instance when it is being used as default TCP port in the default instance. You must specify another fixed port which is not currently being used by other applications, you can run this command to check current port usage: 

netstat -a -n -o

For any instances which is not configured with 1433 default port, you must specify it in the connection string when remotely connecting to the named instance, for example, "SQL-B\ALTERNATE,3341".

The Window Management Instrumentation (WMI) is not available in Windows Server Core environment. The WMI provider is a published layer that is used with the SQL Server Configuration Manager snap-in for Microsoft Management Console (MMC) and the Microsoft SQL Server Configuration Manager. In that case there are two methods to configure TCP/IP properties of SQL Server in Windows Server Core environment.

Method 1

1. Open File Explorer, right-click Computer, and then choose Manage.

2. In Computer Management, right-click Computer Management (Local), and then choose Connect to another computer.

3. In Another computer field, type the computer name of the server that runs Windows Server Core.

4. After connection is successfully authenticated (if you have sufficient permission) you should be able to see SQL Server Configuration Manager snap-in under Services and Applications node.

If you encounter error messages when using Method 1, please refer to Access SQL Server Configuration Manger of remote instances via Microsoft Management Console.

Method 2

You can use SQL PowerShell to configure port by referencing the following steps:

1. Launch the SQL PowerShell in a command prompt.

SQLPS

2. Initialize the namespace that contains the classes representing the core SQL Server database engine objects.

$smo = 'Microsoft.SqlServer.Management.Smo.'

3. Set the ManagedComputer object that represents a Windows Management Instrumentaion(WMI) installation on an intance of SQL Server.

$wmi = new-object ($smo + 'Wmi.ManagedComputer') 

4.

$uri = "ManagedComputer[@Name='SQL-CORE']/ServerInstance[@Name='ALTERNATE']/ServerProtocol[@Name='TCP']"

5.

$Tcp = $wmi.GetSmoObject($uri)

6. Check the value of the IsEnabled propoerty.

$Tcp

7. Set the property to true if it is on false.

$Tcp.IsEnabled = $true

8. Check the properties of each IP type, input different types in @Name parameter, for example, @Name='IP1', @Name='IP2', ..., @Name='IPAll'.

$wmi.GetSmoObject($uri + "/IPAddress[@Name='']").IPAddressProperties

9. Except IPAll, remove any value under TcpDynamicPorts section of each IP type, replace the question mark with specific number.

$wmi.GetSmoObject($uri + "/IPAddress[@Name='IP?']").IPAddressProperties[3].Value=""

10. Remove value of TcpDynamicPorts section in IPAll type.

$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPALL']").IPAddressProperties[0].Value=""

11. Specify fixed port of TcpPort in IPAll.

$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPALL']").IPAddressProperties[1].Value=""

12. Validate all the changes.

$Tcp.Alter()

13. Return all Windows services on local machine that contains key word 'SQL'.

Get-Service *SQL*

14. Stop SQL Server Database engine service of the named instance.

Stop-Service -Name 'MSSQL$ALTERNATE' -Force

15. Start SQL Server Database engine service of the named instance.

Start-Service -Name 'MSSQL$ALTERNATE'