Start Powershell failes when connecting to non default port
When connecting in SSMS to a server configured to run under a different port than 1433 and TCP/IP network library only the Start Powershell command failes to connect to the server. The server is registered as SERVERNAME,PORT. The SqlBrowser service is not running.
It seems that the comma needs to be escaped. The next command isn't working within the SQLPS utility as well:
When you change this to:
I'm know the character needs to be escaped in powershell or enoded. The problem is that the connection is failes when is start powershell within SSMS (right click and choose Start Powershell). I'm not ware of escaping any character there.
Upvotes: 2<=-=Jul 18 2008 7:58AM=-=>
Thanks for reporting the issue on PowerShell. This is by design. There are certain characters within PowerShell that have special meaning. This is mostly documented here: http://msdn.microsoft.com/en-us/library/cc281841(SQL.100).aspx
You need to escape the special characters and there are a few options for doing so:
1) Escape with a tick `
2) Wrap the text in double quotes “blah,blah”
3) Use the hex equivalent %2C
4) Use the Encode-SqlName cmdlet
Best of luck,
Group Program Manager
SQL Server Manageability
Well, I’m aware of that, as I described connecting in SQLPS works when you escape the character. It doesn’t work when you select Start Powershell from the context menu within SSMS. And I can’t escape the characters there, or can I?<=-=Jan 29 2009 8:48PM=-=>
Hi, Robert! This is a bug and we will fix this in the next major releases.
Mingwu<=-=Jan 29 2011 10:07PM=-=>
Greetings from SQL Server Manageability Team.
I have installed SQL Server of SQL 11. By default SQL Server listens to Port 1433.
I have changed SQL Server to listen on port 1434 by changing the port in SQL Server Configuration Manager.
I have queried the following T-SQL which returns the port:
DECLARE @tcp_port nvarchar(5)
@rootkey = �HKEY_LOCAL_MACHINE�,
@key = �SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP�,
@value_name = �TcpPort�,
@value = @tcp_port OUTPUT
It returned 1434 which indicates the instance is listening on Port 1434.
I started the “Start Powershell” command from Object Explorer and it launched the powershell window properly.
I am unable to repro the issue.
Could you please provide more steps in reproducing the issue?
Thank you for you’re effort. I can confirm this works as expected in SQL 11. I have several instances running on one machine. One of them is on port 1600. When I connect to this server in SSMS like , and then start PowerShell from the context menu it will connect. It will also work with SQL 2008 R2. As I recall this was reported with SQL 2008. I don’t have a SQL 2008 installation at the moment to confirm it works there as well with the latest servicepack.
Jim Higgins commented
I see the same issue when launching replication manager from SSMS. If the database connection is server,port, many utilities can't be used (Replication Monitor, and similar issues when configuring AlwaysOn availability groups. It would be nice if SSMS could handle connection to static ports, with browser disabled, for improved\increased security. Using a comma as the port separator in the server name works with many items, but not all.