Issue in Linked Server from Azure SQL VM to Azure SQL database

Question

Script.txt

Hi,

I have created linked server on Azure sql VM (not on domain) to connect Azure SQL database.

It works fine for admin users but non admin users get msg:

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

Script file is attached to create linked server.

Any thoughts on this will be appreciated.

Thanks

Answer 1

Hi @ Zahid Butt

Thank you for reaching out. The issue seems to be related to login mapping for non-admin users on the linked server. Here are some steps that may help resolve it:

Configure Login Mapping: Use sp_addlinkedsrvlogin to explicitly map non-admin users to remote logins.

Check Security Settings: In the linked server properties, go to the Security tab and ensure that authentication is properly set up. Explicit mappings may be required.

Adjust Provider String: If applicable, modify the provider string to explicitly specify User ID in the connection settings.

Alternative Configuration: Instead of @provstr, try using @datasrc in your sp_addlinkedserver configuration.

Please go through these threads that might help you:

https://stackoverflow.com/questions/32084453/sql-linked-server-returns-error-no-login-mapping-exists-when-non-admin-account

https://dba.stackexchange.com/questions/184194/linked-server-to-azure-error-access-to-the-remote-server-is-denied-because-no

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hi,

Issue resolved following the link https://blog.sqlauthority.com/2019/06/07/sql-server-fix-msg-7416-access-to-the-remote-server-is-denied-because-no-login-mapping-exists/.

As described in link I have added data source & user id in the @provstr.

Found this through Copilot.

Thanks anyway.