SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Change the port number for connections to SQL Server


Change the port number for connections to SQL Server

Author
Message
Phil Brammer
Phil Brammer
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 640
okbangas (3/1/2012)
Hi, just a little feedback. Under conclusion, you write:
If the SQL Server Browser service is not running, connection strings will have to specify the port number in order to connect.

As I understand it, this is partially correct. You do not have to specify port number if the server is listening on port 1433, as it does on default instances by default. Apart from this, it is correct, whether you have specified port numbers manually or not.


The SQL Browser service is only for named instances. If you change the default port of the default instance, you will always have to specify the new port number on client connections -- the browser service will not help.

For named instances, if you disable the browser service, then yes, you will also have to specify the port number. For named instances, if the browser service is enabled, then you won't have to specify the port number no matter the port number chosen in the SQL Configuration manager.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35011 Visits: 16670
Phil Brammer (3/1/2012)
[quote]The SQL Browser service is only for named instances. If you change the default port of the default instance, you will always have to specify the new port number on client connections -- the browser service will not help.

That's my experience as well, at least for servers that aren't clustered. We have a default instance on our cluster server that's had its port changed, but we can still connect without specifying the port. I've checked that I haven't got any aliases set up.

John
Mike Dougherty-384281
Mike Dougherty-384281
SSC Eights!
SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)

Group: General Forum Members
Points: 846 Visits: 944
Kelsey Thornton (3/1/2012)
Very well explained but missing one useful point:

"Why would you want to change the port number in the first place?"



Seconded. I also suggest sharing how you mitigate the pain this can cause the developers who write apps that connect to the DB. (don't they all?)

I'd also mention the obvious warning that you shouldn't do this to a production server without considerable impact assessment and CYA/approval from management. Sure you can grep your filesystem for connection strings in sourcecode, but you'll start getting calls from users if your company allows Excel to connect to the DB directly. (you know workbooks multiply like rabbits, increasing the number of documents that would need to be updated)
Phil Brammer
Phil Brammer
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 640
John Mitchell-245523 (3/1/2012)
Phil Brammer (3/1/2012)
[quote]The SQL Browser service is only for named instances. If you change the default port of the default instance, you will always have to specify the new port number on client connections -- the browser service will not help.

That's my experience as well, at least for servers that aren't clustered. We have a default instance on our cluster server that's had its port changed, but we can still connect without specifying the port. I've checked that I haven't got any aliases set up.

John


I suspect there are client aliases set up, or the port has not been changed. SSMS will always try to connect to 1433 for default instances unless you have a client alias set up or you specify the port in the connection string.
SQLDCH
SQLDCH
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3457 Visits: 3401
Thanks for the feedback.

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147752 Visits: 19440
Kelsey Thornton (3/1/2012)
I don't dispute there are many reasons why this might be desirable, or even required.

My point was simply that a little background information into why the port number was being changed might not be out of place in the article.

It's a bit like giving driving directions to #1544 Tree Road, Smalltown, WI and not saying "come to my party at 8 o'clock tonight"


Fair points, but this article was commissioned by me as a short way to show people how to change the port number. The directions in BOL have not been clear to many people.

The "why" article probably should be written, but this wasn't an attempt to cover every aspect of this topic.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35011 Visits: 16670
Phil Brammer (3/1/2012)
[quote]
I suspect there are client aliases set up, or the port has not been changed. SSMS will always try to connect to 1433 for default instances unless you have a client alias set up or you specify the port in the connection string.

No aliases - I've checked that. And if I try to telnet on port 1433 it doesn't work. Not that I'm complaining - I'd rather have something work when it's not expected to than the other way round!

Edit: I suppose the connection could be being made using Named Pipes.

John
Kelsey Thornton
Kelsey Thornton
SSC Eights!
SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)

Group: General Forum Members
Points: 883 Visits: 282
Steve Jones - SSC Editor (3/1/2012)

<snip>
Fair points, but this article was commissioned by me as a short way to show people how to change the port number. The directions in BOL have not been clear to many people.

The "why" article probably should be written, but this wasn't an attempt to cover every aspect of this topic.

If the article had said something like "As the directions in BOL are unclear to many people, here is how to go about changing a port number" then that would also have been fine...

(I'm not bashing the article, it was simply a little "here's some information", that's all.)

Kelsey Thornton
MBCS CITP
CollectInfo
CollectInfo
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 0
Good
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search