I keep highlighting that it is necessary for DBAs to understand the underlying infrastructure as it helps when it comes to resolving issues. I was having connectivity issues with my SQL Server 2005 instance as sqlcmd and osql are both throwing this error
HResult 0xFFFFFFFF, Level 16, State 1SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
This blog post highlights how to troubleshoot connectivity issues with SQL Server and gave me an idea why I am getting the issue. The first thing I did was to run an ipconfig /flushdns to make sure I looking at the correct IP-hostname resolution from my DNS server. Then, I tried to PING the IP address of the SQL Server instance and it worked just fine (I usually add the -a switch on my PING command to include the hostname if it is indeed correct). I tried the reverse – PING the hostname – but my DNS server is returning a different IP for the same hostname. Lokks like the DNS entry for my SQL Server box is a bit screwed up. I logged on to the DNS server to validate and I was right. I modified the IP address of the A record pointing to my SQL Server box, ran another ipconfig /flushdns before doing another PING test to validate the change. Once everything is ok, I was able to connect to my SQL Server instance using sqlcmd and osql. It pays to understand a bit about something not directly related to your work sometimes