SQL Server Name Resolution Troubleshooting

By:   |   Comments (5)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > SQL Server Configurations


Problem

A colleague recently said, "With all the legacy systems, modern systems, and 'spokes in the wheel' in this environment, we should celebrate whenever we actually get data to the client." No doubt there are many pieces to the puzzle that comprises data delivery to internal and external clients, but knowing about every facet of an N-tier architecture application can be challenging. So what are the truly important things a DBA should know about to ensure data gets delivered? Sure, there are the usual technologies like replication and database mirroring, but one of the most important systems that make enterprise applications work is server name resolution.

Solution

So what exactly should a DBA know about name resolution? In a nutshell, a DBA and others responsible for applications should know how to troubleshoot it. Most application programmers use host names in connection strings, since the IP address can be modified easier in the DNS record than it can on the host itself. Also, many companies use a DNS alias for a connection string to either refer applications to a server farm or as an "abstraction layer" for disaster preparedness. In any case, name resolution plays a vital role in the application serving the client.

Name resolution is the association between an Internet Protocol (IP) address and a user-friendly computer name. The most familiar form of naming is called the Domain Name (Naming) System, or DNS. If it weren't for DNS, we would have to remember the IP address of every website we visit. Remembering http://www.microsoft.com/ or http://www.mssqltips.com/ is much easier than 10.72.49.210 or 172.16.198.4. With the impending implementation of IP Version 6, IP addresses will be even harder to remember.

When looking at host names, there are at least two names associated with a host: the host name and the Fully Qualified Domain Name, or FQDN. The Fully Qualified Domain Name is created by attaching a DNS suffix to the host name. Let's say we have a SQL Server or server farm at MSSQLTIPS with a host name Support. The Fully Qualified Domain Name of that SQL Server would be support.MSSQLTIPS.com. A host can have a number of Fully Qualified Domain Names in DNS. This is one of the methods used to resolve names on hosts that host numerous websites.

Name Resolution and The Host

There is a sequence in which name resolution is attempted:
  1. The host first determines whether the name in question is its own name. In addition to the host name given to the host, two other identifications are associated with the host - the name LocalHost and the IP address 127.0.0.1. The latter is considered a loopback network address used to troubleshoot the network interface card (NIC).
  2. A Hosts file, located on each host, is queried. In the early days of networking, and to a lesser extent now, the file (located in %SystemRoot%\System32\Drivers\Etc folder) was used as the primary method for name resolution. As enterprises grew and more machines used, the task of manually updating the file on each host became unruly. This lead to the development and implementation of other technologies such as Windows Internet Naming Service and DNS. These files are mainly used for Disaster Recovery and fault tolerance in today's environments.
  3. DNS Servers are queried. Whether through the Dynamic Host Configuration Protocol or through static entry, most hosts are assigned DNS Servers that can be queried. If the DNS server does not have an immediate answer to the query it can send the request to another DNS server for resolution.
  4. As a last result, a host can use NetBIOS to try and find a host. This is the least efficient methods that requires the equivalent of a broadcast.

Tools A DBA Can Use To Troubleshoot Name Resolution

Since the DBA group is one of the first groups to be contacted when an application fails, name resolution can be quickly eliminated as a possible cause of application failure. The first and easiest way to check name resolution is to PING the host using only the hostname if you're local to the host (instead of the Fully Qualified Domain Name). This will test how easily the name can be resolved from your workstation. On occasions IP addresses are changed on the host, but the DNS entry is not updated either because of latency or because the host was not able to update its own DNS record (certain types of DNS providers do not allow a record to be automatically updated).

Another method for checking name resolution, at least from the domain perspective, is NSLOOKUP. NSLOOKUP is a command-line utility that allows, among other things, lookup of a fully qualified domain name based on either the host name or IP Address. Open a command prompt and type NSLOOKUP:

Access to NSLOOKUP

In this case NSLOOKUP does not have an authoritative DNS server (the workstation is not in a domain); in most cases the default name server assigned to your host will appear. Once the server information is received there are many options available, but since we are discussing name resolution, the only thing you need to do from here is type either the IP address or hostname of the server you wish to check. In this case we are checking the DNS information for "support.microsoft.com":

DNS information for support.microsoft.com

If you suspect that name resolution is causing issues, you can look at a few DNS settings, depending on the method of IP addressing your company uses. There are companies out there that use DHCP for server IP assignment, so to check the DNS information of the server open a command prompt and type "IPCONFIG /ALL":

IPCONFIG information

You can see that DHCP is enabled and that the DNS Server is 192.168.0.254. It could be that the host is directed to an incorrect DNS server. Unfortunately, if DHCP is enabled you cannot make changes to this setting. If the DNS server has changed since the address was obtained (as evidenced by the "Lease Obtained" area), you can attempt to obtain updated information by running "IPCONFIG/ RELEASE" and "IPCONFIG /RENEW" from the command window. This should only be done when working from the host's console, otherwise you will lose connectivity with the host.

If your server is assigned a static address, you can view and potentially modify the properties assigned to the Local Area Network (LAN) properties. To get to the properties of the LAN connection:

Right-click My Network Places and choose Properties on your desktop:

Local Area Network properties

"My Network Places" does not automatically appear on the desktop. If it is not present, open the Control Panel and double-click "Network Connections"

Right-click the LAN connection in question and choose Properties:

Accessing the LAN properties

Navigate to "Internet Protocol (TCP/IP)" and click Properties:

Accessing the TCP/IP protocol properties

This screen gives you information on the current IP address settings, as well as the DNS Servers the host uses. An additional DNS server can also be added here:

LAN Properties

Click on the "Advanced" button and then the "DNS" tab to view additional information on the LAN connection:

Advanced Local Area Network properties

Here you can specify additional/different DNS servers, add additional DNS suffixes that can be appended to the host name, and a static DNS suffix for that connection. Multiple DNS suffixes can be used in instances where a company has multiple domains.

Next Steps
  • IP configuration changes should be made at the host console, not remotely. This is so you can maintain communication with the server in the event the configuration information is mistyped or otherwise incorrect. In addition, most companies have individuals or teams that handle network configuration. Changes should not be made to the IP configuration unless authorized or approved by the network team (they may choose to make the changes themselves)
  • DHCP configuration can be assigned via Active Directory Group Policy as well, so you may have to contact the Active Directory team for IP configuration changes. In addition, any manual entries in the LAN properties may be ignored if DHCP information is obtained via Group Policy
  • Read about the Domain Name System and the Windows Internet Naming Service as implemented in a Microsoft environment
  • Review the sequence in which Microsoft Windows clients resolve host names
  • Learn how to use NSLOOKUP to troubleshoot name resolution
  • Use Fully Qualified Domain Names when referring to servers or server farms


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Sunday, March 22, 2020 - 12:13:06 PM - Tim Cullen Back To Top (85165)

Hi Mark:

Keep in mind that name resolution is a function within the operating system, not SQL Server, so the linked servers table will never be checked.

Tim Cullen


Friday, January 10, 2020 - 11:25:32 AM - Mark Burns Back To Top (83672)

You missed sommething important. In your sequence under "Name Resolution and The Host", you failed to address the Linked Servers table.

So this is my question: in the Server name resolution sequence, where are think Linked Servers list checked to resolve the name? BEFORE the DNS query, or after? (to me, it would seem to make more sense to check the internal server list BEFORE going out across the wires, but I don't know if thats what happens or not).  Does anybody know the answer to this?


Friday, September 10, 2010 - 10:58:24 PM - Tim Cullen Back To Top (10153)
Let me ponder that one-an answer is NOT jumping out at me at the moment.


Friday, September 10, 2010 - 10:57:34 PM - Tim Cullen Back To Top (10152)
Let me ponder that one-an answer is jumping out at me at the moment.


Thursday, September 9, 2010 - 5:51:21 PM - Don Schaeffer Back To Top (10144)
Perhaps a distantly related problem I have from time to time is a developer claiming his web app does not have the necessary database permissions when actually his application is trying to connect to a different server than the developer is claiming.  I would like a way to determine what server an application or service is attempting to access.  Any tips here?  I've resorted to examining the error log on likely servers looking for login failures, but that's rather hit or miss.















get free sql tips
agree to terms