Access to the remote server is denied because no login-mapping exists.

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

Open in new window

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:
Linked Server configNote 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:
Be made using this security contextAny ideas how to get this working?
LVL 1
MarkAsked:
Who is Participating?
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.

Eugene ZCommented:
if you used
Be made using the login’s current security context

you can try

"Be made using this security context"

or

Local server login to remote server login mappings
0
MarkAuthor Commented:
As mentioned, I already tried both of those with my specifying a SQLserver Id and PW for "Be made using this security context". The 'mapping' was my first example wherein I used the SQLserver ID and PW for both local and remote.
0
Eugene ZCommented:
for start (I missed this )
this does not use Linked server
select memberId, orgMemberType, memberType
FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes','EXEC hprs.dbo.actuaryPension_sp')
more BOL
https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-2017
-----------------------------------------------------------------------------------------
#2  try

select * from yourlinkedserver.DBname.Dbo.sometable

follow step-by step from this link
How to create and configure a linked server in SQL Server Management Studio
      by Marko Zivkovic
https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-in-sql-server-management-studio/

#3
what are your sql server versions \sp #?
can you check and post  related sql errors on you "destination" sql server?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

MarkAuthor Commented:
Eugene Z: Sorry, not making much progress. In your "for start" comment, that link doesn't make it clear to me why my query is not using a linked server. Perhaps an example would help.

#2: I followed the steps in that link, but got the error "You cannot create a local SQL Server as a linked server". The server I want to use *is* the local server, so ...?

SQL Server version is 2014 Express.

Do I *need* a linked server for the OPENROWSET()? I can run this query as sa, so perhaps it's a permission/privilege thing?
0
Eugene ZCommented:
OPENROWSET - is not using "LINKEDSERVER"' -- it is "like" connection string in  e.g. C# apps  -->

"You cannot create a local SQL Server as a linked server""
it means you must use "server name"   -- on server that you try to link -- run Select @@servername
and use it instead of 'Local'

same for linkedless OPENROWSET - -try to use "real sql server name"

---

review the above posted links
0
MarkAuthor Commented:
I tried:
select top 10 memberId, orgMemberType, memberType FROM OPENROWSET('SQLNCLI', 'Server=DBSERVER\MEMBERSLINK;sqluser;password','EXEC hprs.dbo.actuaryPension_sp')

Open in new window

(@@servername is DBSERVER\MEMBERSLINK)

Same error. As usual, works as user sa

Eugene Z: thanks for your patience on this, but unless you have some magic incantation up your sleeve, I'm going to give up on this. It was intended to save me time specifying each column of the stored procedure in a temp table, but this attempt to save time is taking WAY MORE TIME. Not untypically, Microsoft's "security" is getting tangled in its own underwear! On an OPENROWSET invoked from local server to local server, why doesn't it just use the invoking user's credentials? Sigh.
0
Eugene ZCommented:
can you stop using "OPENROWSET"
and use Linked server as I asked?

try set poper linked server -> not Localhost but using actual sql server name DBSERVER\MEMBERSLINK?
just follow above link or BOL

https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-2017
or these steps
--steps 1 from SSMS when loog in on the "main" sql server where from you plan connect DBSERVER\MEMBERSLINK
USE [master]  
GO  
EXEC master.dbo.sp_addlinkedserver  
    @server = N'DBSERVER\MEMBERSLINK',  
    @srvproduct=N'SQL Server' ;  
GO  

--step 2 -- add login to linked server (just make sure this sql login- pwd is created and tested  on DBSERVER\MEMBERSLINK

EXEC master.dbo.sp_addlinkedsrvlogin  
    @rmtsrvname = N'DBSERVER\MEMBERSLINK',  
    @locallogin = NULL ,  
    @useself = N'False',
@rmtuser=N'yoursqluser',
@rtmpassword=N'yoursqluserpwd';


--3 test it

SELECT name FROM [DBSERVER\MEMBERSLINK].master.sys.databases ;  
GO  
GO
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
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
SQL

From novice to tech pro — start learning today.