Connecting SQL Server Management Studio to a non-standard TCP/IP Port

Avian Waves : Blog

Topic: All

Filter by topic: All, Tech, Music, Website, Birds, Politics, Misc, TV, Environment, Smart Car, Science, Sports, Anime

RSS 2.0 Feed

Search Blog

All Any Exact

Archives

• February, 2013 (1)
• January, 2013 (1)
• November, 2012 (1)
• July, 2012 (1)
• May, 2012 (1)
• March, 2012 (2)
• October, 2011 (1)
• January, 2011 (1)
• December, 2010 (1)
• November, 2010 (3)
• September, 2010 (2)
• July, 2010 (1)

[ Show Full Archive ]

Connecting SQL Server Management Studio to a non-standard TCP/IP Port

Posted By Timothy • Topic: Tech

Feb 12, 2007 4:23 PM EDT

I searched for an hour on live.com and google.com to try to figure out how on EARTH you specify a non-1433 port number when you want to establish a remote connection to MS SQL Server using Management Studio. It seemed like such a simple thing. So simple, I guess, that I'm the only person that doesn't know how to do it!

Well I finally found a page that has the answer. It's easy. You add a comma and the port number to the end of the server name.

So if you want to connect to MySqlServer.MyDomain.com on port 3821, you type...

MySqlServer.MyDomain.com,3821

That's it!

Hopefully this will help some other poor sap who didn't realize (like me) that you use the same nomenclature as any normal connection string to specify a port number. So simple. Sheesh.

Ah well. Such is life!

Digg! / del.icio.us / Furl

[ Comments (70) • Trackbacks (0) ] PermaLink

Trackbacks :

Send trackbacks to this URL:

http://www.avianwaves.com/Blog/track.aspx?id=25

In your blog post, you must link back to my post using this URL:

http://www.avianwaves.com/Blog/default.aspx?id=25

Comments :

This was very helpful to me. Thank you very much for the post.

Comment By Sam Lalani At 3/8/2007 7:15 PM EDT

PermaLink

THANKS!!!

Comment By Hank At 4/2/2007 4:17 PM EDT

PermaLink

Thanks! This was great info. I couldnt find it in the help anywhere, and I still can't believe that it doesn't use a colon to separate the server name and the TCP/IP port.

Comment By Adam At 4/4/2007 10:28 AM EDT

PermaLink

Thanx buddy !

Comment By moszi At 4/27/2007 6:01 PM EDT

PermaLink

I'm glad someone else faced the same problem!! granted this was a simple connection string, but really shouldnt it be documented somewhere!!
now i could connect this way to a certain port only when the default tsql tcp endpoint has permissions to public - was it the same in your case?

Comment By adita At 5/3/2007 9:44 AM EDT

PermaLink

Yes it did -- thanks very much!

Comment By Jan At 5/14/2007 4:25 AM EDT

PermaLink

Thanks, this helped me too. I kept using servername:port with no luck, looked for a port setting everywhere I could with no luck, looked through the help files with no info available that I could find, edited the hosts config to specify a port number there with no luck, then googled the problem, found this article, and I'm connected.
You saved my day.

Comment By Me At 6/7/2007 4:37 PM EDT

PermaLink

I second the above. What a life-saver, and I agree with Adam: why on Earth doesn't it use a colon?

Comment By Henri Hein At 10/7/2007 10:41 PM EDT

PermaLink

Thanks a lot, so helpful and I think if cant find this page, more that one hour may be wasted.

Comment By AFShin At 11/14/2007 7:23 AM EDT

PermaLink

Thanks to you and Google, i just had to waste 30 secs to get this answer. Thanks a lot!

Comment By Kristian At 12/7/2007 2:59 AM EDT

PermaLink

Thanks, short answer, great results.

Comment By Enrique Sansores At 2/26/2008 3:01 PM EDT

PermaLink

Thank you very much! It was exactly what I've searched! Thank you and thank Google! :)

Comment By dj shaman At 4/2/2008 4:14 AM EDT

PermaLink

One of those simple-hard-to-find things that no one at microsoft gave its importance. Thanks a lot buddy !!!!

Comment By George Waters At 8/29/2008 4:30 AM EDT

PermaLink

How gay, you have to put a comma, how hard is it to put the port in the connection options dialog?! Thanks mate now I can actually connect to the server, onto the actual work.

Comment By bollo At 10/5/2008 7:27 PM EDT

PermaLink

GREAT, Thanks!!

There have been many many people sweating blood due to this idiotic defect - you can define protocol, but not the port, in connection options.

Comment By Finn, just not Hucleberry At 10/8/2008 5:50 AM EDT

PermaLink

Thank you very very much, you just saved my day.

Comment By Antti Kukkonen At 10/24/2008 4:54 AM EDT

PermaLink

Thanks a lot. Ben looking for this for a long time.

Comment By Jonas At 11/13/2008 4:32 AM EDT

PermaLink

THANKS ALOTTTTTTTTTT.

Comment By sara At 12/1/2008 8:18 AM EDT

PermaLink

It seems as though you should be able to create an alias and connect to SSMS using the alias name only instead of alias ame, port.

Comment By Renee' At 1/12/2009 2:26 PM EDT

PermaLink

Thank you so much! Now if someone can just explain why MS coders didn't write it to accept the typical colon (and if they needed it as a comma in the underlying connection string, just swap it out after the user hits Connect on that Connect to SErver dialog).

:-)

Comment By Deron Dilger At 4/8/2009 7:14 PM EDT

PermaLink

Thanks, this was frustrating me for a while.

Comment By JC At 4/16/2009 12:28 PM EDT

PermaLink

OH MY GOD!! THANK YOU SO MUCH!!!!

Comment By jk At 5/4/2009 11:03 PM EDT

PermaLink

Just wanted to thank!!

Comment By Jimsy At 5/11/2009 12:31 PM EDT

PermaLink

Thanks for saving a lot of time.

Comment By Benny At 9/21/2009 8:15 AM EDT

PermaLink

Well I didn't find the answer here.

For some odd reason though on my computer when specifying a different port number a space is required.
for instance

192.168.0.100,1232/SQLEXPRESS

did not work. but

192.168.0.100,1232 /SQLEXPRESS

does.. it doesn't make sense cause from a connection string either method works.. I really dislike this tool, but there is no replacement for it out there.

Comment By earlz At 10/14/2009 7:51 PM EDT

PermaLink

Another Server:Port person. Thanks fo rtaking the time to post this.

Another poor sap!

Comment By Ken At 11/8/2009 8:09 AM EDT

PermaLink

Thanks for the post, more helpful than the Help file!

Comment By Jay At 12/9/2009 5:26 PM EDT

PermaLink

bo selecta

Comment By Pete At 12/18/2009 11:40 AM EDT

PermaLink

Thanks, worked! No one specified this small thing.

Comment By Ram Kumaran At 12/23/2009 8:22 AM EDT

PermaLink

I guess Bill Gates had other uses for his colon... :-P

Thanks for this post. You really helped me out! :-)

Comment By Evert Meulie At 1/8/2010 6:17 AM EDT

PermaLink

Thanx man. Help me a lot

Comment By NKappa At 1/11/2010 2:42 PM EDT

PermaLink

Thanks A Lot. Initially tried with colon and Gawked when it didn't work.

Thanks for you posting buddy. It Worked.

Comment By Sarav At 1/20/2010 11:07 AM EDT

PermaLink

Thank you very much

Comment By Parker At 2/3/2010 10:13 AM EDT

PermaLink

A BIG thanks for that. I would have incorrectly guessed colon.

Comment By George At 3/1/2010 1:37 PM EDT

PermaLink

I was one of those poor saps you helped...thanks!

Comment By Homer Lim At 3/11/2010 9:17 PM EDT

PermaLink

thanks a lot

Comment By vinsen At 4/23/2010 11:39 AM EDT

PermaLink

Wonderful. Thanks. Sometimes it is just the little things that count (and save us a lot of time!)

Comment By Eric At 5/4/2010 9:22 PM EDT

PermaLink

thanx it saved alot of time! 30 seconds.

Comment By wous At 6/21/2010 8:31 AM EDT

PermaLink

Thanks man! Saved me some time. I can also add that localhost isn't recognized but using 127.0.0.1 works fine, though. Why follow standards...? :)

Comment By colon blow At 7/15/2010 4:26 AM EDT

PermaLink

The probable reason it's not a colon (which I agree is pretty standard) is that it's only a standard in the IPv4 world. In IPv6, colons separate elements in the address so confusing would come in at that point.

And yes, I got here because I had to google it for an IPv4 address as well :P

Comment By lansalot At 9/27/2010 9:49 AM EDT

PermaLink

In Most cases with hosting providers, MS SQL Port is not open but MYSQL is open by Default. This was the case with us.

Just change your SQL Instance to point to the MYSQL port i.e. 3306 If you are not using Mysql on the same box.

Otherwise request your provider to open the port 1433 for you.

SERVER, PORT/INSTANCE worked like a charm !!

Gurdeep

Comment By Gurdeep At 10/1/2010 7:47 AM EDT

PermaLink

Thanx for that! :) Yours came up top of google, and solved my prob in 1 min. :)

Comment By Quigrim At 10/25/2010 12:22 PM EDT

PermaLink

Thanks for posting this Tim! I've been running around like a headless chicken looking for this solution. Like other posters I kept using servername:port# because that looked logical to me but it wouldn't work. I used yours and I was right there!

Comment By Julian At 10/29/2010 7:46 PM EDT

PermaLink

Thank you!!

Comment By John At 12/16/2010 2:05 AM EDT

PermaLink

Goodness! Why didn't they use ~ or $%^ so all of us would have even more fun :).

Comment By Kira Corina At 12/21/2010 7:04 AM EDT

PermaLink

thanks!

Comment By rob At 2/9/2011 5:09 PM EDT

PermaLink

This was very helpful ... Thank you ...

Comment By Venkatesh At 3/11/2011 4:41 PM EDT

PermaLink

Shouldn't this be in BOL? Its not documented anywhere! I'm very thankful this information was available.

Comment By Jerry At 3/15/2011 5:00 PM EDT

PermaLink

Thank you, this is exactly what I needed.

Comment By JohnW At 3/18/2011 5:41 PM EDT

PermaLink

Thanks so much for this, I agree with the other comments, why did the use of a colon go away??

Comment By Mweath At 5/28/2011 1:41 PM EDT

PermaLink

Great post! I was thinking for this overnight. Thanks! =)

Comment By Joie At 7/2/2011 10:26 PM EDT

PermaLink

Thanks it works like magic

Comment By shefiu At 7/15/2011 5:51 PM EDT

PermaLink

Thank you so much! I appreciate your effort to research and share with us!

Comment By Oks At 9/25/2011 1:42 PM EDT

PermaLink

I just set up my first virtual dedicated server with Godaddy. I put SQL2008 on there, and now I want to connect to the database from a remote computer which has server management tool for sql 2005.

I've tried every combo I can think of to connect and it just won't entertain it, refuses with a huge message (error 87)

I thought for the server name I should use
IP/Instance

Am I missing something :(

Btw The firewall on the server is switched off, SQL is running because I can connect when I try on the server, just not remote

Thanks

Comment By Mk At 10/20/2011 8:47 PM EDT

PermaLink

Thanx a lot, man, you saved my life.
<sarcasm>I LOVE Microsoft for intuitive settings and great easy-to-find documentation</sarcasm>.

Comment By Andy At 11/3/2011 6:29 AM EDT

PermaLink

Awesome, this post saved me! Thanks a lot!!!!

Comment By Jay At 11/7/2011 3:54 PM EDT

PermaLink

You and earlz helped a lot ! Thanks !

Comment By Vette Run At 12/14/2011 5:29 AM EDT

PermaLink

Appreciate this. Thanks!

Comment By Dan At 1/9/2012 2:18 PM EDT

PermaLink

...and 5 years later it still helps. Thank you!

Comment By Bev At 1/12/2012 11:41 AM EDT

PermaLink

Still works. Man, you saved my brains.

Comment By Evil_Kitten At 3/20/2012 7:56 AM EDT

PermaLink

thnaks this saves mi life

Comment By Julio Zoto At 7/27/2012 10:45 AM EDT

PermaLink

Thank you

Comment By Kumar At 9/13/2012 9:27 AM EDT

PermaLink

Thanks a lot it took me ages...

Comment By Kinajo At 10/2/2012 9:45 AM EDT

PermaLink

You made my day. Thanks!
God bless you :)

Comment By Son of Liberty At 11/19/2012 8:54 AM EDT

PermaLink

Bless you my friend!!!

Comment By Shaun At 12/26/2012 6:41 PM EDT

PermaLink

This worked...
Thankyou!

This is the solution I was looking for, I can now connect to a remote SQL Server using MS Sql Server Management Studio.

Could not connect on port 1433, don't know why, found a note on my ISP's (discountasp.net) knowledge base:

Some ISPs do not allow port 1433 connections to go through their network. If you find that to be that case, you can configure your SQL client to connect via the alternative port, 14330.

So I put a comma and 14330 after the server name; and MSSS Management Studio connected immediatley.

Thank you!

Comment By Alan At 1/25/2013 1:44 AM EDT

PermaLink

It worked! Fantastic! Tks a lot!

Comment By Rubens At 3/5/2013 2:08 PM EDT

PermaLink

Amazing! You saved my day! Thank you!

Comment By Tiono At 3/18/2013 5:30 AM EDT

PermaLink

This site can give great advantages to advertiser for a product promotion. These paid services always work faster and better for promotional action of a product. Thanks.

Comment By Arik air uk At 5/16/2013 3:16 PM EDT

PermaLink

Thanks very much. Google led me straight here after some head scratching in front of Management Studio. Why wouldn't they just use : like web browsers.

Comment By Ash Smith At 5/20/2013 8:27 AM EDT

PermaLink

Add Comment :

Name :

Email :

URL (Optional) :
Remember Info?

Comments :

Allowed BBCode Tags : LINK (URL), BOLD, ITALIC, UNDERLINE, QUOTE

By posting a comment to this blog, I agree to the blog rules.

This Avian Waves blog is powered by a modified version of Presstopia Blog