SQL Linked Server returns a "no username mapping" error when using an account without administrator rights

Question

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

The linked server works fine when logging into the local server using the SQL login account with the sysadmin server sysadmin . I can request a remote server, so I know that the Linked Server parameter is correct. However, I would get the error below if I use an account that does not have the sysadmin server sysadmin .

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

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

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

Answer 1

According to this blog , I have to specify the User ID in the provider line if accounts other than sysadmin 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 came across and solves my problem.

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

Answer 2

As an alternative solution, you can use the @datasrc parameter instead of @provstr. @dataSrc works without setting a user ID

Example:

 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 added a comment here , but it is not displayed (I don’t know why).

Answer 3

@datasrc with this, I found that you can completely avoid using the User ID property 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 your users. Currently, the accepted answer has a huge drawback in that you need to set up a separate linked server for each user.