How do you specify a different port number in SQL Management Studio?

Question

I am trying to connect to a Microsoft SQL 2005 server which is not on port 1433. How do I indicate a different port number when connecting to the server using SQL Management Studio?

Facultative

Answer 1

127.0.0.1,6283

Add a comma between the ip and port

Apeman

I did not know that! It has just helped me point the finger at SQL Server Browser as the cause of an issue I am having. Cheers. – Blub

I just chased my tail for an hour trying to figure out how to give the port. Nowhere on MSDN. Stack Overflow to the rescue again! – Format

Shit... big fail MS... THX... and for some reason if you're tunneling: use 127.0.0.1 instead of localhost... SQL MStudio doesnt like it for some reason.. – Fraunhofer

same as you guys, but worse. I had the case some years ago. Did not remember the workaround exactly today as I needed it ... if They use a "different" syntax, why can't they at least mention it in the right places !! – Venola

@mark Agreed, but it should be no surprise really - this is the same company that ignored the rest of the world and decided to use backslashes in file paths. sigh – Common

Odd occurrence: when connecting that way, it appears to connect via the DAC. – Koniology

The colon becomes less meaningful when you connect to an IPv6 address. – Hufnagel

The answer was in here since 2005. Kind of hard to find, but I'm just sayin, it was there...blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx – Eliezer

For connection string "Data Source= '<server name>,<port name>'; Initial Catalog=<instance name>;Integrated Security=true;...;" – Korella

This also works when connecting from SQL Server Profiler. – Tashinatashkent

Answer 2

If you're connecting to a named instance and UDP is not available when connecting to it, then you may need to specify the protocol as well.

Example: tcp:192.168.1.21\SQL2K5,1443

Fruma

thanks very much. this was correct. only I did only need the instance name followed by port. eg INSTANCENAME\INSTANCE,1541 – Travis

Answer 3

Another way is to set up an alias in Config Manager. Then simply type that alias name when you want to connect. This makes it much easier and is more preferable when you have to manage several servers/instances and/or servers on multiple ports and/or multiple protocols. Give them friendly names and it becomes much easier to remember them.

Inaugural

This is a great answer, always better to have a descriptive name that an ip address, especially for new maintainers in your code – Lining

Answer 4

You'll need the SQL Server Configuration Manager. Go to Sql Native Client Configuration, Select Client Protocols, Right Click on TCP/IP and set your default port there.

Engrail

That is good if all the databases you connect to use the same port. Not the case in all environments. – Facultative

Answer 5

Using the client manager affects all connections or sets a client machine specific alias.

Use the comma as above: this can be used in an app.config too

It's probably needed if you have firewalls between you and the server too...

Graeae

Answer 6

On Windows plattform with server execute command:

netstat -a -b

look for sql server processes and find port f.e 49198

Or easier. Connect with dbvisualizer, run netstat -a -b find dbvis.exe process and get port.

Holmun