While the ArcSDE component organizes database access to ArcGIS clients, it is the RDBMS client libraries which provide connectivity. Esri makes some database vendors' installers available, but PostgreSQL is the only database which has client libraries installed within ArcGIS clients by default.
Therefore, for all connectivity questions, the following questions must be resolved:
- Is the necessary RDBMS client installed?
- Does the RDBMS client have IP connectivity to the server?
- Is the database configured in a form compatible with ArcGIS access?
The ArcGIS documentation has dozens of pages for each of the five major RDBMS it supports, but a good generic starting point would be the Manage Data page, with Databases, Geodatabases, and Administering geodatabases main topics along the left side. The Database clients page provides details on what clients are supported.
Each RDBMS has its own tools for connectivity, and even when the client libraries are installed, server firewall or access permissions may need to be granted before a successful connection can be made. The Troubleshoot connections to a database page details possible issues.
Due to case sensitivity in some databases, there may be database, schema, table, or column properties which might prevent ArcGIS connectivity. In general, it is unwise to use mixed-case in names of database objects, because ArcGIS clients are unlikely to be able to see them. There are also a number of datatypes available in databases which cannot be mapped to common types by the Direct Connect (ArcSDE) communication protocol, and even some types (64-bit integers) which are not accessible by ArcGIS Desktop or Server clients. The DBMS data types supported in ArcGIS page provides references to supported types.
In your particular case, with Microsoft SQL Server, the appropriate Native Client libraries need to be installed (which one depends on the exact release of SQL Server). The Microsoft SQL Server database requirements page provides details on the supported database versions and which client may be necessary.