2

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?

4

Microsoft already provides guidance on this topic.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-or-disable-a-server-network-protocol?view=sql-server-2017

Using SQL Server PowerShell

To Enable a Server Network Protocol Using

  1. Using administrator permissions open a command prompt.
  2. Start Windows PowerShell from the taskbar, or click Start, then All Programs, then Accessories, then Windows PowerShell, then Windows PowerShell.
  3. Import the sqlps module by entering Import-Module "sqlps"
  4. Execute the following statements to enable both the TCP and named pipes protocols. Replace 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()  
$Np
| improve this answer | |
1

Enabling 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 article Enable or Disable a Server Network Protocol.

| improve this answer | |
  • Thank you, I'm insterested to do the task via scripting, I'll check out the article. – Haroldo Payares Salgado Feb 14 '19 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.