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)

  

SQL Server Restore Error - Access is Denied

 USE [master]
RESTORE DATABASE [SS_TESTDB_2023] FROM  DISK = N'E:\SQLServer_DB\SS_PRODDB_29MAY2023.bck' WITH  FILE = 1,  
MOVE N'SS_PRODDB' TO N'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023.mdf',  
MOVE N'SS_PRODDB_log' TO N'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023_log.ldf',  NOUNLOAD,  STATS = 5
GO
SQL Server logfile location 
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log 
2023-06-15 12:43:00.92 spid60      Error: 3633, Severity: 16, State: 1.
2023-06-15 12:43:00.92 spid60      The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023.mdf' at 'container.cpp'(2693).
2023-06-15 12:43:00.92 spid60      Error: 3633, Severity: 16, State: 1.
2023-06-15 12:43:00.92 spid60      The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023_log.ldf' at 'container.cpp'(2693).

solution
grant read,write permission on folder for the which is logged in
best practice is to login through Administrator and restore so that we could avoid the error

USE [master]
RESTORE DATABASE [SS_TESTDB_2023] FROM  DISK = N'E:\SQLServer_DB\SS_PRODDB_29MAY2023.bck' WITH  FILE = 1,  
MOVE N'SS_PRODDB' TO N'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023.mdf',  
MOVE N'SS_PRODDB_log' TO N'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023_log.ldf',  NOUNLOAD,  STATS = 5
GO
5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
100 percent processed.
Processed 3869 pages for database 'SS_TESTDB_2023', file 'SS_PRODDB' on file 1.
Processed 2 pages for database 'SS_TESTDB_2023', file 'SS_PRODDB_log' on file 1.
RESTORE DATABASE successfully processed 3869 pages in 280.499 seconds (66.223 MB/sec).

SqlServer Error when creating Table : "Incorrect syntax near 'COLUMN_NAME' " due to QUOTED_IDENTIFIER OFF

Getting error while creating table in SQL Server database using SVN, SVN execute the statement through SQLCMD and by default QUOTED_IDENTIFIER  is OFF. 

The error was Incorrect syntax near 'EMP_ID'

13> CREATE TABLE EMPLOYEE(

14>     EMP_ID NUMERIC(18,0) NOT NULL,
15>     EMP_TYPE VARCHAR(10) NOT NULL,
16>     FIRST_NAME VARCHAR(40) NULL,
17>     LAST_NAME VARCHAR(40) NULL,
18>     EMAIL_ID VARCHAR(50) NOT NULL,
19>     MOBILE VARCHAR(40) NOT NULL,
20>  CONSTRAINT PK_EMPLOYEE  PRIMARY KEY ("EMP_ID", "EMP_TYPE")
21> );
22> GO
Msg 102, Level 15, State 1, Server DKBLAPT\MSSQLSERVER, Line 20
1>

Incorrect syntax near 'EMP_ID'.

I tried to run through SQL Server Management Studio, same statement is executed successfully because of by default QUOTED_IDENTIFIER is ON.

SSMS 

    --> Tools 

           --> Option 

                 --> Query Execution 

                     --> SQL Sever 

                        --> ANSI 

                           --> SET QUOTED_IDENTIFIER [check box selected] 

2> SET QUOTED_IDENTIFIER ON;

3> CREATE TABLE EMPLOYEE(

4>     EMP_ID NUMERIC(18,0) NOT NULL,

5>     EMP_TYPE VARCHAR(10) NOT NULL,

6>     FIRST_NAME VARCHAR(40) NULL,

7>     LAST_NAME VARCHAR(40) NULL,

8>     EMAIL_ID VARCHAR(50) NOT NULL,

9>     MOBILE VARCHAR(40) NOT NULL,

10>    CONSTRAINT PK_EMPLOYEE  PRIMARY KEY ("EMP_ID", "EMP_TYPE")

11> );

12> GO

(1 rows affected)

1>

Generally we create table without use of double quotes, we should use like below statement so that we could avoid error.

CREATE TABLE EMPLOYEE(

     EMP_ID NUMERIC(18,0) NOT NULL,

     EMP_TYPE VARCHAR(10) NOT NULL,

     FIRST_NAME VARCHAR(40) NULL,

     LAST_NAME VARCHAR(40) NULL,

     EMAIL_ID VARCHAR(50) NOT NULL,

     MOBILE VARCHAR(40) NOT NULL,

  CONSTRAINT PK_EMPLOYEE  PRIMARY KEY (EMP_ID, EMP_TYPE)

 );

 GO