Sql-server – Linked Server to Azure Error: “Access to the remote server is denied because no login-mapping exists.”

azure-sql-databaselinked-serversql server

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

Best Answer

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

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:

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';

Related Solutions

Sql-server – Insert to SQL Azure through Linked Server very slow

I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes

Remote data modifications through a linked server use the sp_cursor model. The effect is similar to issuing 1000 separate single inserts (one for each row). If a round trip takes 250ms, 1000 such trips will take 4 minutes and 10 seconds. Using a bulk loading method such as bcp or SSIS will generally be more efficient (unless the number of rows to be inserted is tiny).

Another alternative for ad-hoc needs is to build a string containing a single INSERT statement with multiple rows in the VALUES clause. The statement is then used with EXECUTE AT, for example:

-- Note local-to-azure table name used, not four-part syntax
DECLARE @sql varchar(max) = 'INSERT dbo.Test VALUES ';

-- Build a list of 1000 numbers to insert    
SELECT @sql += '(' + CONVERT(varchar(11), n) + '),'
FROM dbo.Numbers AS N
WHERE n BETWEEN 1 AND 1000;

-- Remove trailing comma
SET @sql = LEFT(@sql, LEN(@sql) - 1);

-- For debugging
PRINT @sql;

-- Execute the finished statement at the remote server;
EXECUTE (@sql) AT AZURE;

The constructed insert statement executes at the Azure database, so a local name is used for the table (note: building a dynamic insert statement using a four-part target name buys you nothing).

Note that EXECUTE ... AT requires RPC and RPC OUT enabled in the linked server options.

The VALUES clause for a plan INSERT statement is limited to 1000 elements. There are ways around that (a VALUES clause in a CTE does not have that restriction). You could also choose to build 1000-row batches or build single-row insert statements, but if the there is that much data on a regular basis, you would probably be better off using one of the bulk loading methods instead.

Sql-server – Linked Server Error – Login Failed for user ‘theaccount’

In linked servers - you'll need to make sure you have permissions on both servers that you're looking to set the link between.

So for instance - if you're sa on Server A, you'll still need at least read-only access to the database you're trying to access on Server B in order to use the linked server.

Additionally - when you're configuring your linked server, it's important to make sure your security context is set correctly.

By default, Linked Servers are made without any security context which puts the responsibility for authentication on the connection string you specify.

Usually you'll want to use the credentials of who is attempting to connect through the linked server by selecting "Connections will be made using the login's security context", but this varies based on you and your companies needs.

More information on linked servers can be found on technet:

General Linked Servers - https://msdn.microsoft.com/en-us/library/ms188279(v=sql.105).aspx

Linked Server Security - https://technet.microsoft.com/en-us/library/ms188477(v=sql.105).aspx

Best Answer

Related Solutions

Sql-server – Insert to SQL Azure through Linked Server very slow

Sql-server – Linked Server Error – Login Failed for user ‘theaccount’

Related Question