大网数据广告活动
数据库与应用安全

Linked server connectivity errors in SQL Server

· 52CE 我爱测

This article provides a resolution for consistent authentication errors that are related to linked server connectivity in SQL Server.

This article helps you to resolve the consistent authentication linked server connectivity issue.

Symptoms

When you try to connect to your Microsoft SQL Server account from within SQL Server Management Studio (SSMS), you receive the following error message:

Msg 233, Level 20, State 0, Line 0 A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

If you try to connect by using an application that was developed by using .NET technology, you might receive the following error messages:

The OLE DB provider "MSDASQL" for linked server "SQLPROD02" reported an error. Authentication failed.

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SQLPROD02".

You might also receive the following error message:

OLE DB provider "MSDASQL" for linked server "SQLPROD02" returned message [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'CONTOSO\SQLPROD01$'.

This message indicates the following conditions:

  • You're using named pipes for the linked server connection.
  • You're using a SQL Server login account.
  • The mid-tier SQL Server service account or machine account doesn't have login rights to Windows on the back-end server.

Cause

You might encounter these error messages because of a linked server account mapping issue.

Resolution

You can correct this error by forcing TCP/IP or granting the appropriate permissions.

In the Linked Server security dialog box, select Be made with this security context for a successful SQL Server login.

Note: When you select Be made without using a security context option, you will see the following error message: "Login Failed for user NT AUTHORITY\ANONYMOUS LOGON".

In addition to the main mapping settings in the Linked Server security dialog box, you can modify the individual account mappings in the upper portion of the box. These override the main mapping settings.

Note: Using a SQL Server ODBC driver in a linked server isn't a supported scenario. Other ODBC drivers might have primary support from the vendor and only limited support from Microsoft. For more information, see SQL Server Linked Server Delegation Issues.

More information

Consistent authentication issues in SQL Server

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 03/26/2024
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。