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

Mark
Mark used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

Author

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.

Commented:
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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?

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

Author

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.
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial