How does an OLEDB/ODBC connection know the path to a SQL Server database by name only?

Question

If i have a connection string like this that I use in a Data Connection in Excel to get data from a SQL Server database:

OLEDB;
Provider=SQLOLEDB.1;
User ID=MY_USERNAME;
Password=MY_PASSWORD;
Persist Security Info=False;
Initial Catalog=ABC;
Data Source=MY_SQL_SERVER_NAME;
Use Procedure for Prepare=1;
Auto Translate=True;
Packet Size=4096;
Use Encryption for Data=False;
Tag with column collation when possible=False

How does my computer know the path to MY_SQL_SERVER_NAME on the network?

I assume that is somehow set by my company using some sort of configuration file somewhere, but I don't really know how it all works under the hood.

If I were getting data from a website instead of a SQL Server database, the website resource would need to be preceded by a path/domain-name like this: http:\\www.example.com\MY_RESOURCE_NAME.html

Without that path, I couldn't get to MY_RESOURCE_NAME.html. So, then how does ODBC work without a path preceding MY_SQL_SERVER_NAME?

My problem is that most of the time this connection string works fine. However, if I'm at home on VPN it doesn't. But I don't have any idea how to troubleshoot it.

Notes: I don't have a connection set up to MY_SQL_SERVER_NAME in the Windows ODBC Data Source Administrator, so my computer is somehow accessing the SQL Server by only using the connection string AFAIK.

Answer 1

ODBC relies on host name resolution capabilities, so the Data Source argument needs to be an expression that the host can lookup, via its available mechanisms. In Windows this involves resolution via SMB/CIFS, WINS, HostFile, DNS, mDNS, etc.

The VPN underscores that you are using SMB or DNS naming (with a short name, not FQDN), as both of those generally require fully qualified DNS names when configured on systems that do not permanently reside on the network in question. For instance, your host likely points to a LAN dns server, which doesn't host a zone for the network on the other side of the VPN. in those cases you must use a fully qualified domain name so that the query is resolved on the correct server.