SSMS – Specify Port Number while Connecting to Remote SQL Server

Sometimes SQL Server is configured to use a different Port Number than the Default Port Number (1433 is the Default Port Number for SQL Server). And when SQL Server is configured with a Different Port Number, often times we can directly connect to the server by just specifying the name of the server for the Server Name in Connect to Server dialog box. But sometimes, it doesn’t allow connection and requires us to provide the Port Number as part of the Connection Information.

Last week one of my colleagues came to me requirement wherein, he mentioned that, he wanted to connect to a remote SQL Server which is configured with a different Port Number and he was not able to connect directly by just specifying the Server Name in SQL Server Management Studio (SSMS), and he wanted to know if there is a way to specify Port Number in SSMS while connecting to Remote SQL Server.

When I was searching for how to go about doing this, I found a MySQL sample Connection String on www.ConnectionStrings.com, which had Port Number specified as part of the Connection Information. The Connection String looked something like this:

Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

When I looked at this, I thought I can try something similar while connecting to remote SQL Server in SSMS and what I did is, I specified Server Name and Port Number separated by a Comma under the Server Name as shown below.

 

Since I didn’t have a SQL Server configured with a different Port Number, I just connected by explicitly specifying Default Port Number. But this approach works specifically for different Port Number. Below is a screenshot of how the servers look like in the Object Explorer Details when connected With and Without Specifying Port Number.

 

Note: I have tried out this approach to connect to SQL Server 2008 R2 instance. This approach does not work with a Named Instance of SQL Server.

[Updated : 2012-06-04]: SQL Browser Service was running while performing the above steps (Updated for more clarify based on comment from Sachin on my Submission of this Tip on Just Learned here).

Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on May 28, 2012, in Issues 'N' Resolution, SQL Client Tools, SQL Server, SSMS, SSMS Tips, Tips 'N' Tricks and tagged , , , , , , , . Bookmark the permalink. Leave a comment.

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: