5

I'm setting up a linked server from an on-prem SQL Server to a database on MS Azure (see below). When non-admin users query the Azure database via the linked server they get:

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

Any thoughts on how to get this working?

EXEC sp_addlinkedserver
@server='LincServer',                              
@srvproduct='',     
@provider='sqlncli',
@datasrc='myAzureDB.database.windows.net',
@location='',
@provstr='Encrypt=Yes;TrustServerCertificate=No;User ID=myAzureID',
@catalog='MyDB'

EXEC sp_addlinkedsrvlogin 
@rmtsrvname = 'LincServer',
@useself = 'false',
@rmtuser = 'myAzureID',
@rmtpassword = '########'
GO
3

The other answer is wrong and I want to disprove it with repro code and pictures.

The problem here is the parameter @provstr, it's a known issue and how to solve it is described here: Access to the remote server is denied because no login-mapping exists:

Problem: Unable to run a query through a linked server SQL Server 2005. This problem only happens with a non-sysadmin account.

You got the message below:

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

Cause: When creating a linked server with the parameter @provstr and you use a local SQL Server non-admin or non-Windows account, you have to add the parameter "User Name" into the @provstr

Resolution : Add "User ID=Username" into the provider string on your linked server

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

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName', @locallogin = NULL , @useself = N'False', @rmtuser = N'myUser', @rmtpassword = N'*****'

The OP has already done it, he included remote login into provider string in the linked server definition, so I really have no idea what else can cause the error.

But now I want to prove that following the cited guide you get rid of the error, while following the other answer we solve nothing.

Here is my repro code.

I have 2 servers, the default and named instance (hp2\sql_2012_dev), I'm going to link hp2\sql_2012_dev server to the default using @provstr without passing in @provstr remote login rmt that is not admin, this will cause the OP's error for ALL non-admin users.

I will map all the local logins to 1 remote login using OP's code. Then I'll try to apply the suggestion to map the problematic login individually but this will not solve the problem.

Then I'll recreate my linked server passing the remote login with the provider string and this will fix the issue WITHOUT ANY ADDITIONAL INDIVIDUAL MAPPING.

EXEC master.dbo.sp_addlinkedserver 
@server = N'LinkServer2012_rmt', 
@provider=N'SQLNCLI',
@srvproduct = '', 
@provstr=N'SERVER=hp2\sql_2012_dev';--don't pass User ID=rmt' now in order to cause the error

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = N'LinkServer2012_rmt', 
@locallogin = NULL , 
@useself = N'False', 
@rmtuser = N'rmt', 
@rmtpassword = N'*****';

At this point non-admin login l tries to select:

select * 
from linkserver2012_rmt.master.dbo.spt_values; 

And got the error

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

Ok, we are trying now to map it individually:

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = N'LinkServer2012_rmt', 
@locallogin = N'l', 
@useself = N'False', 
@rmtuser = N'rmt', 
@rmtpassword = N'*****';

And login l re-tries to select from linked server but he's got the same error

enter image description here

At this point I drop and re-create linked server, this time passing in rmt login in linked server definition, and map all the local logins to rmt, without doing any individual mapping:

exec master.dbo.sp_dropserver @server=N'LinkServer2012_rmt', @droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver 
@server = N'LinkServer2012_rmt', 
@provider=N'SQLNCLI',
@srvproduct = '', 
@provstr=N'SERVER=hp2\sql_2012_dev;User ID=rmt'; --now pass in rmt login 

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = N'LinkServer2012_rmt', 
@locallogin = NULL , 
@useself = N'False', 
@rmtuser = N'rmt', 
@rmtpassword = N'*****';

This time non-admin login l has no problem at all:

enter image description here

I cannot consider this my post a real answer to OP's problem, maybe the cause is Azure and I cannot test it because I have no access to Azure server. But at least it solves the same problem when linking to on-prem instance.

UPDATE

Try to not to use @provstr at all when creating linked server like this:

EXEC sp_addlinkedserver   
   @server=N'LincServer', 
   @srvproduct=N'',
   @provider=N'SQLNCLI', 
   @datasrc=N'myAzureDB.database.windows.net',
   @catalog='MyDB';
2

With the help of MS Premier Support, we were able to resolve the issue by removing all granted user permissions from the views we were using to access the linked server. That is, we could not explicitly grant SELECT permission on a view based on the linked server, but if the account accesses the view as a member of the db_datareader role, it seems to work just fine. So much for fine-grained security! MS is researching and will hopefully have an answer soon which I will be happy to share.

  • Nice find. I’ve deleted my answer so as not to lead people astray. When you have the final answer, please put it in a blog post and link to it from here. – Randolph West Aug 25 '17 at 1:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.