Connectivity issues with linked servers in SQL Server authentication

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.

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.