Error message from database execution: Access to the remote server is denied because no login-mapping exists

Question

Environment & Details

• Environment – SQL Server 2014.
• All DB’s are participating in availability group.
• To connect to Read only node of Server, we created the linked server on both node.
• Internally,we have provided the Server DNS name and “ApplicationIntent=ReadOnly” as part of linked connection string. The linked server always read the data from secondary/read node.
• Linked server is using [ReadOnly] SQL user. This user has data reader permission on the selected database.
• Above Linked server – worked fine. And we are using the linked server in SQL jobs, which runs under the “sa” context.

Problem

• I am developing ADF pipeline. For the same, created the SQL user – “adfuser” on SQL server. This user has data reader, data writer permissions on required database.

• Now, I am trying following query under the ‘adfuser’ context.

  SELECT Col1, Col2 FROM [ReadOnly].DB.dbo.TableA.

It is throwing an error – Database operation failed on server 'Server name' with SQL Error Number '7416'. Error message from database execution: Access to the remote server is denied because no login-mapping exists.

Any solutions?

Answer 1

You need to Add “User ID=Username” into the provider string on your linked server when it's created.

EXEC master.dbo.sp_addlinkedserver @server = N’LinkServerName’, @provider=N’SQLNCLI’,@srvproduct = ‘MS SQL Server’, @provstr=N’SERVER=serverName\InstanceName;User ID=myUser‘ 

you can read more here: https://blogs.technet.microsoft.com/mdegre/2011/03/10/access-to-the-remote-server-is-denied-because-no-login-mapping-exists/