Tony Tony - 4 months ago 37
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

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

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.

Comments