Skip to content

Latest commit

 

History

History
93 lines (66 loc) · 3.23 KB

how-to-enable-tcp-sqlps.md

File metadata and controls

93 lines (66 loc) · 3.23 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic
How to enable the TCP protocol using SQLPS
Learn how to enable TCP protocols using SQLPS
markingmyname
maghan
matteot
08/06/2020
sql
sql-server-powershell
conceptual

How to enable the TCP protocol

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

[!INCLUDE sql-server-powershell-version]

  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