All we need is an easy explanation of the problem, so here it is.
I have SQL SERVER 2008 R2 installed on windows Server 2016, it runs smoothly on a LAN network interface.
I Added a USB MODEM (4G) to the server and went to the IP Addresses tab in network configuration to Adjust its IP Settings (enable the IP and set the port for listening) but it is not shown in the IP Addresses tab, I tried to restart the SQL services and restart the server but it is not showing.
I searched the web but couldn’t find an answer, what is the problem with it ?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
I don’t know the official way that Microsoft recommends adding a new ip address to the network configuration panels, and I don’t know how you would do it with SQL Server 2008, I’ve only worked with SQL Server 2012+, so take these with a grain of salt.
For reference, here’s Microsoft’s page on editing the TCP/IP properties panels – https://docs.microsoft.com/en-us/sql/tools/configuration-manager/tcp-ip-properties-ip-addresses-tab?view=sql-server-ver15
IF all of your ip addresses on the host are going to be for listening to SQL Server, you could tell it to ‘Listen All’ = ‘Yes’. Instead of then having to specify the ip addresses and ports you would then just specify the port under the ‘IPAll’ region. Now, this is not for everybody, but it is an option.
If using the ‘IPAll’ doesn’t work for you, and you are lucky enough to have an ‘IPx’ that isn’t in use you can edit the ‘IP Address’ box and ‘TCP Port’ fields to make it work for the new ip address. Just make sure to check ‘Enabled’ = ‘Yes’ otherwise SQL Server will ignore it.
Now, if you can’t just blanket listen to all ip addresses on the same port or just edit an existing IPx field you are going to have to add another ‘IPx’ field. Here’s how I add an ‘IPx’ field to the panel, but I don’t think it’s how Microsoft officially recommends people to do it, and unfortunately it’s a registry edit. With that being said, if you are not comfortable working with the registry DO NOT DO THIS. As always, back up your registry, take preventative measures, and test it out in development before applying this in your production environment. In order to get a new ‘IPx’ field to show up you have to create a new registry key at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLXX.(INSTANCENAME)\MSSQLServer\SuperSocketNetLib\Tcp\IPy (where IPy is the number that comes after the largest existing one) and copy over the key entries found in the other IPx registry entries. You should see registry entries that say ‘Active’ and ‘IpAddress’ under the registry entry that you copy over.
I am going to be very interested to see how others solve this to hopefully make it so that I don’t have to edit the registry in the future.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂