How to configure XP ICF for SQL Server? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to configure XP ICF for SQL Server?

I want to know the correct way to configure XP ICF for SQL Server in Windows XP and Windows XP SP1 machines. Currently, I do the following steps: 1. Go to Network Connections
2. Go to Advanced tab of a connection
3. Check the Internet Connection Firewall
4. Go to the Advanced Settings
5. Add SQL Server service: Description of Service: mssqlserver
Name of IP Address: 127.0.0.1
External Port: 1433 TCP
Internal Port: 1433 My understanding is that: 1. Description of Service can be anything.
2. Name of IP Address not sure why 127.0.0.1
3. 1433 since it is the default port but don’t whether it should be for both External and Internal Port
4. Don’t know why TCP
5. Don’t know what External Port and Internal Port mean Thank you for any help.
The port that the client uses to communicate with an instance of SQL Server is the same port that the instance uses to listen for client requests. SQL Server listening on 127.0.0.1: 1362.
The four-digit number after the colon is the port that SQL Server is listening on for the IP address, which is specified to the left of the colon. SQL Server is a Winsock application that communicates over TCP/IP using the sockets network library. The SQL Server listens for incoming connections on a particular port; the default port for SQL Server is 1433. The port doesn’t need to be 1433, but 1433 is the official Internet Assigned Number Authority (IANA) socket number for SQL Server. When the client establishes a TCP/IP connection, a three-way handshake is done. The client opens a source port and sends traffic to a destination port, which by default is 1433. The client source port in use is random, but is greater than 1024. By default, when an application requests a socket from the system for an outbound call, a port between the values of 1024 and 5000 is supplied. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
1. Yes it can be anything
2. It should be your computer IP address that other people can reach you on.
3. 1433 is default. If your computer can be reached from the internet I would problably change to something else like.
4. TCP is the protocol used, leave it as it is
5. You can open up one port externaly, in otherwords outside the firewall and have the service listen on another port internaly, behind the firewall. Usually keep them the same.
Hi Satya, I try to understand the communication between client and server. I have installed TCPView from sysinternals. I see something like this: Process Protocol Loca Address Remote Address State
sqlservr.exe: 1000 TCP peter: 1433 charles: 1413 ESTABLISHED
sqlservr.exe: 1000 TCP peter: 1433 peter: 0 LISTENING I believe the 1st line means sqlservr.exe is running with process identifier 1000 using TCP Protocol. The machine peter has estatblished a communication link using its port 1433 with the machine charles which is using port 1413. I don’t understand the 2nd line. Thank you for any help.

Hi Argyle, Can you explain little bit more on external and internal port?
Thank you.
It’s just a function in the firewall that can translate your port to another port outside the firewall if you want. For example you can have your sql server running on 1433 but tell it to be 1555 outside the firewall and thus clients connect to 1555. When it comes to the Windows firewall and SQL Server have a look at: How to use a script to programmatically open ports for SQL Server to use on systems that are running Windows XP Service Pack 2
http://support.microsoft.com/default.aspx?scid=kb;EN-US;839980
Hi Argyle,<br /><br />Thank you for the clarification. If I understand correctly, it means I can configure the ICF (before XP SP2), <br /><br />the internal port: 1433<br />the external port: 1000<br /><br />So, on the client side, it needs to set use port 1000 in the Client Network Utility (I forget what will use the setting from this) or in ODBC Data Source Administrator.<br /><br /><br />Thank you for the link for the article 839980. I have been waiting for this article and 841249 and I believe it is just out last night. How can you know this is out so quick?[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] I know this from your post and also from the email from kbAlertz.com.<br /><br />
I’m playing around with the Windows Firewall of the XP SP2 RC2 in my server machine (testxp). Now, the Enterprise Manager in my client machine is showing 2 testxp entries when I try to register the testxp server. How can I fix this in my client machine? I have tried on another client machine and only 1 entry is showing so this is something wrong in my 1st client machine which I have been using to connect to testxp when I’m playing with the Windows Firewall of the testxp.[<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />]<br /><br /><br />I have found out why 2 entries. Alias has been setup somehow.<br /><br /><br /><br />Found out that the alias is created due to changeing Network libraries in ODBC Data Source Administrator.
I have tried the instruction from Article 841252 to configure Windows Firewall for TCP and UDP in a Windows XP SP2 RC2 machine with SQL Server 2000. I have added a port named Port 1433 which has Port number 1433 and TCP. I have added another port named Port 1434 which has Port number 1434 and UDP. I have checked these 2 ports and the File and Printer Sharing. On the client machine, I only enable TCP/IP protocol in the SQL Server Client Network Utility. I cannot add a System DSN that connects the SQL Server of the Windows XP SP2 RC2 machine in the ODBC Data Source Administrator. On the same client machine, . I can add a System DSN that connects SQL Server 2000 of a Windows XP SP1 machine in the ODBC Data Source Administrator. The Windows XP SP1 has been enabled with Internet Connection Firewall. I have added a service test which has external port 1433 TCP and internal port 1433.

http://www.winnetmag.com/SQLServer/Article/ArticleID/42854/42854.html &http://www.microsoft.com/sql/techinfo/administration/2000/security/winxpsp2faq.asp for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hi Satya, Did you see anything wrong with the way I set up the Windows Firewall? I’m following article 841252 instead of 841251.
I believe the http://support.microsoft.com/default.aspx?kbid=841252 KBA refers the steps to be performed and don’t see any issues. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I have installed these utilites: TCPView and Active Ports. On the server which is XP SP2 RC2, I don’t see any entries of port 1433 or port 1434 or ms-sql-s or ms-sql-m in both utilities. On another server which is XP SP1, I will see (regardless whether ICF is enabled or not) In TCPView
Process Protocol Local Address Remote Address State
sqlservr.exe:1792 TCP PETER-XP-PRO:ms-sql-s PETER-XP-PRO:0 LISTENING
sqlservr.exe:1756 UDP PETER-XP-PRO:ms-sql-m *:* In Active Ports (there is nothing in Remote IP, Remote Port columns)
Process PID Local IP Local Port State Protocol Path
sqlservr.exe 1792 0.0.0.0 1433 LISTEN TCP C:pROGRA~1MICROS~4MSSQLinnsqlservr.exe
sqlservr.exe 1756 0.0.0.0 1434 LISTEN UDP C:pROGRA~1MICROS~4MSSQL$~1innsqlservr.exe I’m not sure whether I have setup ICF correctly or not in the 2nd server. I think if ICF is enabled, I should not see those entris in both TCPView and Active Prots. Thank you for any help. Addition info: I have even disabled the Windows Firewall by marking the Off button but I still cannot access 1433 and 1434.

Haven’t used those tools but if you type "netstat -nao" in a command prompt you can see which ports a specific process_id uses. Check which process_id SQL Server has via taskmanager and verify that it actually uses port 1433 and not something else (like if the port is set to dynamic in server network utility).
I checked the SQL Server Logs and the SQL Server is only listening to Shared Memory and Named Pipes. But I found that the SQL Server was listening to TCP, Shared Memory and Named Pipes in SQL Server Logs before XP SP2 RC2 was applied. I have doubled checked that Server Network Utility and registry have TCP and 1433.
I believe I found out what happen. After I install XP SP2 RC2 on SQL Server 2000 Personal Edition with SP2, TCP/IP and UDP ports are automatically disabled according to Event Viewer log. I will install SP3a to see whether it will resolve this issue.
Installing SP3a has resolved my issue. I have also found on MS that this will happen to SQL Server w/o SP3 or SP3a but the log should be an error entry instead of information entry in the Event Viewer logs.
]]>