Tony Tony - 1 year ago 130
SQL Question

SQL Linked Server yields error "no login-mapping exists" if non-admin account is used

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

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
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download