Skip to content

Instantly share code, notes, and snippets.

@stefanteixeira

stefanteixeira/EnableTCPSQLServer.ps1

Created August 25, 2015 20:02
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save stefanteixeira/12208058dd0894ce0873 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save stefanteixeira/12208058dd0894ce0873 to your computer and use it in GitHub Desktop.
Download ZIP
PowerShell script to enable TCP in SQL Server
Raw
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
@Sarafian
Copy link

Sarafian commented Dec 27, 2016

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

@Sarafian
Copy link

Sarafian commented Dec 27, 2016

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 ; \

@GeradeDev
Copy link

GeradeDev commented Jan 21, 2021

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:

image

Do you have any suggestions?

@stefanteixeira
Copy link
Author

stefanteixeira commented Jan 21, 2021

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment