Edit

How to enable the TCP protocol

  • Article

How to enable the TCP protocol when connected to the console with SQLPS.

Note

There are two SQL Server PowerShell modules; SqlServer and SQLPS.

The SqlServer module is the current PowerShell module to use.

The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.

The SqlServer module contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.

Install the SqlServer module from the PowerShell Gallery.

For more information, see SQL Server PowerShell.

  1. Open a command prompt and type:

    C:\> SQLPS.EXE

    Tip

    If SQLPS is not found, you may need to open a new command prompt or just log-off and log back on.

  2. At the PowerShell command prompt, type:

    # Instantiate a ManagedComputer object which exposes primitives to control the
# installation of SQL Server on this machine.

$wmi = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' localhost

# Enable the TCP protocol on the default instance. If the instance is named, 
# replace MSSQLSERVER with the instance name in the following line.

$tcp = $wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
$tcp.IsEnabled = $true  
$tcp.Alter()  

# You need to restart SQL Server for the change to persist
# -Force takes care of any dependent services, like SQL Agent.
# Note: if the instance is named, replace MSSQLSERVER with MSSQL$ followed by
# the name of the instance (e.g. MSSQL$MYINSTANCE)

Restart-Service -Name MSSQLSERVER -Force

How to enable the TCP protocol when connected to the console not using SQLPS.

  1. Open a command prompt and type:

    C:\> PowerShell.exe

  2. At the PowerShell command prompt, type:

    # Get access to SqlWmiManagement DLL on the machine with SQL
# we are on, which is where SQL Server was installed.
# Note: this is installed in the GAC by SQL Server Setup.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement')

# Instantiate a ManagedComputer object which exposes primitives to control the
# installation of SQL Server on this machine.

$wmi = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' localhost

# Enable the TCP protocol on the default instance. If the instance is named, 
# replace MSSQLSERVER with the instance name in the following line.

$tcp = $wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
$tcp.IsEnabled = $true  
$tcp.Alter()  

# You need to restart SQL Server for the change to persist
# -Force takes care of any dependent services, like SQL Agent.
# Note: if the instance is named, replace MSSQLSERVER with MSSQL$ followed by
# the name of the instance (e.g. MSSQL$MYINSTANCE)

Restart-Service -Name MSSQLSERVER -Force

Next steps