Sdílet prostřednictvím

Configuring SQL Protocols through Windows PowerShell

  • Článek

                Sometimes we are asked about the possibility of configuring SQL Server protocols through PowerShell. In SQL Server 2008, the sqlps tool incorporates WMI and SMO into this powerful Windows administrator tool, making it easy to manage SQL Server protocols through PowerShell.

                To get started, run (elevated, if on Windows Vista or Windows Server 2008) sqlps.exe, which by default is located at the %ProgramFiles%Microsoft SQL Server100Toolsbinnsqlps.exe; or, if your architecture is x64, it is in the same path as above, under your Program Files (x86) directory.

                Now that you have an Admin SQL PowerShell window, here are some example scripts that you can run to configure your SQL Server instance:

Get the TCP, NP, and SM objects

This script is the starting point for manipulating the protocols properties on a local default instance. To modify this for a named instance, replace “MSSQLSERVER” with the name of your instance.

$MachineObject = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') .

$ProtocolUri = "ManagedComputer[@Name='" + (get-item env:computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol"

$tcp = $MachineObject.getsmoobject($ProtocolUri + "[@Name='Tcp']")

$np = $MachineObject.getsmoobject($ProtocolUri + "[@Name='Np']")

$sm = $MachineObject.getsmoobject($ProtocolUri + "[@Name='Sm']")

Enable remote connection protocols

Once you have the base protocol objects, enabling remote connections is trivial:

$np.IsEnabled = $true

$np.alter()

$tcp.IsEnabled = $true

$tcp.alter()

Calling the .alter() method commits changes you make to the registry, and you will need to restart the SQL Server instance for it to pick up these changes.

More elaborate example: Modifying an instance’s TCP Port

Once you have the TCP object, you can view the properties of the TCP Ports on the various IP Addresses your SQL Server instance is listening on. For instance, this command will show the properties of the “IPAll” IP Address:

$MachineObject.getsmoobject($tcp.urn.value + "/IPAddress[@Name='IPAll']")

The following commands will make your server listen on the TCP port 3344, by modifying the TcpPort property of the IPAll entry and then committing those changes:

$MachineObject.getsmoobject($tcp.urn.Value + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = "3344"

$tcp.alter()

You can now verify in the SQL Server Configuration Manager that your IPAll setting is now set to listen on TCP Port 3344, and restarting the SQL Server service will result in it now listening on the newly-specified port.

Dan Benediktson
SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

  • Anonymous
    September 11, 2008
    I am unable to connect with the sql server, when I am trying to connect with the server, it shows the connection failed: SQL State:'01000' SQL server error:121

  • Anonymous
    December 10, 2008
    Can you use PowerShell to add new IP addresses for SQL Server 2008 to use? We installed SQL Server 2008 Developer Edition and then added new IP addresses to the server. We want to use the new IP address for SQL Server, but this IP address is not listed in SQL Server Configuration Manager.

  • Anonymous
    January 04, 2009
    Do you have any idea about how I could connect to an instance using the "ip address,port" notation? For example, Microsoft propose that you could connect by using the following format: cd SQLSERVER:SQLMyComputerMyInstance This could work ok, but for us it doesn't. If I try to connect from the SQLSERVER:SQL prompt with cd (encode-sqlname "10.10.10.10,1234") I get a mixed error message; from WMI saying RPC server is unavailable, and from PS engine saying that the (encoded) path does not exist. Is there another way/method I could connect?

  • Anonymous
    June 27, 2012
    Hi Dan, Could you please give me the code for changing the Active and Enabled properties of IP Addresses.

  • Anonymous
    November 27, 2013
    It always states as creating and never gets completed. even post restart of the service

  • Anonymous
    June 30, 2014
    The comment has been removed