A network-related or instance-specific error has occurred while establishing a connection to SQL Server [Error Number: 53]

Connecting SQL Server database remotely using SQLCMD getting below error

C:\Users>sqlcmd -S  192.168.5.100 -d TESTDB_2023 -U sa -P Password_4U
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [53]. .
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : 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..

While I was trying to make connection SQL Server Management Studio getting below error

===================================

Cannot connect to 192.168.5.100.

===================================

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

------------------------------

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-53-database-engine-error

------------------------------

Error Number: 53

Severity: 20

State: 0

------------------------------

Program Location:

   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)

   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)

   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.Open()

   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)

   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

The network path was not found

-------------------------------------END---------------------------------------------------

 

Possible Solution

---------------------

Possible reason is bloking IP and Port for incoming connection in the server
Let suppose Local Machine static IP : 192.138.10.51 where where you want to access sql server
and SQL Server IP : 192.168.5.100 where the Sql server is Installed

On the SQL server Machine
   1. Press the Windows + R 
   2. type control firewall.cpl  --> click on Advance Security
   3. click on Inbound Rule
   4. Click on New Rule
   5. General Tab --> Write Name of Rule Example : SQL_Server_TCP_IN
   6. Scope Tab   --> Local IP Address  --> By default selected on Any IP Address
                  --> Remote IP Address --> Add your local machine IP Example : 192.138.10.51
                    Or you can specify range of IP Address of your On-Primises Network
   Protocol and Ports 
              --> Protocol Type : TCP(By default this is selected)
              --> specify the DB port for Example SQL Server By default port is 1433
                  otherwise you will get network related error while accessing SQL Server DB.

  Verify TCP is enabled for SQL Server using SQL Server Configuration Manager
  1. Click the Windows key + R
  2. Type compmgmt.msc
  3. Expand Services and Applications
  4. Expand SQL Server Configuration Manager
  5. Sql server Network Configuration 
       Protocol of <Instance Name>
       Enable TCP/IP
  6. Click on Sql Server Sevices and right click on Sqlserver(<INSTANCE_NME>) and restart the service
  
C:\Users>sqlcmd -S  192.168.5.100 -d TESTDB_2023 -U sa -P Password_4U
1>
2> select db_name()
3> go
--------------------------------------------------------------------------------------------------------------------------------
TESTDB_2023
(1 rows affected)
1>

Now able to connect remotely.

Also  can be reviewed TCP/IP Protocol is enable or not

WIndows + R

    compmgmt.msc

Computer Management local

     SQL Server Configuration Manager

     SQL Server Network Configuration

     Protocols for <Instance_name>

    Make Sure TCP/IP and Named Pipe should enable

    If not then you would have to enable, SQL Server service restart will required (Downtime approval will required)