SQL Server Performance Forum – Threads Archive

IP Addresses of TCP/IP Properties

I want to understand the IP Addresses tab of the TCP/IP Properties in SQL Server Configuration Manager. The following settings are from the default named instance of SQL Server 2005 Express (computernameSQLEXPRESS) on a Windows XP SP2 machine with IP Address 192.168.1.151. IP1 has the following settings:
Active: Yes
Enabled: No
IP Address: 192.168.1.151
TCP Dynamic Ports: 0
TCP Port: 1433 IP2 has the following settings:
Active: Yes
Enabled: No
IP Address: 127.0.0.1
TCP Dynamic Ports: 0
TCP Port: IPAll
TCP Dynamic Ports: 1121
TCP Port: Is the instance using Dynamic Port or Static Port? Normally, I will see blank for TCP Port when TCP Dynamic Ports is 0 in IP1 but in this case, TCP Port has 1433. My guess is that the instance is using Dynamic Port and the port is 1121.

---

I never work with express, but in any Server the default port is 1433.
If you want to change it, in sql properties (EM or Management Studio) there is a place to do it.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

Hi Luis, Thanks for your reply. My question actually applies to any edition of SQL Server 2005 not just Express. The Server Network Utility of SQL Server 2000 can only specify a Default Port for TCP/IP. But in SQL Server 2005, that settings has been splitted into ip1, ip2, and ipall and I want to understand how they work.

---

Keep in mind that the ip-address you get to see is stored in the registry !
It is the ip-address that was in use when you installed sqlserver !
Using another networkcard may get you a new ipaddress whilst the registrykey will still contain the old one.
Your sqlserver will work, only the ipinfo is wrong.
The portnumber is accurate.

---

Hi alzdba, Which registry contains the ip address in use when SQL Server is installed? I’m also trying to understand the registry used by SQL Server.

---

Hi,
I don’t remember the exact but it is located in HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpip Regards Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com

---

Here’s where I found it
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServerSuperSocketNetLibTcpIP1]
"Enabled"=dword:00000000
"Active"=dword:00000001
"TcpPort"=""
"TcpDynamicPorts"="0"
"DisplayName"="Specific IP Address"
"IpAddress"="***.***.***.***"

---

KBAs
http://support.microsoft.com/kb/823938
http://support.microsoft.com/kb/841394 Fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Hi alzdba, I found the registry you mentioned: [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServerSuperSocketNetLibTcpIP1] but I also found another registry [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerSQLEXPRESSMSSQLServerSuperSocketNetLibTcp] which does not have any subkeys but have the string value TcpPort. Any idea what is this registry for?

---

– are you running both sqlexpress and sqlserver2005 on the same machine ? – btw : did you check the KB Satya mentioned [?] [^]

---

KB823938 defines the behaviour of this issue and more information on how you have to control the settings, IMHO its not good to attach on registry without a testing. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Hi alzdba, Yes, I’m running the following SQL Server instances in the same machine: SQL Server 2005 Standard Edition
SQL Server 2005 Express
SQL Server 2000

---

Hi Satya, Thanks for those MS article links.

---

As you will have noticed when reading the KB Satya refered to, you should find a set of reg-keys per sqlinstance.
…MSSQL.1.. for your first SQL2005 instance
…SQLEXPRESS… for your sqlexpress instance You’ll also find a set for you sql2000 instance, buth they don’t store the ipaddress with sql2000. (…MSSQLServerMSSQLServer…)

---

Hi alzdba, I have these registry: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerSQL2005
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerSQLEXPRESS The MSSQL.1 and SQL2005 seems to be for the instance SQL2005 and the MSSQL.2 and SQLEXPRESS are for the instance SQLEXPRESS.

---

– your key SQL2005 is the pointer from the named instance to the correct MSSQL.x-regkey.
Likewize for the SQLEXPRESS.

---

]]>