Enable or Disable a Server Network Protocol
Applies To: SQL Server 2016
All network protocols are installed by SQL Server Setup, but may or may not be enabled. This topic describes how to enable or disable a server network protocol in SQL Server 2016 by using SQL Server Configuration Manager or PowerShell. The Database Engine must be stopped and restarted for the change to take effect.
In This Topic
In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration.
In the console pane, click Protocols for <instance name>.
In the details pane, right-click the protocol you want to change, and then click Enable or Disable.
In the console pane, click SQL Server Services.
In the details pane, right-click SQL Server (<instance name>), and then click Restart, to stop and restart the SQL Server service.
Using administrator permissions open a command prompt.
Start Windows PowerShell from the taskbar, or click Start, then All Programs, then Accessories, then Windows PowerShell, then Windows PowerShell.
Import the sqlps module by entering Import-Module “sqlps”
Execute the following statements to enable both the TCP and named pipes protocols. Replace
<computer_name>with the name of the computer that is running SQL Server. If you are configuring a named instance, replace
MSSQLSERVERwith the instance name.
To disable protocols, set the
$smo = 'Microsoft.SqlServer.Management.Smo.' $wmi = new-object ($smo + 'Wmi.ManagedComputer'). # List the object properties, including the instance names. $Wmi # Enable the TCP protocol on the default instance. $uri = "ManagedComputer[@Name='<computer_name>']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']" $Tcp = $wmi.GetSmoObject($uri) $Tcp.IsEnabled = $true $Tcp.Alter() $Tcp # Enable the named pipes protocol for the default instance. $uri = "ManagedComputer[@Name='<computer_name>']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Np']" $Np = $wmi.GetSmoObject($uri) $Np.IsEnabled = $true $Np.Alter() $Np
When the script is run locally and configures the local computer, SQL Server PowerShell can make the script more flexible by dynamically determining the local computer name. To retrieve the local computer name, replace the line setting the
$urivariable with the following line.
$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"
After you enable or disable protocols, you must stop and restart the Database Engine for the change to take effect. Execute the following statements to stop and start the default instance by using SQL Server PowerShell. To stop and start a named instance replace
# Get a reference to the ManagedComputer class. CD SQLSERVER:\SQL\<computer_name> $Wmi = (get-item .).ManagedComputer # Get a reference to the default instance of the Database Engine. $DfltInstance = $Wmi.Services['MSSQLSERVER'] # Display the state of the service. $DfltInstance # Stop the service. $DfltInstance.Stop(); # Wait until the service has time to stop. # Refresh the cache. $DfltInstance.Refresh(); # Display the state of the service. $DfltInstance # Start the service again. $DfltInstance.Start(); # Wait until the service has time to start. # Refresh the cache and display the state of the service. $DfltInstance.Refresh(); $DfltInstance