Query to a server linked through an "EXECUTE AS"

Michel PRIORI 21

Hello,
For security reasons we have a SRVLINK linked server that associates a local SQL account with a remote account.

and outside of this association connections are refused.

The problem comes when you execute:
EXECUTE AS LOGIN= 'SRVLINK
SELECT * FROM OPENQUERY([SIPRDSCO], 'select * FROM EXSDTA.PRREELLE WHERE 1=2')
REVERT
If we execute line by line it's Ok
If we execute all 3 lines together we get the error :
Msg 7416, Level 16, State 1, Line 3
Access to the remote server is denied because no login-mapping exists.

The objective being to generalize the practice in stored procedures - so no intermediate GOs are possible.

Q1: How to respond to security injunctions?
Q2: Why doesn't M$ allow the use of security groups for linked servers?

4 answers

Erland Sommarskog 111.2K Reputation points MVP

2020-11-20T23:03:12.907+00:00
You need to change it to:

EXECUTE AS LOGIN= 'SRVLINK' EXEC (' SELECT FROM OPENQUERY([SIPRDSCO], ''select FROM EXSDTA.PRREELLE WHERE 1=2'')' REVERT

The reason for this is that when you execute the stored procedure, SQL Server wants to build a query plan for the entire procedure. And that includes queries in OPENQUERY. So it attempts to access the linked server under the security context that applies when the stored procedure starts. By wrapping the select in dynamic SQL, you push into an inner scope and compilation is now performed under the security context of SRVLINK.
Please sign in to rate this answer.

2 people found this answer helpful.
Michel PRIORI 21 Reputation points

2020-11-24T08:04:10.633+00:00

Hi ErlandSommarskog,

Thanck for your advised reply.

Points are about :

temporary table visibility within EXEC instruction

creation and test the inner SELECT

script maintenance when the inner SELECT is about 100 more lines.

Why there is no GRANT possible for linked databases objects ? :(

Erland Sommarskog 111.2K Reputation points MVP

2020-11-24T22:06:19.637+00:00

I am not sure that I understand your point about temp tables, but temp tables created outside the dynamic SQL is visible inside of it.

If the SELECT is very long, an alternative is to push the query to an inner stored procedure. That procedure would be compiled successfully, since it is invoked in the context of the impersonated login.

Don't get me started on linked servers - Microsoft could do a lot to improve that feature.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
m 4,271 Reputation points

2020-11-23T08:45:49.16+00:00

Hi @MichelPRIORI-205,

Q1: How to respond to security injunctions?

Please test with this option: Be made using this security context

Q2: Why doesn't M$ allow the use of security groups for linked servers?

It does. If your M$ has the permission, it can used for linked server.

More information: create-linked-servers-sql-server-database-engine

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.
Erland Sommarskog 111.2K Reputation points MVP

2020-11-23T22:11:36.457+00:00

Mia, for the first question, this is the option that Michel is using.

For the second question, I think Michel is asking for a feature. Today you can only set up mapping for individual users or everyone. The first is cumbersome and the far from always acceptable from a security perspective. It would be a lot easier if mapping could be set up for a group. (Or if linked server were securables, you that you could grant/deny access to them.) I have written more about this here:
http://www.sommarskog.se/grantperm-appendix.html#linkedserver
(This chapter also includes the solution that I posted above.)

Michel PRIORI 21 Reputation points

2020-11-24T08:15:13.8+00:00

Hi Mia,

As ErlandSommarskog notices in "local login" you can't setup a GROUP.
A service account (ended by $) it's not an local or domain group.

And the choise "be made using the security context" it's not a correct security option : everyone who an granted connect can use the linked server => privilege elevation.

A simple GRANT over linked server will be a good option (and then use an generic user mapping)
The counter part is 1 linked server by security scope.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
m 4,271 Reputation points

2020-11-25T06:27:58.24+00:00

Hi @Michel PRIORI ,

OK. Now I want to reproduce the issue on my own side.
Could you please describe the local login of SRVLINK and CMSCO for more details?
Two server here: Server 1 and Server2

SRVLINK is one SQL Server Login in Server 1 and what permissions this login has?
CMSCO is one SQL Server Login in Server 2 and what permissions this login has?

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.
Michel PRIORI 21 Reputation points

2020-11-25T08:03:16.287+00:00

Hi mia,

The link server is not an SQL server.
In my case it's an OLEDB AS400 one. You can test with a Postgres outside AD authentification ; for this question it will be almost the same.

Note : if you link two SQL server it's more simple ; With an AD environment, you can setup kerberos delegation (double hop). In this case, authentification made by group are take in account.

Erland Sommarskog 111.2K Reputation points MVP

2020-11-25T22:13:20.727+00:00

Note : if you link two SQL server it's more simple ; With an AD environment, you can setup kerberos delegation (double hop).

There is not really any difference. Yes, self-mapping is an option with SQL Server instances, but this is far from always permissible. For instance, in the system I spend a lot of my time on these days, we have a linked server to another SQL Server instance, but very few users have logins on that instance, and nor should we have. And the data on that instance is sensitive.

I was considering removing the mapping-for-all and apply EXECUTE AS LOGIN in the places where we access the linked server, but in our case, I could resolve the issue by removing permissions on the TCP Enpoint. (The app is a web app, and the web server and SQL Server are on the same machine.)

By the way, in my text on http://www.sommarskog.se/grantperm-appendix.html#linkedserver I also suggest a way for users to administer their own login mapping.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
m 4,271 Reputation points

2020-11-26T05:56:19.977+00:00

Hi @Michel PRIORI , @Erland Sommarskog

I have learnt much from this case and @Erland Sommarskog 's reply.
Thanks very much.

BR,
Mia
Please sign in to rate this answer.
Michel PRIORI 21 Reputation points

2020-11-26T08:14:35.307+00:00

do not hesitate, vote for him ;)

m 4,271 Reputation points

2020-11-27T05:38:58.737+00:00

Have done.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Query to a server linked through an "EXECUTE AS"

4 answers

Your answer