March 24, 2011 at 7:43 pm
Hey all,
I have been searching all over for a specific port number, but I simply cannot find it (or it is not directly stated).
In 2008 management studio, you can view the registered server list for all the servers that you can connect to. After loading up this list, Management studio will poll each server in the list one by one in ascending order. Once it has determined the SQL server status, the icon will change and turn from white to green, indicating that SQL server is up and running.
What port needs to be open for this communication to occur? I have been referencing the following technet article, but unfortunately, it does not say directly.
http://technet.microsoft.com/en-us/library/cc646023.aspx
Any help would be appreciated as I am trying to give my network engineer the proper information. I am confirming with my network engineer to make sure, but right now we have 1433 TCP (not UDP) and 1434 TCP/UDP.
Thanks in advance!
March 24, 2011 at 9:06 pm
Typically port 1434 UDP is the port that SQL Server listens on for requests to SQL Server about configuration. The other port is used to connect to SQL Server.
The other port is depending on how it was set up. The easiest way to get to this information is to look in SQL Server Configuration manager and look at the protocols. But you can also get this information from the Errorlogs in SQL Server when the SQL Server starts up it indicates which port it is listening on.
So execute sp_readerrorlog and it will read the current one. Look at the line with listening on and you will find the other port.
March 24, 2011 at 11:51 pm
UDP 1434 needs to be open and can't be changed.
TCP 1433 should be changed in most cases to a different port number. This port can be changed but whichever port you decide on for the TCP stack also needs to be open.
To connect to SQL server without specifying a port number, both ports are used. If a port number is specified then only the TCP port is used in the connection, but the 1434 port will still be listening and available.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2011 at 12:07 am
CirquedeSQLeil (3/24/2011)
TCP 1433 should be changed in most cases to a different port number. This port can be changed but whichever port you decide on for the TCP stack also needs to be open.
Why should 1433 be changed in most cases? (Microsoft doesn't think so according to a recent blog: http://blogs.msdn.com/b/psssql/archive/2011/03/17/to-1433-or-not-to-1433-that-is-the-question.aspx)
However they do think non-default instances should not be on 1433, but I suspect most people only have a default instance.
March 25, 2011 at 8:56 am
i thought avoiding the use of 1433 was to avoid brute force attacks to discover passwords....script kiddies or worse.
Lowell
March 25, 2011 at 10:31 am
UMG Developer (3/25/2011)
CirquedeSQLeil (3/24/2011)
TCP 1433 should be changed in most cases to a different port number. This port can be changed but whichever port you decide on for the TCP stack also needs to be open.Why should 1433 be changed in most cases? (Microsoft doesn't think so according to a recent blog: http://blogs.msdn.com/b/psssql/archive/2011/03/17/to-1433-or-not-to-1433-that-is-the-question.aspx)
However they do think non-default instances should not be on 1433, but I suspect most people only have a default instance.
I think the reasons he has given are weak. If the hacker is on your network you have big problems as it is. An extra layer of defense slows down his attack - why make it easier?
I'm also not certain that the opinion of one PSS blogger represents Microsoft recommendation and best practices either.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2011 at 10:34 am
Lowell (3/25/2011)
i thought avoiding the use of 1433 was to avoid brute force attacks to discover passwords....script kiddies or worse.
That is one of the benefits - extra layer of defense.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2011 at 10:43 am
I think that blog is OK for internal applications, but it doesn't think through the complete issue. While it's true that a port scan or network trace can easily find other ports, that leaves out that many companies, especially larger ones, can detect port scans and use that to find hackers. ISPs used to detect this and shut down IPs that were scanning.
I have changed the default port for SQL Servers that were exposed to the Itnernet for some reason, along with having some network check to look for port scans.
If you are internal, no reason to to this. UDP 1434 is often shut because of Slammer, which can find those instances. You can scan 1433, but you won't find instances on other ports. You can potentially look for 1434 and get a list of instances for that IP, but it might not work if the port is blocked for that machine.
Short answer: no reliable way to be sure you have all SQL instances unless you have domain admin access to the registry of all machines and can check for installs. Best way is to know which machines are important, make a quick connect on 1433, and update your status.
March 25, 2011 at 10:54 am
Back on topic... I received confirmation from my network engineer that 1434 TCP/UDP bidirectional is open, so I should be seeing the status indicator of my servers turn to a green icon. It is not. So there must be another port number involved. Any thoughts?
On a side note, there is no need for that final layer of protection if you have a sufficient number of layers already present. There is no level of security that a smart man/woman can't figure their way past. If they are willing to spend that much time and effort to get that far into your network, one more layer will not matter.
I have heard the blog information previously when I have attended SQL conferences... actual members from Microsoft. If your Network/Security Engineer is doing his/her job correctly, the port SQL is listening on should not matter.
If anyone has any ideas about the port number, please let me know. netstat -an isn't giving me any useful information.
March 25, 2011 at 11:06 am
Are you sure the server is up? If you know the server, check the errorlog for the instance. At the beginning it lists where SQL Server is listening.
March 25, 2011 at 11:09 am
The status indicator will be affected by permissions. The user making the connection needs to have adequate permissions on the Server. Are you able to remote to the server and see the status change when connected at the server level?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2011 at 11:12 am
I use windows authentication and I am a domain administrator. My domain account also has sysadmin privileges on SQL server given that I am the DBA.
I can double click on the server in my registered servers list, connect with the machine, and execute anything I want.. sql agent jobs, run queries, delete databases, etc.. Its just the status indicator.
March 25, 2011 at 11:15 am
do you see the status indicator display properly when on the server itself (rather than using ssms from your desktop)?
I have to find some notes on it, but am certain that DCOM was causing this issue for me a couple of years ago.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2011 at 11:16 am
We use VM templates for our SQL servers, so all servers are set up identical (all 78 that I manage). All are configured with default SQL configurations regarding the port numbers.
I am convinced it something with the firewall.
March 25, 2011 at 11:18 am
When on the server itself, the status icon displays properly and I can control the SQL service when on the box.
When on the main management db server under registered servers, when I right click on my server and go to "service control", all the options are greyed out. For two of the servers (out of the 78) where I can see the green icon, the service control options are not greyed out. Go figure eh?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply