473

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?

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
Brettski
  • 19,351
  • 15
  • 74
  • 97

6 Answers6

970

127.0.0.1,6283

Add a comma between the ip and port

gunr2171
  • 16,104
  • 25
  • 61
  • 88
Nescio
  • 27,645
  • 10
  • 53
  • 72
  • 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. – stephen Jun 21 '11 at 15:47
  • 14
    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! – Christo Jun 28 '11 at 12:23
  • 23
    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.. – David Apr 02 '12 at 15:28
  • 1
    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 !! – oldbrazil Jul 13 '12 at 17:09
  • 5
    @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* – Scott Jan 09 '13 at 15:31
  • Odd occurrence: when connecting that way, it appears to connect via the DAC. – mbourgon Mar 05 '13 at 22:01
  • 6
    The colon becomes less meaningful when you connect to an IPv6 address. – EpicVoyage Jul 30 '13 at 15:51
  • 1
    The answer was in here since 2005. Kind of hard to find, but I'm just sayin, it was there...http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx – Flat Cat Jan 29 '15 at 18:43
  • 1
    For connection string "Data Source= ','; Initial Catalog=;Integrated Security=true;...;" – sansy Mar 02 '17 at 19:48
  • This also works when connecting from SQL Server Profiler. – brentlightsey May 18 '18 at 15:15
88

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

Michael
  • 8,362
  • 6
  • 61
  • 88
James
  • 3,312
  • 1
  • 21
  • 15
  • 3
    thanks very much. this was correct. only I did only need the instance name followed by port. eg INSTANCENAME\INSTANCE,1541 – mikoop Nov 03 '17 at 00:37
10

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.

Pang
  • 9,564
  • 146
  • 81
  • 122
mattlant
  • 15,384
  • 4
  • 34
  • 44
  • This is a great answer, always better to have a descriptive name that an ip address, especially for new maintainers in your code – ENDEESA May 03 '20 at 08:36
5

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.

Mike
  • 5,181
  • 3
  • 25
  • 19
  • 3
    That is good if all the databases you connect to use the same port. Not the case in all environments. – Brettski Dec 06 '13 at 05:31
2

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...

gbn
  • 422,506
  • 82
  • 585
  • 676
-8

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.

Milen
  • 8,697
  • 7
  • 43
  • 57
guest
  • 27
  • 1