In the previous article, I showed a bothersome little error for the Kerberos Configuration Manager software. In that article, I also showed how to resolve that error. This article has nothing to do with that previous article beyond the similarity in tool names (“Configuration Manager” being central to both). In this article, I will show you how to add or rename a network adapter to be used by SQL Server.
You are probably thinking “What’s the big deal? SQL Server does that automatically.” Well, for the most part SQL Server does automatically add your network adapters for you. I would guess the success rate is somewhere in the realm of 99.999% of the time. However, that is not 100% of the time and it just so happens I have run into a couple of occasions where the NIC was not properly presented to SQL Server for use by SQL Server or even configuration by the DBA. This presents a bit of a problem!
Add a Network Adapter to SQL Server Configuration Manager
Before diving into adding a network adapter, let’s take a look at what the network properties pages might look like in SQL Server Configuration Manager. Many of you are likely very familiar with this already. If you are not, you should be!
In SQL Server Configuration Manager, expand “SQL Server Network Configuration, then click on the Instance for which you are interested. Note here that I am showing a multi-instance server. Adding multiple network adapters is a configuration method that I frequently use when dealing with a multi-instance server. There are several benefits to having multiple network adapters such as: 1) being able to have each instance listen on the same port – but different adapters, 2) being able to add an A record in DNS for each instance, and 3) adding a layer of security by obfuscation (e.g. developers don’t know the instance name – just an alias for it).
In the case where there will be multiple Network Adapters presented to SQL Server, the first thing to do is to disable “Listen All” in the case where multiple Instances exist on the server and you are looking for a more advanced setup.
With that configuration set, the next page you should be familiar with is the “IP Addresses” page. This is the page where the network adapters should be found.
The preceding image is a very busy image. There are three network adapters on this particular server. Each network adapter is assigned to a different IP address and each has a different port. In this case, which happens to be a multi-Instance server, not all of the assigned adapters are listening on the specified port. This is a desired configuration when everything is working well. This is where the problem that is the basis for this article comes into play – what happens when the network adapters do not automatically show up in SQL Server Configuration Manager (SSCM)? Alternatively, if the adapters are all showing, how do I keep them all straight in SSCM so I can make sure the correct adapter is enabled/disabled for the instance in question?
Let’s add that Network Adapter Now
SQL Server should detect a new network adapter when it is added to windows. Sometimes, it takes a server restart. Sometimes it never shows up. And sometimes it shows up but you want to rename it. Some will say to just run a repair on your instance if it is not showing the network adapter changes. After a recent experience with that, I say don’t bother with the repair! In a recent adventure with this problem, I had two adapters presented to the server prior to installing SQL Server and only one of the adapters was recognized by SQL Server. Clearly, the attempt after that would have been fruitless because the setup didn’t find the second adapter. Additionally, the running of repair on the server could cause more harm than it might fix.
So, if we don’t want to run repair, then what should we do? Regedit to the rescue! Oh no, not that! Isn’t that risky as well? Truth be told, editing the registry can cause damage if you are careless, reckless, and maybe in a bad mood. Editing the registry is no more dangerous than altering data in a database. Just take a few precautions if you are worried. Some precautions might include taking a backup of the registry or maybe a system state backup of the server. These are the same sorts of precautions a good DBA would take when altering data in a database (backup the data, backup the server etc). Let’s see what hacking the registry would entail.
First and foremost, the path we are looking for in the registry is a bit long and hairy and you must pay particular attention here.
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQLXX.[YourInstance Identifier]MSSQLServerSuperSocketNetLibTcp]
In the preceding image, my path is (circled in red at the top of the image) [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL14.DIXSEPTLATIN1MSSQLServerSuperSocketNetLibTcp] where my version of SQL is 14 (replacing the XX) and my instance identifier is DIXSEPTLATIN1 (replacing “[YourInsance Identifier]“). Once Tcp is expanded, you will see all of your adapters that are usable in SSCM.
In order to change any of these adapters (or to add a new one), the first step is to export the key for either the adapter to be changed or for the adapter to be used as a model in order to create the missing adapter.
As noted in the image, you will export the key to a reg file. Everything in IP2 shown in the image will then be exported and saved. It is this exported reg file that we will edit in order to create a new adapter (or rename an adapter). Here is an example of an edited reg file that is ready for import into the registry.
In this example I have only made a couple of changes for this demo adapter. In this case, I named the adapter to match the instance name for which it will be assigned and “listening”. Then I proceeded to modify the IPAdress (in red) to the appropriate IP address that has been reserved for that network adapter. You will need to modify each of these settings as best suits your needs. I like to name the adapters to match the instance because then it becomes very easy to keep straight and troubleshoot in the future.
After making the adjustments in the file, then it is time to save the file and “import” it into the registry. This step is easy – just double click the file from explorer and you will be prompted with some warnings about writing data to the registry. Once imported, I see something like this in my registry.
See how that adapter has been added in the registry? This is precisely what we want. Now when we look at the network properties pages in SSCM for this Instance (because this is Instance specific), we will see a graphical representation of that adapter that we can edit.
One major caveat illustrated in this image of the IP Addresses page in SSCM is that the adapter name is different than what I showed in the reg file. This was done to help illustrate a requirement here. If the adapter name does not begin with “IP” then SSCM will not pick up the new adapter. You must name your adapters with an IP in front for it to be usable in SSCM. Now that you have more adapters added and named to your liking, you are set for a more custom setup for SQL Server that allows you greater security and flexibility.
Put a bow on it
In this article I have demonstrated a fairly simple method to help you modify your network adapters that are presented to SQL Server. Being able to modify these adapters is essential in multiple different scenarios such as the adapter is just plain missing or you want to rename the adapters in order to more easily manage the server in SSCM.
Interested in more back to basics articles? Check these out!
Want to learn more about your indexes? Try this index maintenance article or this index size article.
This is the second article in the 2020 “12 Days of Christmas” series. For the full list of articles, please visit this page.
The post Simple Method to Add A Network in SQL Configuration Manager first appeared on SQL RNNR.