Link to home
Start Free TrialLog in
Avatar of Mark
Mark

asked on

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:
User generated imageNote 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:
User generated imageAny ideas how to get this working?
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

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
Avatar of Mark
Mark

ASKER

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.
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?
Avatar of Mark

ASKER

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?
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
Avatar of Mark

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial