Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Change the port number for connections to SQL Server Expand / Collapse
Author
Message
Posted Thursday, March 1, 2012 8:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 9:50 AM
Points: 109, Visits: 601
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
Posted Thursday, March 1, 2012 8:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:50 AM
Points: 5,367, Visits: 9,913
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
Posted Thursday, March 1, 2012 8:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:43 AM
Points: 261, Visits: 911
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
Posted Thursday, March 1, 2012 9:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 9:50 AM
Points: 109, Visits: 601
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
Posted Thursday, March 1, 2012 9:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 1,314, Visits: 2,809
Thanks for the feedback.

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

Yeah, well...The Dude abides.
Post #1260263
Posted Thursday, March 1, 2012 10:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:38 PM
Points: 31,018, Visits: 15,453
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
Posted Friday, March 2, 2012 1:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:50 AM
Points: 5,367, Visits: 9,913
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
Posted Friday, March 2, 2012 3:30 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:16 AM
Points: 534, 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
Post #1260699
Posted Friday, March 2, 2012 3:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 2, 2012 3:29 AM
Points: 1, Visits: 0
Good
Post #1260700
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse