February 29, 2012 at 10:07 pm
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
----------------------------------------------------------------------------
March 1, 2012 at 12:11 am
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.
March 1, 2012 at 1:23 am
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
March 1, 2012 at 2:02 am
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...
March 1, 2012 at 2:02 am
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
March 1, 2012 at 2:22 am
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
March 1, 2012 at 2:27 am
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
March 1, 2012 at 2:35 am
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
March 1, 2012 at 7:05 am
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).
March 1, 2012 at 7:16 am
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
March 1, 2012 at 8:07 am
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.
March 1, 2012 at 8:13 am
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
March 1, 2012 at 8:36 am
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)
March 1, 2012 at 9:04 am
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.
March 1, 2012 at 9:37 am
Thanks for the feedback.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply