Skip to content

enable-or-disable-a-server-network-protocol.md

Latest commit

 

History

History
127 lines (95 loc) · 7.3 KB

enable-or-disable-a-server-network-protocol.md

File metadata and controls

127 lines (95 loc) · 7.3 KB
title Enable or Disable a Server Network Protocol | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql
ms.prod_service high-availability
ms.reviewer
ms.technology configuration
ms.topic conceptual
helpviewer_keywords
network protocols [SQL Server], disabling
remote connections [SQL Server], enabling using Configuration Manager
protocols [SQL Server], enabling using Configuration Manager
protocols [SQL Server], disabling using Configuration Manager
disabling network protocols, Configuration Manager
network protocols [SQL Server], enabling
enabling network protocols, Configuration Manager
surface area configuration [SQL Server], connection protocols
connections [SQL Server], enabling remote using Configuration Manager
ms.assetid ec5ccb69-61c9-4576-8843-014b976fd46e
author MikeRayMSFT
ms.author mikeray
manager craigg

Enable or Disable a Server Network Protocol

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md] All network protocols are installed by [!INCLUDEssNoVersion] Setup, but may or may not be enabled. This topic describes how to enable or disable a server network protocol in [!INCLUDEssCurrent] by using [!INCLUDEssNoVersion] Configuration Manager or PowerShell. The [!INCLUDEssDE] must be stopped and restarted for the change to take effect.

Important

During setup of [!INCLUDEssExpress] a login is added for the BUILTIN\Users group. This allows all authenticated users of the computer to access the instance of [!INCLUDEssExpress] as a member of the public role. The BUILTIN\Users login can be safely removed to restrict [!INCLUDEssDE] access to computer users who have individual logins or are members of other Windows groups with logins.

Warning

[!INCLUDEssNoVersion] and [!INCLUDEmsCoName] data providers for [!INCLUDEssNoVersion] up to [!INCLUDEsssql14] only support TLS 1.0 and SSL 3.0 by default. If you enforce a different protocol (such as TLS 1.1 or TLS 1.2) by making changes in the operating system SChannel layer, your connections to [!INCLUDEssNoVersion] might fail unless you have installed the appropriate update to add support for TLS 1.1 and 1.2 to [!INCLUDEssNoVersion] which is listed here. Starting from [!INCLUDEsssql15], all release versions of SQL Server include TLS 1.2 support without further updates required.

In This Topic

Using SQL Server Configuration Manager

To enable a server network protocol

  1. In [!INCLUDEssNoVersion] Configuration Manager, in the console pane, expand SQL Server Network Configuration.

  2. In the console pane, click Protocols for <instance name>.

  3. In the details pane, right-click the protocol you want to change, and then click Enable or Disable.

  4. In the console pane, click SQL Server Services.

  5. In the details pane, right-click SQL Server (<instance name>), and then click Restart, to stop and restart the [!INCLUDEssNoVersion] service.

Using SQL Server PowerShell

To Enable a Server Network Protocol Using PowerShell

  1. Using administrator permissions open a command prompt.

  2. Start Windows PowerShell from the taskbar, or click Start, then All Programs, then Accessories, then Windows PowerShell, then Windows PowerShell.

  3. Import the sqlps module by entering Import-Module “sqlps”

  4. 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 [!INCLUDEssNoVersion]. If you are configuring a named instance, replace MSSQLSERVER with the instance name.

    To disable protocols, set the IsEnabled properties to $false.

    $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

To configure the protocols for the local computer

  • When the script is run locally and configures the local computer, [!INCLUDEssNoVersion] 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 $uri variable with the following line.

    $uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"

To restart the Database Engine by using SQL Server PowerShell

  • After you enable or disable protocols, you must stop and restart the [!INCLUDEssDE] for the change to take effect. Execute the following statements to stop and start the default instance by using [!INCLUDEssNoVersion] PowerShell. To stop and start a named instance replace 'MSSQLSERVER' with 'MSSQL$<instance_name>'.

    # 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