SQL Server - Error Using OPENROWSET: Access to remote server is denied because no login-mapping exists

Views: 857
Reading Time: 3 minutes

Hello people,
Good night!

In this post I will talk about an error that occurs when trying to use the OPENROWSET or OPENQUERY command to access data from a remote server where the linked server used does not have a fixed user and the connection user does not belong to role sysadmin, which represents the majority. of the cases. (plus one error reported by Henrique Mauri.. kkk)

Msg 7416, Level 16, State 2, Line 22
Access to the remote server is denied because no login-mapping exists.

This error is very similar to the double-hop scenario that occurs in Kerberos, where a client application is on the 1 computer, BizTalk RFID is on the 2 computer, and the feature that requires credentials (such as an SQL server). Server RFIDsink) is on the 3 computer, but in this post I will show a solution using SQL Server only. If you would like to go deeper into troubleshooting using Kerberos, please take a look in this post.

Simulating the error in your environment

A practical way to simulate this problem is to create a new linked server with the parameter @ useself = N'True ', stating that the same user will be used at both ends of the connection, which will be the currently logged on user, and then try to use this linked server.

As already mentioned above, this error message only appears when the user running the query CAN'T be part of role sysadmin and the connection user is using SQL Server authentication (instead of Windows Authentication).

SQL Server - Access to the remote server is denied because no login-mapping exists.

How to solve the problem

From what I mentioned above, it is clear that if the connecting user is added to role sysadmin, this problem will stop occurring, but I don't even consider it a solution. Granting privileges (even more than sysadmin) just to stop error messages is never a solution.

Nor am I very much in favor of setting the username and password on Linked Server, so that all users with access to the server can use this Linked Server in an untraceable way. Always check the option "Be made using the login's current security context" in LinkedServer.

SQL Server - Linked Server Properties Security

A good solution would be to use Windows Authentication, but as the development team of the company where this problem occurred would not even consider changing the authentication of all applications to Windows Authentication, the best solution I found for this problem was to create a new user only for OPENROWSET and OPENQUERY uses, with restricted access only for queries made with these two commands (which are very few) and set this user and password in OPENROWSET, thus:

SQL Server - Access to the remote server is denied because no login-mapping exists solved

And that's it folks!
If you know of a different solution to this problem, leave your suggestion in the comments.

Hug and even more!