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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.