22

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?

Tony
  • 1,827
  • 1
  • 22
  • 23
  • 1
    If you right click . properties on the linked server definition and go to the Security tab, how is it set up? Alternatively script the linked server as Create and paste the `sp_addlinkedsrvlogin` part(s) (sanitising passwords etc.) – Nick.Mc Aug 19 '15 at 00:31
  • What @Nick.McDermaid said. If it says Not be made, you need to add a mapping to the list OR provide one of the 3 alternate options below in the radio buttons. – Brad D Aug 19 '15 at 00:48
  • @Nick.McDermaid, @BradD : I have added mapping to the list. I have tried `Not be made` and `Be made using this security context`, and both options did not help. However as soon as I added `sysadmin` server role to the local SQL account, it worked instantly. Why? – Tony Aug 19 '15 at 07:13
  • I can't help unless you do as I asked originally - script out the `sp_addlinkedsrvlogin` parts. Otherwise I'm wasting my time trying to guess what your configuration is. I'm not aware of any kind of `sysadm` override. – Nick.Mc Aug 19 '15 at 07:43

5 Answers5

20

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.

Tony
  • 1,827
  • 1
  • 22
  • 23
  • Over a year later and you solved one of my head scratchers! – Nathan C Nov 21 '16 at 17:29
  • 1
    Note that the "User ID=" bit can be anything, i.e. "User ID=MickeyMouse", and it will work. Personally I prefer @Wouter's solution -- just leave out the "@provstr" parameter entirely, in favour of "@datasrc". – Mike Jan 10 '23 at 02:52
  • 1
    is it some bug !? for me that worked also but this is so strange – d00lar Mar 27 '23 at 12:56
6

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).

Anton R
  • 76
  • 1
  • 2
  • 2
    I prefer your solution because the other doesn't address how to make it work if you have multiple User ID's – Dave Jan 23 '19 at 21:56
4

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.

Wouter
  • 1,829
  • 3
  • 28
  • 34
  • Actually the "User ID=" bit can be anything, i.e. "User ID=MickeyMouse", and it will work. So you wouldn't need to have one Linked Server per user. Even so, I agree your approach is better -- just leave out the @provstr parameter entirely. – Mike Jan 10 '23 at 02:50
3

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

properties

login mappings

Nace Kapus
  • 71
  • 6
0

I tried @Wouter 's & @anton-r 's solutions and neither worked for me, but this did!

This is straight from Pinal Dave's solution (https://blog.sqlauthority.com/2019/06/07/sql-server-fix-msg-7416-access-to-the-remote-server-is-denied-because-no-login-mapping-exists/)

EXEC master.dbo.sp_addlinkedserver @server = N'mylsname', @srvproduct=N'SQL', @provider=N'SQLNCLI11', @datasrc=N'123.45.6.789', @provstr=N'Encrypt=yes;TrustServerCertificate=yes;User ID=user_no_login'

GO

Interestingly, I do NOT have "user_no_login" on that server - I meant to change it to a user on that machine, but forgot.

Also of note: if you have the IP in the @provstr instead of in @datasrc then while it connects to (local server, maybe?!), it doesn't connect to the server you're trying to connect to.

mbourgon
  • 1,286
  • 2
  • 17
  • 35