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

[chriscrowhurst]

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?

 

[SQLBill]

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

 

[chriscrowhurst]

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 ?

 

[SQLBill]

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

 

[chriscrowhurst]

Hi SQLBill,

I tried this and it did not work.

I am came across this post

http://blogs.technet.com/b/mdegre/a...s-denied-because-no-login-mapping-exists.aspx

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

 

[SQLBill]

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