I have a stored procedure with the following line:
select memberId, orgMemberType, memberType
FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes','EXEC hprs.dbo.actuaryPension_sp')
I can run this as user sa, but as a normal user I get "Access to the remote server is denied because no login-mapping exists." I've created a linked server per http://sqlserverplanet.com/dba/how-to-add-a-linked-server
Note that "Connection:" (lower left of above image) says, sa.
On the "Security" page, I've tried entering the SQL Server ID of a user in the "Local server login to remote service login mappings" - same error
"Be made using the login's current security context" - same error
"Be made using this security context", and I've entered the SQL Server ID and password of the desired normal user - same error
"Be made without using a security context" - In which case I get "The linked server has been updated but failed a connection test", this simply by clicking OK, no actual attempt to run the procedure:
Any ideas how to get this working?