Enable or Disable a Server Network Protocol

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 2012 by using SQL Server Configuration Manager or PowerShell. The Database Engine must be stopped and restarted for the change to take effect.

Security Note
During setup of SQL Server Express a login is added for the BUILTIN\Users group. This allows all authenticated users of the computer to access the instance of SQL Server Express as a member of the public role. The BUILTIN\Users login can be safely removed to restrict Database Engine access to computer users who have individual logins or are members of other Windows groups with logins.

In This Topic

• To enable or disable a server network protocol using:

  SQL Server Configuration Manager

  PowerShell

Using SQL Server Configuration Manager

To enable a server network protocol

1. In SQL Server 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 SQL Server service.

[Top]

Using SQL Server PowerShell

To Enable a Server Network Protocol Using PowerShell

1. Using administrator permissions open a command prompt.

2. Start Windows PowerShell 2.0 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 SQL Server. 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, 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 $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 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 '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
  

[Top]