SQL SERVER – FIX: Msg 7416 – Access to the Remote Server is Denied Because No Login-mapping Exists

While working with a client, I encountered few errors while using linked server and I am going to share my solutions via a few blog posts. In this blog we would talk about fixing error 7416 – Access to the remote server is denied because no login-mapping exists.

Error Related to Remote Server

Before I talk about the problem and the solution, I must share a few environment details.

In my lab, I got two servers SQLSERVER-0 and SQLSERVER-1. The linked server was created on SQLSERVER-1 which was given name as ONE, connecting to SQL SERVER-1. There are two databases involved here. On source server (SQLSERVER-0) I have a database called SQLDB0 and on destination (SQLSERVER-1), the database is called SQLDB1.

  1. Linked server “test connection” was working just fine.
  2. The linked server was not created using “SQL Server” option, but “Other data source” was used before application wanted to use a different name.
  3. Simple queries were working fine but a stored procedure which was using “execute as user” was failing.

Here is the error which was coming when we were executing a stored procedure. Here is an oversimplified version of the stored procedure. The procedure is created in database SQLDB0.

CREATE PROCEDURE usp_fetch_data
AS
BEGIN
	EXECUTE AS user = 'user_no_login'
	SELECT *
	FROM One.SQLDB1.dbo.Table_1
	REVERT
END

And here is the error message when I execute it as below.

Here is the text of the error message.

Msg 7416, Level 16, State 2, Procedure usp_fetch_data, Line 5 [Batch Start Line 10] Access to the remote server is denied because no login-mapping exists.

WORKAROUND/SOLUTION

It is important to note is that user_no_login is NOT a login but a user in the database created using below command.

CREATE USER [user_no_login] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO

Now, the question was how can I provide a mapping to a user? Here was the error which would come if we even try that.

Msg 15007, Level 16, State 1, Procedure master.dbo.sp_addlinkedsrvlogin, Line 76 [Batch Start Line 2]
‘user_no_login’ is not a valid login or you do not have permission.

‘user_no_login’ is not a valid login or you do not have permission.

The message is perfect as it’s not log in but a user in the database which is not mapped to any login. I searched further and found that we can provide this user id in the connection string, also called as Provider String. Here is what I have changed in the linked server definition.

-- NOT WORKING
EXEC master.dbo.sp_addlinkedserver @server = N'ONE', @srvproduct=N'SQL', @provider=N'SQLNCLI11', @datasrc=N'SQLSERVER-1', @provstr=N'Data Source=SQLSERVER-1'
-- WORKING (added "User ID" added @provstr)
EXEC master.dbo.sp_addlinkedserver @server = N'ONE', @srvproduct=N'SQL', @provider=N'SQLNCLI11', @datasrc=N'SQLSERVER-1', @provstr=N'Data Source=SQLSERVER-1;User ID=user_no_login'

I am not sure how it worked but we just passed the User ID parameter. This user was not present in the destination server. When I captured profiler, I found that this was passing login name which was provided in linked server properties. So, the user ID passed was just to bypass the error message.

Have you seen a similar error? Did you find any other solution?

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Login, SQL Scripts, SQL Server, SQL Server Security
Previous Post
SQL SERVER – FIX: Msg 15274 – Access to the Remote Server is Denied Because the Current Security Context is not Trusted
Next Post
SQL SERVER – Add Database to Availability Group Failure – This BACKUP or RESTORE Command is Not Supported on a Database Mirror or Secondary Replica

Related Posts

2 Comments. Leave new

  • Thanks Pinal, This article resolve my issue as well.

    Reply
  • I have two SQL Servers, both 2016, and am trying to set up a linked server from one to the other.
    The linked server is of type “SQL Server” and in the Security section, I’m adding a mapping as Local = DOMAINUSER, Remote = DOMAINUSER and password – this domain user is set up as a login on both servers.
    The option “Not be made” is selected from the list below.
    However, when trying to create this, I get the error as mentioned.
    So I’m a bit puzzled why it doesn’t work…

    Reply

Leave a Reply

Menu
Exit mobile version