PowerShell script to enable TCP in SQL Server

EnableTCPSQLServer.ps1

Import-Module "sqlps"
$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='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)
$Tcp.IsEnabled = $true
$Tcp.Alter()
$Tcp

Does this work with SQL Server Express? I'm trying the following

Import-Module SqlServer
$wmi = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer"
$wmi.GetSmoObject("ManagedComputer[@Name='$env:COMPUTERNAME']/ServerInstance[@Name='SQLEXPRESS']/ServerProtocol[@Name='Tcp']")

$wmi.GetSmoObject("ManagedComputer[@Name='$env:COMPUTERNAME'] works fine but there is a property ServerInstances and not ServerInstance but in my case it's empty.

I've installed SQL Server Express using Chokolatey

# Install SQL Server 2012 Express
choco install mssqlserver2012express -y

# Install SQL Server Management studio
choco install sql-server-management-studio -y

Just sharing, the sql server express container image does the same thing but with registry keys. From the DockerFile

set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql13.SQLEXPRESS\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpdynamicports -value '' ; \
set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql13.SQLEXPRESS\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpport -value 1433 ; \

Hi, thanks for the script. I am trying to run on a couple of servers but on some of the servers, the WMI object does not have the SQL server instance listed. See image below:

Do you have any suggestions?

Sorry @geradev, it's been more than 5 years I don't work with that, didn't even worked with Windows since then 😕