Freigeben über

Enabling TCP/IP on multiple interfaces using PowerShell (part of hands free SQL Server installation)

  • Artikel

In trying to install SQL Server completely "hands free" I struck a snag  - by default the TCP/IP protocol is installed, but the interfaces are all "Enabled=false", which would require a manual step to enable them.

Naturally - PowerShell to the rescue!

To view the basic post-install state, open up the SQL Server Configuration Manager (below)

 

Double-click TCP/IP and switch to the "IP Addresses" tab.  Ah ha!  The culprit - "enabled" is set to "No" on each interface (IP1, IP2, etc)

 

After some basic searching I found the following Technet article - however it enables TCP "in general", but not specifically for each interface.  After further searching - I didn't find a suitable script to fix the problem, so I was forced to research and build one (see below).

 

The following script sets "Enabled" and the "Port" for each IP address.  Note the odd structure of ipaddressproperties field underneath a given ipaddress.

Enjoy!

 

As always - comments / fixes are welcomed!

 

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

# From: https://technet.microsoft.com/en-us/library/dd206997.aspx

# List the object properties, including the instance names.
# $Wmi # Uncomment to view

# Enable the TCP protocol on the default instance.

# SQL Server name should be simple - eg "SP2010". Instance name by default is SQLSERVER. 
$uri = "ManagedComputer[@Name='YOUR SQL SERVER NAME']/ServerInstance[@Name='INSTANCE NAME']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)
$Tcp.IsEnabled = $true
$TCP.alter()
#
# To view properties, use - $tcp.ipaddresses[1].ipaddressproperties or
# $tcp.ipaddresses[1].ipaddressproperties[0]
#
foreach ($tcpAddress in $Tcp.ipaddresses) {
 foreach ($property in $tcpAddress.ipaddressproperties) {
  if ($property.Name -eq "Enabled") {
   $property.Value=[bool]1
   $Tcp.Alter
  }
  if ($property.Name -eq "Port") {
   $property.Value=1433
   $Tcp.Alter
  }
 }
}
$Tcp

# Enable the named pipes protocol for the default instance.
$uri = "ManagedComputer[@Name='$sp2010_server_name']/ ServerInstance[@Name='$sql_instanceName']/ServerProtocol[@Name='Np']"
$Np = $wmi.GetSmoObject($uri)
$Np.IsEnabled = $true
$Np.Alter()
$Np

Restart-Service 'MSSQL$SQLSERVER' -Force

Comments

  • Anonymous
    November 07, 2013
    Gr8! But you forgot to add the assemblies in the script, add the following at the top: [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

  • Anonymous
    March 09, 2015
    The comment has been removed