How to change the TCP/IP port for a SQL Server instance

I wrote this script sometime back. I thought it would be a good idea to share this script. The background for writing this script was there were a bunch of SQL Server installations in the environment with the same instance names but on different boxes. The installations were pushed through programmatically and company policy was set in place which needed the TCP/IP port to be changed to a different port other than the default port i.e. 1433. The DBA obviously wouldn’t be too happy sitting and using SQL Server Configuration Manager on multiple servers doing the same monotonous task. The ask was to write a script which could be pushed to all the servers so that the port on which the SQL instance was listening on was modified programmatically.

This script changes the PORT number for a named instance of SQL Server 2008 called KATMAI to 1434. The namespace used here are:

SQL Server 2005: \root\Microsoft\SqlServer\ComputerManagement

SQL Server 2008: \root\Microsoft\SqlServer\ComputerManagement10

So if you wanted to use the script for SQL Server 2005, you would need to change the namespace to the one for SQL Server 2005. The other variables that come into play for this script are the Instance Name and the Port number:

‘Specify the instance name if any. Default is MSSQLSERVER.
‘Assumption is that we are running this for the default instance.
‘For a named instance, only provide the instance name. Eg: For a named instance LABDC\INST1, the parameter below would be strInstance = "INST1"
‘For a cluster, we need to run this script on the active node.

strInstance = "KATMAI"

‘Specify the new PORT

strPort = "1434"

So, you would have to modify the above variables for the correct Instance Name and Port number.

The script then makes use of the ServerNetworkProtocolProperty class methods to set the TCP port value.

If you need to extend this script, you can do so by creating an Array for the list of Servers in your environment and running the entire code in the script in another WHILE loop.

Another customized implementation could be if you want to change the port number for multiple instances with incremental values of Port Numbers. This can be done by querying the same namespace for getting the SQL instances on the box using SqlService. A sample WMI script is provided in the blog post below.

HOW TO DETECT SQL SERVER INSTANCES / FEATURES INSTALLED ON A MACHINE

Once you have the Instance Names, then you can again create a loop with the Port Number variable being incremented and execute the function to set the TCP/IP Port number.

Download the script from here

Reference:

How to- Configure a Server to Listen on a Specific TCP Port

Happy Automation!! ūüôā

How to find startup parameters for SQL Server 2005 using WMI

SQL Server startup parameters -d, -l, -e which store the information about the master database data file, log file and ERRORLOG locations respectively are stored under the registry key: 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\Parameters

However, if you need to find out the startup parameters without using the regedit snap-in (default and any extra ones added), you can use the following script:

 <<SCRIPT>>

strComputer = "."
 

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")

 

Set colItems = objWMIService.ExecQuery( _

 

"SELECT * FROM SqlServiceAdvancedProperty WHERE SqlServiceType = 1 and PropertyName = 'STARTUPPARAMETERS'",,48)

 

For Each objItem in colItems

 

Wscript.Echo "ServiceName: " & objItem.ServiceName

 

Wscript.Echo "PropertyName: " & objItem.PropertyName

 

Wscript.Echo "PropertyStrValue: " & objItem.PropertyStrValue

 

Next

 

 <</SCRIPT>

Retrieving TCP/IP Properties using WMI

This can be done using the following script 

******* START OF SCRIPT ******** 

strComputer = "." 

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement") 

Set colItems = objWMIService.ExecQuery( _ 

"SELECT * FROM ServerNetworkProtocolProperty where InstanceName='MSSQLSERVER' and ProtocolName = 'Tcp'",,48) 

For Each objItem in colItems 

Wscript.Echo "-----------------------------------" 

Wscript.Echo "ServerNetworkProtocolProperty instance" 

Wscript.Echo "-----------------------------------" 

Wscript.Echo "InstanceName: " & objItem.InstanceName 

Wscript.Echo "IPAddressName: " & objItem.IPAddressName 

Wscript.Echo "PropertyName: " & objItem.PropertyName 

Wscript.Echo "PropertyNumVal: " & objItem.PropertyNumVal 

Wscript.Echo "PropertyStrVal: " & objItem.PropertyStrVal 

Wscript.Echo "PropertyType: " & objItem.PropertyType 

Wscript.Echo "PropertyValType: " & objItem.PropertyValType 

Wscript.Echo "ProtocolName: " & objItem.ProtocolName 

Next

******* END OF SCRIPT ******** 

This uses the class ServerNetworkProtocolProperty. Similarly, you use the above script and modify it to obtain information for the other server network protocols.

WMI Script for Changing SQL Server 2000 and 2005 passwords

Our domain security policy requires us to change our Domain Account passwords every once in a while. This security feature is a good practice but the change of password requires me to change my SQL Server component startup accounts to be changed also as most of them run under my Domain Account.

Tired of accomplishing this task which requires me to type out my user name and password (twice for the reconfirmation ūüôā )¬† brought forward the need for automating this task. Once I did this, I thought it would be prudent for me to share the same with the SQL community. WMI is quite powerful and a number of tasks can be achieved through it which you shall see in my future blog posts. I have created a category in my blog completely for WMI.

Addition: October 24th, 2011

If you are using SQL Server 2008 R2, then you need to have this fix applied so that SetServiceAccount can work:

2397020¬†FIX: “Access is denied. [0x80070005]” error message occurs in SSCM when you try to change the password of an account of SQL Server 2008 R2 in Windows Vista or in a later version of Windows
http://support.microsoft.com/default.aspx?scid=kb;EN-US;2397020

If you use SetServiceAccount to change the password, then this will result in a SQL Server service restart. The script provided on the Technet Gallery is for changing both the service account and the password. If only the password needs to be changed for the service account, then use SetServiceAccountPassword. This will change the password and will not automatically restart your SQL Server service.

Download the file: pwdchange file and change the name to pwdchange.vbs. This can be executed from command prompt using the following command (provided you have Windows Script Host which almost all Windows Servers have):

cscript pwdchange.vbs

The script has comments written to help you understand the same. This script makes use of the following namespaces:

\root\Microsoft\SqlServer\ComputerManagement (SQL Server 2005)

\root\CIMV2 (SQL Server 2000)

The reason I do not use SQL Server 2000 WMI namespace as it is not installed by default with a SQL Server 2000 default installation. It has to be installed separately. So, I decided to use CIMV2 which has Win32_Service for achieving the same purpose. In the script you would have to provide the following details:

strAccount1 = “Account1” ‘ For alias@domain.com
strAccount2 = “Account2” ‘ For domain\alias naming convention
strAccount3 = “Account1” ‘ To cover any other naming convention
strAlias = “Alias” ‘ Provide the alias
strDomain = “DomainName” ‘ Provide the domain name
strPassword = “Set Password Here” ‘ Put the new password here

Using WMI to Manage SQL Server 2000 Services

There are quite a few blog posts out there to manage SQL Server 2005 services using the :\\.\root\Microsoft\SqlServer\ComputerManagement
However, WMI Admin Provider is not pre-installed for SQL Server 2000. It needs to be installed separately using the WMI Admin Provider Setup available along with the SQL Server 2000 Setup CD under x86\other\wmi folder. 

Sample script to change SQL Server 2005 service startup account and password using WMI:
http://blogs.msdn.com/mwories/archive/2006/11/03/wmi_5F00_change_5F00_password.aspx
MSDN Documentation on Win32_Service class
http://msdn.microsoft.com/en-us/library/aa394418.aspx 

Sample Script to change a SQL Server 2000 instance startup account using root\MicrosoftSQLServer namespace: 

strComputer = "." 

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\MicrosoftSQLServer") 

' Obtain an instance of the the class 

' using a key property value. 

Set objShare = objWMIService.Get("Win32_Service.Name='MSSQL$SQL2000'") 

' Obtain an InParameters object specific 

' to the method. 

Set objInParam = objShare.Methods_("Change"). _ inParameters.SpawnInstance_() 

' Add the input parameters. 

objInParam.Properties_.Item("StartName") = "LocalSystem" 

objInParam.Properties_.Item("StartPassword") = "" 

' Execute the method and obtain the return status. 

' The OutParameters object in objOutParams 

' is created by the provider. 

Set objOutParams = objWMIService.ExecMethod("Win32_Service.Name='MSSQL$SQL2000'", "Change", objInParam) 

' List OutParams 

Wscript.Echo "Out Parameters: "Wscript.echo "ReturnValue: " & objOutParams.ReturnValue