SQL server list in ODBC

Question

When I go to 64- or 32-bit ODBCAD32 and begin the process of adding a System DSN, I select SQL Server, then drop down the SQL server selection box. Depending on what computer I am on within the same LAN (all computers on the same AD domain), I see some of the same servers--but also differences between one computer and another. On one computer, I see nine servers, on another I see six. Some are just ServerName and some are ServerName\Instance name. In at least one case, I see more servers on the 32-bit list than on the 64-bit list on the same computer.

My question has three parts:

1. What determines which computers on the same network see which SQL servers? The server machines are accessible exactly the same to me regardless of which workstation I am on, but the SQL server list I see differs from station to station.
2. How would I hide a particular server? Just close port 50000 on the firewall? In particular, we have a BackupExec server that has no need to be visible on the network--only on the server where it is running, the same server hosting the SQL instance.
3. In at least one case, the Server is just ServerName (no instance). But if I try to ping that name, it is not even resolved in DNS. How would I determine the actual server computer name or source for this. Is there any way to query the network for more information on the available SQL servers so I can positively identify them? Part of the problem is workstations running some instance of SQLExpress for some local app that really has no business being visible on the network.

Answer 1

Hi @Brian Hart ,

What determines which computers on the same network see which SQL servers? The server machines are accessible exactly the same to me regardless of which >workstation I am on, but the SQL server list I see differs from station to station.

It determined by the account's right you use.
If your domain\account is in the Administrator Group in DC, you can see all the instances in your workstation.If you use Domain\Administrator,you can see all the instances.

More information: administer-odbc-data-sources

How would I hide a particular server? Just close port 50000 on the firewall? In particular, we have a BackupExec server that has no need to be visible on the >network--only on the server where it is running, the same server hosting the SQL instance.

Open your SSCM-->right-click Protocols for <server instance>, and then select Properties-->On the Flags tab, in the HideInstance box, select Yes, and then click OK to close the dialog box. Please reference: hide-an-instance-of-sql-server-database-engine
For example, I hide the SQL2019DEV,then it excluded in the ODBC system DSN list:

In at least one case, the Server is just ServerName (no instance). But if I try to ping that name, it is not even resolved in DNS.

That is one default instance.For one default instance, its servername is your machine name, instance name is MSSQLSERVER.

How would I determine the actual server computer name or source for this. Is there any way to query the network for more information on the available SQL >servers so I can positively identify them? Part of the problem is workstations running some instance of SQLExpress for some local app that really has no business >being visible on the network.

You can check by SSCM, there is one list you can see their names clearly.

And you can execute code as next to verify the servername and servicename in SSMS:

select @@servername  

select @@servicename  

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

The function to list existing SQL Server have never worked well, see Enumeration Limitations "All of the available servers may or may not be listed. The list can vary depending on factors such as timeouts and network traffic."

Answer 3

Hi @Brian Hart ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.