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]
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?
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]
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?
ASKER
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 ?
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 ?
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.
ASKER
If that is the case then why does SQL allow you to use different credentials by using the method I have used?
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The solution you declare that you found yourself is the solution that I described. You just define it another way.
ASKER
Found my own solution in the end.
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"