I got a case recently where the customer had a SQL Alias setup but was having issues connecting to their application. Being in Business Intelligence Support we deal with plenty of connectivity issues and this is one topic of connectivity that does not get touched on a lot.
A SQL alias is an alternate name that can be used to make a connection. The alias encapsulates the required elements of a connection string, and exposes them with a name chosen by the user. Aliases can be used with any client application.
It started by getting this error message when trying to connect to SQL from a client application. For the sake of this write up we are going to use SQL Server Reporting Services (SSRS) as our application. We received an error similar to the following:
ERROR [08001] [Microsoft][SQL Server Native Client 10.0]TCP Provider: No such host is known. ERROR [HYT00] [Microsoft][SQL Server Native Client 10.0]Login timeout expired ERROR [01S00] [Microsoft][SQL Server Native Client 10.0]Invalid connection string attribute ERROR [08001] [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
From this we can tell we cannot connect to SQL Server because it could not find the server.
We tried a Universal Data Link (UDL) file to start with using the SQL Server name the customer provided, not knowing at that point it was actually a SQL Alias. Of Note, I always start with a UDL test because it is the simplest way to test connectivity. This gets the Customer’s application out of the picture.
Next we did a Ping on the IP of the SQL server and this was successful. This test rules out any DNS or name resolution issues in the environment.
So since Ping was successful, I went down the TELNET road. This allows us to see if anything is listening on the port we are expected to be on. This could be impaired by a firewall or the listener not being there.
We know the connection was successful because we got a blank screen that looks like this…
This got me thinking, why does Ping and TELNET work but not the UDL. I tested the UDL with ODBC Driver, MS OLE DB Provider, and SQL Server Native Client (SNAC), of which none worked using the name. I even tested the UDL forcing the TCP protocol which failed also.
TCP:<server name>,<port #>
e.g. - TCP:SQL2008R2,1433)
None of this was making sense especially since the server looked correct in configuration manager based on the Customer’s comment that they were using an Alias and they said it looked correct.
Aliases can be found under SQL Server Configuration Manager in the SQL Native Client configuration area.
At this time everything looked Ok, so I had to do further research where a SQL Alias can reside. During that research I found a bunch of references to Cliconfg. What is Cliconfg you may ask? I had the same question! In short, Cliconfg is the old configuration tool that ships with Windows. You can find more info on Cliconfg here .
NOTE: Be aware that Cliconfg is missing an "I" in the config part. This is due to the old 8.3 naming conventions for files.
So on the Application we went to Start > Run and typed in Cliconfg and noticed that they were using an IP address instead of a name which we saw in SQL Server Configuration Manager of which the Customer indicated the IP listed was incorrect.
In Cliconfg we saw something similar to the following…
I know the SQL Server IP address is 10.0.0.4, but the alias in Cliconfg was configured for 10.0.0.272. So to help correct this we edited the Connection parameters and set Server Name to the actual name of the SQL Server instead of the IP Address.
After changing that in Cliconfg we were able to connect to the SQL Server using the UDL successfully. Then we went back to the application, in this example being SSRS, and it was also able to connect successfully.
Bitness matters!
Be aware that client aliases can lead to connectivity issues if not configured correctly. These client aliases are really just entries in the registry. Also be aware that BITNESS MATTERS! The alias bitness will depend on what the bitness of the application is. If the application is 32-bit then the alias needs to be 32-bit.
From SQL server configuration manager the bitness is broken out in the GUI.
For Cliconfg there is actually separate application. One for 32-bit and one for 64-bit. By default if you go to start > run and type in Cliconfg on a 64-bit OS, you will get the 64-bit version. If the application is 32-bit, and you added a 64-bit alias then your will not pick up the alias. To get the 32-bit version of Cliconfg you can go to this path…
%SystemDrive%\Windows\sysWOW64\cliconfg.exe
Mark Ghanayem
Microsoft Business Intelligence Support