Mark
asked on
Access to the remote server is denied because no login-mapping exists.
I have a stored procedure with the following line:
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?
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?
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','EXE C 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.Db o.sometabl e
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?
this does not use Linked server
select memberId, orgMemberType, memberType
FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_
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.Db
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?
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?
#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
"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
ASKER
I tried:
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.
select top 10 memberId, orgMemberType, memberType FROM OPENROWSET('SQLNCLI', 'Server=DBSERVER\MEMBERSLINK;sqluser;password','EXEC hprs.dbo.actuaryPension_sp')
(@@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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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