SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Change the TCP Port on SQL Server 2012 Core Edition


How to Change the TCP Port on SQL Server 2012 Core Edition

Author
Message
shew
shew
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2584 Visits: 954
Can anyone tell me how to change the TCP port on SQL Server 2012 Core Edition?

SQL Server Configuration Manager (i.e., SQLServerManager11.msc) is normally used for configuring the TCP port in SQL Server. However, since the Core Edition does not have any Start Menu entries, I am having trouble getting it to run. I found two copies of SQLServerManager11.msc on the system:

1) C:\Windows\System32\SQLServerManager11.msc
2) C:\Windows\SysWOW64\SQLServerManager11.msc

But both copies generate an error when executed:

PS C:\> C:\Windows\System32\SQLServerManager11.msc
Program 'SQLServerManager11.msc' failed to run: Class not registeredAt line:1 char:1
+ C:\Windows\System32\SQLServerManager11.msc
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~.
At line:1 char:1
+ C:\Windows\System32\SQLServerManager11.msc
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ResourceUnavailable: (Smile [], ApplicationFailedException
+ FullyQualifiedErrorId : NativeCommandFailed


PS C:\> C:\Windows\SysWOW64\SQLServerManager11.msc
Program 'SQLServerManager11.msc' failed to run: Class not registeredAt line:1 char:1
+ C:\Windows\SysWOW64\SQLServerManager11.msc
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~.
At line:1 char:1
+ C:\Windows\SysWOW64\SQLServerManager11.msc
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ResourceUnavailable: (Smile [], ApplicationFailedException
+ FullyQualifiedErrorId : NativeCommandFailed




Jay Beta
Jay Beta
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 120
You can't use the gui configuration management tool since its a core install. Use powershell instead. Use the following example below that I found on mdsn's blog by Dan Benediktson. Just replace 3344 with whatever port number you want to use. FYI, this is for the "ipall" config setting.

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

Here is a link to the original post:
http://blogs.msdn.com/b/sql_protocols/archive/2008/08/29/configuring-sql-protocols-through-windows-powershell.aspx
shew
shew
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2584 Visits: 954
Jay Beta (3/22/2013)
Use powershell instead.


Thanks. That helped, but I am still unable to connect. Before I made the recommended changes, based on the link in the prior post (http://blogs.msdn.com/b/sql_protocols/archive/2008/08/29/configuring-sql-protocols-through-windows-powershell.aspx), this was the output from PortQry.exe on a remote machine:

.\PortQry.exe -e 1434 -p UDP -n server_name

Querying target system called:

server_name

Attempting to resolve name to IP address...


Name resolved to 192.168.1.140

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

Server's response:

ServerName server_name
InstanceName instance_name
IsClustered No
Version 11.0.3000.0
tcp 49188 <-- The default TCP dynamic port

5 - 2 1

==== End of SQL Server query response ====

UDP port 1434 is LISTENING


Here are the TCP changes that I made:

# Display machine information

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

$MachineObject

+++ Begin screen output +++
ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services : {MSSQL$instance_name, SQLAgent$instance_name, SQLBrowser}
ClientProtocols : {np, sm, tcp}
ServerInstances : {instance_name}
ServerAliases : {}
Urn : ManagedComputer[@Name='server_name']
Name : server_name
Properties : {}
UserData :
State : Existing
--- End screen output ---

# Display which protocols are enabled; be aware that you need to update the instance name to make it valid in the next command

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

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

# Display the properties of the "IPAll" IP Address:

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

+++ Begin screen output +++
Parent : Microsoft.SqlServer.Management.Smo.Wmi.ServerProtocol
IPAddressProperties : {TcpDynamicPorts, TcpPort}
IPAddress : 0.0.0.0
Urn : ManagedComputer[@Name='server_name']/ServerInstance[@Name='instance_name']/ServerProtocol[@Name='Tcp']/IPAddress[@Name='IPAll']
Name : IPAll
Properties : {}
UserData :
State : Existing
--- End screen output ---

# Set Server Server to listen on TCP port 1433

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

$tcp.alter()

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

+++ Begin screen output +++
Parent : Microsoft.SqlServer.Management.Smo.Wmi.ServerProtocol
IPAddressProperties : {TcpDynamicPorts, TcpPort}
IPAddress : 0.0.0.0
Urn : ManagedComputer[@Name='server_name']/ServerInstance[@Name='instance_name']/ServerProtocol[@Name='Tcp']/IPAddress[@Name='IPAll']
Name : IPAll
Properties : {}
UserData :
State : Existing
--- End screen output ---


The PortQry.exe output on a remote machine looks correct now, but I am still unable to connect from the remote machine:

PS C:\cron\dba> .\PortQry.exe -e 1434 -p UDP -n server_name

Querying target system called:

server_name

Attempting to resolve name to IP address...


Name resolved to 192.168.1.140

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

Server's response:

ServerName server_name
InstanceName instance_name
IsClustered No
Version 11.0.3000.0
tcp 1433 <-- This is the TCP port that I am trying to use

5 - 2 1

==== End of SQL Server query response ====

UDP port 1434 is LISTENING


Here is the connection failure message:

sqlcmd.exe -S server_name\instance_name -U sa -P sql_password -Q "select getdate()"

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..




shew
shew
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2584 Visits: 954
My bad. I forgot to bounce SQL Server after making the TCP change.

get-service *sql*

+++ Begin screen output +++
Status Name DisplayName
------ ---- -----------
Running MSSQL$instance_... SQL Server (instance_name)
Stopped SQLAgent$instan... SQL Server Agent (instance_name)
Running SQLBrowser SQL Server Browser
Running SQLWriter SQL Server VSS Writer
--- End screen output ---

stop-service "SQL Server (instance_name)"
get-service *sql*

+++ Begin screen output +++
Status Name DisplayName
------ ---- -----------
Stopped MSSQL$instance_... SQL Server (instance_name)
Stopped SQLAgent$instan... SQL Server Agent (instance_name)
Running SQLBrowser SQL Server Browser
Running SQLWriter SQL Server VSS Writer
--- End screen output ---

start-service "SQL Server (instance_name)"
PS C:\cron\dba> get-service *sql*

+++ Begin screen output +++
Status Name DisplayName
------ ---- -----------
Running MSSQL$instance_... SQL Server (instance_name)
Stopped SQLAgent$instan... SQL Server Agent (instance_name)
Running SQLBrowser SQL Server Browser
Running SQLWriter SQL Server VSS Writer
--- End screen output ---


The connection now works:

sqlcmd.exe -S server_name\instance_name -E -Q "select getdate()"

-----------------------
2013-03-23 08:06:11.750

(1 rows affected)




Jay Beta
Jay Beta
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 120
Awesome!!!! I'm glad you got it up and running. Good point about bouncing SQL, LOL...
Eric Zierdt
Eric Zierdt
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 304
awesome tip. I just started playing with this; i there a parameter that can be set in the config file to do this at install?
As long as I'm asking, is there a parameter for default data directory? I couldn't find a list of all available parameters, just some example scripts when I tried googling it.

Thanks
shew
shew
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2584 Visits: 954
Eric Zierdt (3/25/2013)
i there a parameter that can be set in the config file to do this at install?


This may help. Not sure. http://msdn.microsoft.com/en-us/library/ms144259.aspx



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search