Trouble with getting a linked server working : access to the remote server is denied because no login-mapping exists.

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?
chriscboyAsked:
Who is Participating?
 
chriscboyAuthor Commented:
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
0
 
tigin44Commented:
As I understand that you have created "crystal2" as a login on the linked server target database server.  You also map that login to the database as a user and give the appropriate privileges.

to do this connect to your target server
navigate to the Logins under Security
select your login "crystal2"  and open up properties
you can set the mapping options under "User Mapping"
0
 
chriscboyAuthor Commented:
Thanks for your answer but why do I need to create the crystal2 logon on the target server if I am using the "Be made using this security context (I then enter the credentials of the remote logon)" option?

I thought the whole point of the above option so that you can specify a different user on the target server without having to duplicate user credentials everywhere ?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
tigin44Commented:
In order to access to a database on any mssql server you need to define a "login" to connect to the database server and map that login to the databases to grant access and set the privileges.  While defining a linked server you should use an account which currently has access to the target database. This is the way mssql server security mechanism works.
0
 
chriscboyAuthor Commented:
If that is the case then why does SQL allow you to use different credentials by using the method I have used?
0
 
tigin44Commented:
By this MsSql Server offers you different authentication systems and based on your environment you can choose any of them. I was just commenting on the system you are using on which you are setting your linked server to access to the target server under an sql server authenticated user privileges (crystal2).
0
 
tigin44Commented:
The solution you declare that you found yourself is the solution that I described. You just define it another way.
0
 
chriscboyAuthor Commented:
Found my own solution in the end.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.