<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Dan Hess
»
Change the port number for connections to SQL...
19 posts, Page 2 of 2
««
1
2
Change the port number for connections to SQL Server
Rate Topic
Display Mode
Topic Options
Author
Message
Phil Brammer
Phil Brammer
Posted Thursday, March 01, 2012 8:07 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:56 AM
Points: 109,
Visits: 564
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.
Post #1260168
John Mitchell-245523
John Mitchell-245523
Posted Thursday, March 01, 2012 8:13 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 3:48 AM
Points: 4,422,
Visits: 7,177
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
Post #1260180
Mike Dougherty-384281
Mike Dougherty-384281
Posted Thursday, March 01, 2012 8:36 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 198,
Visits: 680
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)
Post #1260205
Phil Brammer
Phil Brammer
Posted Thursday, March 01, 2012 9:04 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:56 AM
Points: 109,
Visits: 564
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.
Post #1260238
SQLDCH
SQLDCH
Posted Thursday, March 01, 2012 9:37 AM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 2:31 PM
Points: 891,
Visits: 2,265
Thanks for the feedback.
----------------------------------------------------------------------------
My LinkedIn profile -
LinkedIn
Sacramento SQL Server users group -
http://sac.sqlpass.org
My blog -
http://www.expressnetsolutions.com/sqldch
Follow me on Twitter -
@SQLDCH
----------------------------------------------------------------------------
Yeah, well...The Dude abides.
Post #1260263
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Thursday, March 01, 2012 10:23 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 2:54 PM
Points: 31,410,
Visits: 13,726
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
Post #1260304
John Mitchell-245523
John Mitchell-245523
Posted Friday, March 02, 2012 1:38 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 3:48 AM
Points: 4,422,
Visits: 7,177
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
Post #1260663
Kelsey Thornton
Kelsey Thornton
Posted Friday, March 02, 2012 3:30 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Monday, March 04, 2013 4:10 AM
Points: 532,
Visits: 281
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
Post #1260699
CollectInfo
CollectInfo
Posted Friday, March 02, 2012 3:33 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, March 02, 2012 3:29 AM
Points: 1,
Visits: 0
Good
Post #1260700
« Prev Topic
|
Next Topic »
19 posts, Page 2 of 2
««
1
2
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
