Is there a way to enable tcp/ip protocol through powershell script or sql script?
The context: I got a Vagrantfile to provision a VM, this VM have SQL-SERVER 2012, 2014, 2016 and 2017 installed. When I check on the SQL SERVER CONFIGURATION MANAGER I saw that in every SQL-SERVER instance have the TCP/IP protocol disable.
Is there a way to enable them via powershell script or sql script?
2 Answers
Microsoft already provides guidance on this topic.
Using SQL Server PowerShell
To Enable a Server Network Protocol Using
- Using administrator permissions open a command prompt.
- Start Windows PowerShell from the taskbar, or click Start, then All Programs, then Accessories, then Windows PowerShell, then Windows PowerShell.
- Import the sqlps module by entering Import-Module "sqlps"
- Execute the following statements to enable both the TCP and named pipes protocols. Replace <computer_name> with the name of the computer that is running SQL Server. If you are configuring a named instance, replace MSSQLSERVER with the instance name. To disable protocols, set the IsEnabled properties to $false.
$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='<computer_name>']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)
$Tcp.IsEnabled = $true
$Tcp.Alter()
$Tcp Enable the named pipes protocol for the default instance.
$uri = "ManagedComputer[@Name='<computer_name>']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Np']"
$Np = $wmi.GetSmoObject($uri)
$Np.IsEnabled = $true
$Np.Alter()
$NpEnabling the TCP/IP protocol in SQL Server can be done right from SQL Server Configuration Manager, after expanding "SQL Server Network Configuration" followed by "Protocols for MSSQLSERVER".
Right-click on "TCP/IP" and choose "Enable", and afterward right-click "SQL Server (MSSQLSERVER)" and choose "Restart".
For a PowerShell script, see the Microsoft articleEnable or Disable a Server Network Protocol.
1
