Skip to content

Latest commit

 

History

History
129 lines (95 loc) · 7.53 KB

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

File metadata and controls

129 lines (95 loc) · 7.53 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Enable or disable a server network protocol
Use SQL Server Configuration Manager or PowerShell to enable or disable a SQL Server server network protocol.
rwestMSFT
randolphwest
04/11/2024
sql
configuration
conceptual
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

Enable or disable a server network protocol

[!INCLUDE sql-windows-only]

All network protocols are installed during installation, by [!INCLUDEssNoVersion] Setup, but may or may not be enabled. This article describes how to enable or disable a server network protocol in [!INCLUDEssnoversion] by using [!INCLUDEssNoVersion] Configuration Manager or PowerShell. The [!INCLUDEssDE] must be stopped and restarted for the change to take effect.

Remarks

  • During setup of [!INCLUDEssExpress] edition, a login is added for the BUILTIN\Users group. This login 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.

  • [!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 install the appropriate update to add support for TLS 1.1 and 1.2 to [!INCLUDEssNoVersion]. For more information, see KB 3135244. Starting from [!INCLUDEsssql16-md], all release versions of SQL Server include TLS 1.2 support without further updates required.

Use SQL Server Configuration Manager

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

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

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

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

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

Note

If you have a named instance of [!INCLUDE ssnoversion-md], including [!INCLUDE ssexpress-md] edition, you should also restart the SQL Server Browser service.

Use SQL Server PowerShell

Enable a server network protocol with PowerShell

  1. Using administrator permissions, open a command prompt.

  2. Start Windows PowerShell from the taskbar or Start menu.

  3. Import the SqlServer module by entering Import-Module SqlServer.

  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 (including [!INCLUDE ssexpress-md] edition), replace MSSQLSERVER with the instance name.

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

    You can run this script from any machine, with or withour SQL Server installed. Make sure you have the SqlServer module installed.

    #requires the SqlServer module
Import-Module SQLServer

$wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer <#computer_name#>

# 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

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']"

Restart the Database Engine with 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

Note

If you have a named instance of [!INCLUDE ssnoversion-md], including [!INCLUDE ssexpress-md] edition, you should also restart the SQL Server Browser service.

Related content