Microsoft SQL Server 2022 - no TCP/IP connection in client protocols in SQLServer Configuration Manager

Hello,

the Server was already linked but the connection bring similar failure. connection from MSSQL 2019 - MSSQL 2022.

I think the destination SQL Server is not listening for connections?

BR Mario

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

The test connection to the linked server failed.

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

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd, Boolean retry)

at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()

at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()

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

Named Pipes Provider: Could not open a connection to SQL Server [2].

OLE DB provider "MSOLEDBSQL" for linked server "SW01PAC702" returned message "Login timeout expired".

OLE DB provider "MSOLEDBSQL" for linked server "SW01PAC702" returned message "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.". (.Net SqlClient Data Provider)

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

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

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

Server Name: SW01PAC01

Error Number: 2

Severity: 16

State: 1

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

Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

Ok,

• So, first install. "Microsoft OLE DB Driver for SQL Server” (MSOLEDBSQL 19.x or later)"
• After installation Open SQL Server Configuration Manager
• Go to SQL Server Network Configuration → Client Protocols
• You should now see TCP/IP, Named Pipes, and Shared Memory
• Right‑click each → Enable
• Then restart SQL Server (MSSQLSERVER) & SQL Server Agent.
• then execute server linked command again

   EXEC master.dbo.sp_addlinkedserver 
   @server = N'MyLink2022_TCP',
   @srvproduct = N'',
   @provider = N'MSOLEDBSQL',
   @datasrc =N'tcp:servername,1433';
   
  

Senthil, Mario obviously already have MSSQLOLEDB installed, since the provider name appears in the error message. But there will not be anything in Client protocols with less than he installs the old SQL Server Native Client. (Which he should not do, and it would not change things.)

Hello, 
I need to grant a client read access to a dedicated table. `@sqlCmd='update #pslTempStudiesProd set IsInUID=1 where ser_ins_uid in (select SERINSTANCEUID from '+@server0x+'.Patient.dbo.SERIES)'` where `server0x` is the remote server with MS SQL 2022.

When I try to apply the AI ​​solution, port 1433 doesn't open. What steps are needed for this in cliconfg.exe?
Thx in Advance, Mario Lackner

What error message do you get?

if I try to connect by using following command I get this error message:

select SERINSTANCEUID from sw01pac0x1.Patient.dbo.SERIES

OLE DB provider "MSOLEDBSQL" for linked server "sw01pac0x1" returned message "Login timeout expired".

OLE DB provider "MSOLEDBSQL" for linked server "sw01pac0x1" returned message "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.".

Msg 2, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [2].

btw. if SQLServer2016 is installed on the querydestination this query works.

So that is a plain standard connection error, meaning that the SQL Server instance you are trying to connect to was not found. This has nothing to do with the missing node Client protocols. This is the same error message you get from SSMS if you mis-type a server name. The only difference is that instead of connecting from SSMS, you are connecting from an SQL Server instance. But the means of troubleshooting is the same.

1. Is the server name correctly spelled? Note that the name used for the connection is not necessarily SW01PAC0x1, because that is just the name of the linked-server object itself. Check the data_source column in sys.servers.
2. Is the remote instance a default instance, or are you missing the instance name? Again, check sys.servers.
3. Is the remote instance, configured to accept remote connections. This you check in SQL Server Configuration Manager on the remote machine, under SQL Server Network Configuration.
4. Are there any firewall rules in the way? This could potentially be an outgoing firewall on the local SQL Server machine.

hello,

point 1 and 2 are ok. servername ist resolveable and it doesn't matter if i use the hostname or the default instance.point 3: where can i check if the remote instance is configured to accept remote connections ind SQL Server Configuraiton Manager?

point 4: windows firewall is for testing complete switched off

any other ideas?

attached the output from "SELECT *  FROM [master].[sys].[servers]"

maybe this helps?

BR Mario

👁 User's image