Menu & Search

Enabling TCP Connections in SQL Server

Enabling TCP Connections in SQL Server

Some network protocols are not enabled by default in SQL Server. We need to enable the option and restart the SQL Service to allow TCP connections.

This post is a guide on how this is done:
# On Windows via SQL Server Configuration Manager.
# On Windows using PowerShell.


Enable TCP/IP SQL via Server Configuration Manager

1. Open SQL Server Configuration Manager.

SQL Server 2019 Configuration Manager Start Menu

2. Navigate to TCP/IP Protocols as highlighted and open Properties.

SQL Server Configuration Manager Protocols

3. Enable the highlighted option within the Protocol tab.  

SQL Server Configuration Manager TCP Properties

4. Enable what you need within the IP Addresses tab.

SQL Server Configuration Manager Enable IPv4

5. Click Apply.

SQL Server Changes on Next Service Restart

6. Navigate to SQL Server Services within the Configuration Manager and restart the SQL Server Service.

SQL Server Configuration Manager Services

Enable TCP/IP SQL Server using PowerShell

I’m using a quick script gained from Microsoft Docs, tweaking to run locally as instructed and I added some visibility of the service restart.

This script requires sqlps, just run Import-Module “sqlps” for the script functions to work.

## Enable/Disable MSSQL TCP & Named Pipes
$smo = 'Microsoft.SqlServer.Management.Smo.'  
$wmi = new-object ($smo + 'Wmi.ManagedComputer').  

# Enable TCP Protocol for the locl machine  
$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)  
$Tcp.IsEnabled = $true
$Tcp.Alter() 

# Enable Named Pipes Protocol for the local machine
$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Np']"  
$Np = $wmi.GetSmoObject($uri)  
$Np.IsEnabled = $true  
$Np.Alter()  

# Restart the SQL Services
$DfltInstance = $Wmi.Services['MSSQLSERVER']  
$DfltInstance.ServiceState
$DfltInstance.Stop(); 
Start-Sleep 1
$DfltInstance.Refresh();
$DfltInstance.ServiceState
Start-Sleep 1
$DfltInstance.Refresh();
$DfltInstance.ServiceState
$DfltInstance.Start();
Start-Sleep 1
$DfltInstance.Refresh();
$DfltInstance.ServiceState
PowerShell MSSQL TCP & Named Pipes Change

I was able to verify by looking at SQL Server Configuration Manager, but we can also do with the script info above.

SQL Server Configuration Manager Protocols

Share

0 Comments