DEV Community

JEFF
JEFF

Posted on

How to automate the process of enabling TCP/IP of a sql express server using Power shell scripting

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$smo = 'Microsoft.SqlServer.Management.Smo.' 
$wmi = new-object ($smo + 'Wmi.ManagedComputer')

# Change the sql instance name accordingly .
$SQLInstance = 'SQLEXPRESS'

# URI for TCP/IP protocol
$tcpUri = "$($wmi.urn.value)/ServerInstance[@Name=`'$SQLInstance`']/ServerProtocol[@Name='Tcp']"
$tcpProtocol = $wmi.GetSmoObject($tcpUri)

# URI for Shared Memory protocol
$sharedMemoryUri = "$($wmi.urn.value)/ServerInstance[@Name=`'$SQLInstance`']/ServerProtocol[@Name='Np']"
$sharedMemoryProtocol = $wmi.GetSmoObject($sharedMemoryUri)

# Enable TCP/IP protocol
$tcpProtocol.IsEnabled = $true
$tcpProtocol.Alter()

# Enable Shared Memory protocol
$sharedMemoryProtocol.IsEnabled = $true
$sharedMemoryProtocol.Alter()

# Restart SQL Server service
Restart-Service "MSSQL`$$($SQLInstance)"
# Check if the protocols were enabled successfully
if (-not $tcpProtocol.IsEnabled -or -not $sharedMemoryProtocol.IsEnabled) {
   Write-Host "Error: Failed to enable TCP/IP or Shared Memory protocol."
   Write-Host "Press Enter to exit..."
   Read-Host
   exit 1
}

Write-Host "TCP/IP protocols enabled successfully."
```




This script is used to enable tcp/ip through powershell scripting .
Change the $SQLInstance according to your server name . 


Enter fullscreen mode Exit fullscreen mode

Top comments (0)