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.
    SQL SERVER - FIX: Msg 7416 - Access to the Remote Server is Denied Because No Login-mapping Exists link-srv-err2-01
  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.
    SQL SERVER - FIX: Msg 7416 - Access to the Remote Server is Denied Because No Login-mapping Exists link-srv-err2-02
  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.
SQL SERVER - FIX: Msg 7416 - Access to the Remote Server is Denied Because No Login-mapping Exists link-srv-err2-03

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.

SQL SERVER - FIX: Msg 7416 - Access to the Remote Server is Denied Because No Login-mapping Exists link-srv-err2-04

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

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

, , ,
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

1 Comment. Leave new

Leave a Reply

Menu