Question: Why SELECT HOST_NAME() Returns Empty String while connected from certain .net application?
Answer: This is a very popular question I have observed in the recent time. Before I answer this question, I have to be honest here. When I was asked this question for the first time I did not know the answer as well. I am a SQL Server person and I know the basics of the .NET.
However, after a quick researched I figured out the answer of this question.
SELECT HOST_NAME() reports value provided as the Workstation ID (or WSID) in the connection string. As this value can be changed easily, I suggest that users should not depend on this value for making decisions. It is quite possible that multiple application may have the same value in the WSID and eventually they all report the same value in the HOST_NAME. Please note that WSID or Workstation ID should be 128 characters or less.
You can read everything about Connection String here.
Here is the screenshot of the query when I ran following query on my SQL Server Management Studio. This query basically returns the name of my machine when I have connected via SSMS.
Reference: Pinal Dave (http://blog.SQLAuthority.com)