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

sql sql-server security linked-server
Tony picture Tony · Aug 19, 2015 · Viewed 35.2k times · Source

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

Tony picture Tony · Aug 19, 2015

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.

Related questions

Cannot create SSPI context

I am working on a .NET application where I am trying to build the database scripts. While building the project, I am getting an error "Cannot create SSPI context.". This error is shown in the output window (inside VS2008 screen) …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More