Connecting to SQL DB using a different port

Posted At : March 20, 2006 11:25 AM | Posted By : Scott Stroz
Related Categories: Tech Stuff

OK, so maybe I am an idiot. I have spent the last hour or so trying to connect to 2 different SQL Servers (one is SQL 2000 one is SQL 2005) while using the new MS SQL Studio. The problem is that both of these servers are behind a firewall, and port-forwarding is being used. As such, neither connection uses port 1433, the default port for SQL Server.

While I am not a super network guy, I'd like to think I am not an idiot when it comes to networks. That being the case, the first thing I did was specify the server as serverName:portNumber. Imagine my surprise when this did not work. Now imagine my surprise when I discovered the solution: serverName,portNumber.

This also worked when connecting to the SQL 2K server using Enterprise Manager.

I have never heard of using a comma to delimit the server name from the port number, but now I know, and so do you.

For the record, I asked a DBA and a network guy, and neither of them suggested using the comma.

Comments (11) |

Print |

Send |

del.icio.us |

Digg It! |

Linking Blogs | 6025 Views

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)

[Add Comment]

Finally - someone who knows my pain! We had the same exact issue when SQL2005 first came out and just tried the comma by sheer trial and error.

Now the real tough one - has anyone figured out how to copy tables from one database to another without losing Primary Key definitions and defaults?

Justin

# Posted By Justin Schier | 3/20/06 11:52 AM

I usually create an alias in the client network utility and set the port in there on the TCP/IP properties

# Posted By johnb | 3/20/06 12:03 PM

John,

I use that as well in Enterprise Manager.

Though,I was using the free version of SQL Studio, and there was no equivalent, that I could find, for SQL 2005.

# Posted By Scott Stroz | 3/20/06 12:11 PM

Justin,

Assuming you are using DTS, you have to "Copy objects between SQL server databases", then makes sure you turn on "include extended properties" on the last page. You may want to turn off the copying of users and roles, that usually doesn't work for me.

# Posted By dave ross | 3/20/06 1:15 PM

awesome, this was exactly what i needed. I'm using www.bitvise.com tunilier and needed to use port mapping to the remote server. I'd been struggling with this until i found this post.

thanks

# Posted By Robin | 9/13/06 7:03 AM

YES YES , I am glad i found your website. my server was getting hammered on port 1433. i could not find a way to use another port. Thank you so much .

# Posted By Mas | 1/11/07 7:16 PM

Exactly what I was looking for... Thanks....

# Posted By Gavin Mannion | 2/27/07 6:53 AM

Thank you, Thank you, Thank you. You are NOT
an ID10t

# Posted By G Victor | 8/15/07 6:18 PM

I have the exact issue. I have a
further problem though,
I need replication to occur on the
same port as well. When I try to add
my subscribers using server,port SSMS tells
me that you can't use an IP Address or Alias
and must use the instance name. Does anyone
know how to push a subscribtion to another
port?

# Posted By Richard | 10/3/07 10:58 AM

Oy! I spent a couple of hours trying to figure out what I was doing wrong!!

After reading your blog I used a comma et voila! It works!

Thankyouthankyouthankyouthankyouthankyou

# Posted By Rodrigo Munera | 2/23/09 4:54 PM

You save me from a lot of work. Thanks