INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Linked server : access to the remote server is denied because no login-mapping exists.

Linked server : access to the remote server is denied because no login-mapping exists.

Linked server : access to the remote server is denied because no login-mapping exists.

(OP)
I am having problem a getting a linked server to work. My problem is when I am running a query using a linked server to another server it is getting the following error for a particular user :

access to the remote server is denied because no login-mapping exists.

I have a user called "crystal2" on ServerA.
I have a linked server pointing to ServerB. In the linked server options I am selecting the following option in the security tab :
For a logon not defined in the list above, connections will: Be made using this security context (I then enter the credentials of the remote logon)

The user crystal2 has public role only, and is mapped to a database on ServerA with public and db_datareader rights only. Whenever crystal2 executes a query using the linked server, such as SELECT TOP 10 * FROM [ServerB].[Database].[dbo].[Tablename] it throws the error :

access to the remote server is denied because no login-mapping exists.

If I then change the rights user crystal2 has and add the sysadmin role, the linked server query works fine.

How do I get the query to work without adding the sysadmin role, as the crystal2 user should only have read only rights to the databases it is accessing?

RE: Linked server : access to the remote server is denied because no login-mapping exists.

What permissions does Crystal2 have on the database on ServerB? It looks like they don't have any permissions. If so, that's your problem...they can't access the database on ServerB.

You say you've set the connection to use the "remote login", but you don't say what permissions that login has.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

RE: Linked server : access to the remote server is denied because no login-mapping exists.

(OP)
Thanks for your reply.

Crystal2 does not have any permissions on ServerB which is why I am using the option "Connections will: Be made using this security context", to use a different user on the remote server.

The remote login details have readonly access to ServerB, and I have tested this working in Management Studio on the remote server.

I was under the assumption that using this option would allow me to use a different logon to get access to the data on ServerB without having to setup additional logins on ServerB. Are you saying that I still need to create Crystal2 on ServerB, even though I am specify different login details to access ServerB ?

RE: Linked server : access to the remote server is denied because no login-mapping exists.

Try running this:

EXEC sp_addlinkedsrvlogin '<linkname>', 'false', 'Crystal2', '<remotelogin>', '<remoteloginpassword>'

Change the values that have <> to what they should be.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

RE: Linked server : access to the remote server is denied because no login-mapping exists.

(OP)
Hi SQLBill,

I tried this and it did not work.

I am came across this post http://blogs.technet.com/b/mdegre/archive/2011/03/... in my search for a solution.

Adding the User ID of the remote user to the provider string for the linked server, fixed my problem.

Thanks for your help

RE: Linked server : access to the remote server is denied because no login-mapping exists.

Cool. I'm glad you got it resolved.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close