How to change SQLPort number through a VBScript/SQLServerConfigurationManager ?

How to change SQLPort number through a VBScript/SQLServerConfigurationManager ?

After successful installation of SQL server every organization follows some set of security standard to secure Sql server. One of those standard is changing the sql default port to some other port(1433 is the default port. )

How would I do that: it is very simple you can do it either through script or GUI -SQLServerConfigurationManager.

You have to restart sql services then only the SQL server starts listening new port.

Following script has two sections, one is to change the port to 22001and the other section is to restart sql services (Engine and Agent). Copy the following script in to notepad and save as PortChangeScript.VBS and open commandprompt

C:\ PortChangeScript.VBS    Enter

-------------------------------------------------------------------------------------------------------------------------------------------.

set wmiComputer = GetObject( _

    "winmgmts:" _

    & "\\.\root\Microsoft\SqlServer\ComputerManagement10")

set tcpProperties = wmiComputer.ExecQuery( _

    "select * from ServerNetworkProtocolProperty " _

    & "where InstanceName='MSSQLSERVER' and " _

    & "ProtocolName='Tcp' and IPAddressName='IPAll'")

for each tcpProperty in tcpProperties

    dim setValueResult, requestedValue

    if tcpProperty.PropertyName = "TcpPort" then

        requestedValue = "22001"

    elseif tcpProperty.PropertyName ="TcpDynamicPorts" then

        requestedValue = ""

    end if

    setValueResult = tcpProperty.SetStringValue(requestedValue)

   

next

strComputer = "."

Set objWMIService = GetObject("winmgmts:" _

    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colServiceList = objWMIService.ExecQuery("Associators of " _

   & "{Win32_Service.Name='MSSQLSERVER'} Where " _

        & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

For each objService in colServiceList

    objService.StopService()

Next

Wscript.Sleep 20000

Set colServiceList = objWMIService.ExecQuery _

        ("Select * from Win32_Service where Name='MSSQLSERVER'")

For each objService in colServiceList

    errReturn = objService.StopService()

Next

Wscript.Sleep 20000

strComputer = "."

Set objWMIService = GetObject("winmgmts:" _

    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colServiceList = objWMIService.ExecQuery("Associators of " _

   & "{Win32_Service.Name='MSSQLSERVER'} Where " _

        & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

For each objService in colServiceList

    objService.StartService()

Next

Wscript.Sleep 20000

Set colServiceList = objWMIService.ExecQuery _

        ("Select * from Win32_Service where Name='MSSQLSERVER'")

For each objService in colServiceList

    errReturn = objService.StartService()

Next

-------------------------------------------------------------------------------------------------------------------------------------

Other way: GUI- SQLServerConfigurationManager

Openà SQLServerConfigurationManagerà SQL Server Network Configuration à select the instance à double click on TCP/IP à click on IP Address Tab  à Scroll down and in section IP ALLà Type TCP Port =22001 and delete if you see anything in TCPDynamic Port and then click on apply then click on OK. Finally you have to restart SQL Services.