SQL Linked Server returns error "no login-mapping exists" when non-admin account is used

Question

I have a local SQL Server 2008R2. I have configured Linked Server to a remote database.

The Linked Server works great when I login to the local server using a SQL-login account with sysadmin server role. I can query against the remote server, so I know the Linked Server setting is correct. However, I would get the error below if I use an account that does not have the sysadmin server role.

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

For both local and remote servers, SQL login is used (Windows authentication is not used)

What kind of security I need to configure for a regular SQL-login account to use Linked Server?

Answer 1

UPDATE: See @Anton's and @Wouter's answer for alternative solution.

According to this blog, I have to specify User ID in the provider string if non-sysadmin accounts are used. Here is an example.

EXEC master.dbo.sp_addlinkedserver 
    @server = N'MyLinkServerName',
    @provider = N'SQLNCLI',
    @srvproduct = 'SQLNCLI',
    @provstr = N'SERVER=MyServerName\MyInstanceName;User ID=myUser'

This exactly matches what I have encountered and it solves my problem.

Answer 2

As alternative solution you can use the parameter @datasrc instead of @provstr. @dataSrc works without setting the User ID

Sample:

EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', @provider=N'SQLNCLI',@srvproduct = 'MS SQL Server', @datasrc=N'serverName\InstanceName' 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName', @locallogin = NULL , @useself = N'False', @rmtuser = N'myUser', @rmtpassword = N'*****'

I've added a comment here, too, but it's not visible (don't know why).

Answer 3

After playing around with this, i found that you can avoid having to use the User ID property altogether, by using @datasrc instead of @provstr. This is very poorly documented, but the example below works for me:

EXEC master.dbo.sp_addlinkedserver
    @server = 'SOME_NAME',
    @srvproduct='', -- needs to be explicitly empty, default is NULL and is not allowed
    @datasrc='some_server.com\SOME_INSTANCE',
    @provider='SQLNCLI';

-- Set up the Linked server to pass along login credentials
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname='SOME_NAME',
    @useself='true', -- Optional. This is the default
    @locallogin=NULL; -- Optional. This is the default

Using this method you can reuse the same Linked server for all of your users. The currently accepted answer has the huge drawback that you need to set up a separate linked server for each user.

Answer 4

You can also add remote server login mapping by right-clicking on your remote server -> Properties -> Security -> Add