Change the port number for connections to SQL Server

  • SQLDCH

    SSChampion

    Points: 11467

    Comments posted to this topic are about the item Change the port number for connections to SQL Server

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

    Yeah, well...The Dude abides.
  • okbangas

    SSChampion

    Points: 11773

    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.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Kelsey Thornton

    SSCrazy

    Points: 2157

    Very well explained but missing one useful point:

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

    Kelsey Thornton
    MBCS CITP

  • heymiky

    SSCarpal Tunnel

    Points: 4795

    One reason to change the port number is if your named instance needs to use kerberos delegation there's many others when you'd need to do it. My personal preference is to set all named instances using static ports I also tend to use port numbers starting from 14330 upwards for all other instances on the server...

  • John Mitchell-245523

    SSC Guru

    Points: 148258

    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?"

    (1) Security, to prevent attacks on the default port. Read about the SQL Slammer virus that hit nine years ago.

    (2) You may have more than one instance on the same computer. They can't all listen on the same port.

    John

  • Kelsey Thornton

    SSCrazy

    Points: 2157

    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"

    Kelsey Thornton
    MBCS CITP

  • John Mitchell-245523

    SSC Guru

    Points: 148258

    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"

    I see what you're getting at now, Kelsey. Anyway, see you at the party tonight! Is WI Wisconsin?

    John

  • Kelsey Thornton

    SSCrazy

    Points: 2157

    John Mitchell-245523 (3/1/2012)


    I see what you're getting at now, Kelsey. Anyway, see you at the party tonight! Is WI Wisconsin?

    John

    Probably - I just used two letters I thought were probably a US state (so the US readers would feel at home)

    😀

    Kelsey Thornton
    MBCS CITP

  • okbangas

    SSChampion

    Points: 11773

    John Mitchell-245523 (3/1/2012)


    Security, to prevent attacks on the default port. Read about the SQL Slammer virus that hit nine years ago.

    I tend to disagree. Yes slammer hit 1433, but in my humble opinion you do not change port numbers due to security issues. If you do read about slammer, the patch for the slammer security issue was released more than six months earlier. Slammer did much damage to to unpatched systems, not due to default port numbers.

    There are still valid reasons for specifying port numbers. The two most common cases I've seen is routing access through a firewall (were all ports are closed by default), and access to named instances from clients who don't understand instances (by using IP and port number).



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • John Mitchell-245523

    SSC Guru

    Points: 148258

    okbangas (3/1/2012)


    I tend to disagree. Yes slammer hit 1433, but in my humble opinion you do not change port numbers due to security issues. If you do read about slammer, the patch for the slammer security issue was released more than six months earlier. Slammer did much damage to to unpatched systems, not due to default port numbers.

    There are still valid reasons for specifying port numbers. The two most common cases I've seen is routing access through a firewall (were all ports are closed by default), and access to named instances from clients who don't understand instances (by using IP and port number).

    It's true that there was already a patch for Slammer, but some people prefer to add that extra layer of security as well. What would have happened if another worm that there was no patch for had come along exploiting 1433? I know that not everybody believes in security by obscurity (changing names and numbers away from default or descriptive values in order to mask their purpose), but it's a genuine school of thought.

    I happen to think that changing ports for security reasons is a good idea, but of course I understand that not everybody has the same point of view. I only pointed out that this is a reason why one might wish to change the port.

    Thanks for also pointing out those other reasons.

    John

  • Phil Brammer

    Ten Centuries

    Points: 1135

    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

    SSC Guru

    Points: 148258

    Phil Brammer (3/1/2012)


    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

    SSCrazy

    Points: 2764

    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

    Ten Centuries

    Points: 1135

    John Mitchell-245523 (3/1/2012)


    Phil Brammer (3/1/2012)


    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

    SSChampion

    Points: 11467

    Thanks for the feedback.

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

    Yeah, well...The Dude abides.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply