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...
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!
This was very helpful to me. Thank you very much for the post.
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.
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?
Yes it did -- thanks very much!
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.
I second the above. What a life-saver, and I agree with Adam: why on Earth doesn't it use a colon?
Thanks a lot, so helpful and I think if cant find this page, more that one hour may be wasted.
Thanks to you and Google, i just had to waste 30 secs to get this answer. Thanks a lot!
Thanks, short answer, great results.
Thank you very much! It was exactly what I've searched! Thank you and thank Google! :)
One of those simple-hard-to-find things that no one at microsoft gave its importance. Thanks a lot buddy !!!!
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.
There have been many many people sweating blood due to this idiotic defect - you can define protocol, but not the port, in connection options.
Thank you very very much, you just saved my day.
Thanks a lot. Ben looking for this for a long time.
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.
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).
Thanks, this was frustrating me for a while.
OH MY GOD!! THANK YOU SO MUCH!!!!
Thanks for saving a lot of time.
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.
did not work. but
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.
Another Server:Port person. Thanks fo rtaking the time to post this.
Another poor sap!
Thanks for the post, more helpful than the Help file!
Thanks, worked! No one specified this small thing.
I guess Bill Gates had other uses for his colon... :-P
Thanks for this post. You really helped me out! :-)
Thanks A Lot. Initially tried with colon and Gawked when it didn't work.
Thanks for you posting buddy. It Worked.
A BIG thanks for that. I would have incorrectly guessed colon.
I was one of those poor saps you helped...thanks!
Wonderful. Thanks. Sometimes it is just the little things that count (and save us a lot of time!)
thanx it saved alot of time! 30 seconds.
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...? :)
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
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 !!
Thanx for that! :) Yours came up top of google, and solved my prob in 1 min. :)
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!
Goodness! Why didn't they use ~ or $%^ so all of us would have even more fun :).
This was very helpful ... Thank you ...
Shouldn't this be in BOL? Its not documented anywhere! I'm very thankful this information was available.
Thank you, this is exactly what I needed.
Thanks so much for this, I agree with the other comments, why did the use of a colon go away??
Great post! I was thinking for this overnight. Thanks! =)
Thanks it works like magic
Thank you so much! I appreciate your effort to research and share with us!
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
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
Thanx a lot, man, you saved my life.
I LOVE Microsoft for intuitive settings and great easy-to-find documentation.
Awesome, this post saved me! Thanks a lot!!!!
You and earlz helped a lot ! Thanks !
...and 5 years later it still helps. Thank you!
Still works. Man, you saved my brains.
thnaks this saves mi life
Thanks a lot it took me ages...
You made my day. Thanks!
God bless you :)
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.
It worked! Fantastic! Tks a lot!
Amazing! You saved my day! Thank you!
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.