Running SQL queries on a linked server using impersonation

Question

Running SQL queries on a linked server using impersonation

Mando 1

Okay, I am finally posting this here because every other Q&A I've found is talking about using impersonation on a SQL Agent Job and I don't need that (yet). I simply just trying to execute SQL queries in an SSMS window using impersonation to view data on a linked server.

I've gone through the following link extensively:
https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188304(v=sql.105)?redirectedfrom=MSDN

And this blog post explains the conundrum perfectly, but it's like 12 years old... so surely SOMEONE has gotten this to work, right?

Anyways, here's a rundown of my config:

Local Server: (LocalSQLUser & LocalDomainUser)
DB Trustworthy ON
SPNs created
Delegation Enabled
Linked Server connection created (Mapped LocalSQLUser to RemoteDomainUser, With all connections using current login's security context)

Remote Server: (RemoteDomainUser)
DB Trustworthy ON (RemoteDBOwner granted AUTHORIZE)
SPNs created
Delegation Enabled
Permissions granted to a specific view for RemoteDomainUser

Currently getting anonymous login error message when trying to run SELECT on view after successfully running EXECUTE AS [RemoteDomainUser].

4 answers

Your answer

Answer 1

I am not sure that I get things together here. First of all, having the databases trustworthy is not going to help, but it is a possible security risk.

You say:

Linked Server connection created (Mapped LocalSQLUser to > RemoteDomainUser, With all connections using current login's security context)

This is kind of contradictory. Have you set up login-mapping, or are you relying on self-mapping? I realise that you talk from the UI, but the UI is more confusing than useful. Please use the scripting button in the UI and post the script, so we can see how the server is set up. If you mask actual data, please make sure that you do it consistently.

If you have self-mapping, this means that you log in on the remote server as yourself. You cannot do EXECUTE AS to impersonate a different login and access the other server with that login. The impersonation you do is only valid in your own SQL Server, but not in Windows or any remote data sources - that would be a major security hole, as that you would permit you to impersonate an admin to elevate your permissions.

If you have set up a login mapping for a specific login, you can impersonate that login, but I stop here, until I understand your configuration better.

Mando 1 Reputation point

2021-04-23T14:32:14.953+00:00

Thanks for the reply, E. I may have spewed too much in my initial post, so apologies for that. Ideally, we want this to work strictly with domain accounts, so let's scratch the whole local login for now. As for the script, here's what I got...

EXEC master.dbo.sp_addlinkedsvrlogin @rmtsrvname=N'[Remote Server]',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL GO

I found this post just now where it sounds like this person got it to work (Bullet Point #2 by Paul Fuller) so I'm hoping that means it's been done before...

Answer 2

CarrinWu-MSFT 6,891

Hi @Mando ,

Welcome to Microsoft Q&A!

Please refer to this link and this blog to get more information. They are might be helped.

Best regards,
Carrin

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Erland Sommarskog 121.4K MVP Volunteer Moderator

EXEC master.dbo.sp_addlinkedsvrlogin @rmtsrvname=N'[Remote >Server]',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmt>password=NULL

So you have self-mapping.

To re-iterate of what I said in the last post, if the desire is that access to the linked server should be by the Windows login REMOTE\User by preceding the access to the view with

EXECUTE AS LOGIN = 'REMOTE\User'

this not going to work. The impersonation you make with EXECUTE AS LOGIN is only valid inside your SQL Server instance. Imaging that REMOTE\User is an admin account on the remote server on which you at most you have humble permission. By impersonating this login on your local server where you admin you could take over the remote server.

If self-mapping is not an option to you, that is, the users on the local machine do not have access on their own to the remote server, you must set up login-mapping in one way or another and that login-mapping must be to an SQL-login - it cannot be a Windows-login.

It could help if you could spell out your requirements in more detail. I may have more tips, but I don't want to drop them now, as they may be off the mark.

Mando 1 Reputation point

2021-05-21T19:42:04+00:00
I need to be able to impersonate a domain user on the Server A, and execute SQL queries as the impersonated user that pulls data from Server B as a linked server. So basically the bullet point I posted earlier where he says he was able to do it successfully, so I'll paraphrase it below:

Login as LocalDomainUser

Switch security context (EXECUTE AS LOGIN = 'RemoteDomainUser')

Send the "GO" command

Get data from "MyTable" (SELECT * FROM [LinkedServer].[Database].[Schema].[MyTable])

Send the "GO" command"

Self-mapping is the only option currently. I just got Kerberos configured on both servers to work and verified via the double-hop method.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-05-21T21:20:00.4+00:00

Send the "GO" command

Nit-pick here: GO is not a command that you send. GO is not an SQL command, but an instruction to SSMS to split up the text in batches and send one batch at a time. So you can select the entire script at once and press Execute, and SSMS will send one the statements one by one, if there is a GO after each of them.

You are saying that this works for you? In such case, I assume that RemoteDataUser maps to an SQL login on the other server. You cannot use self-mapping, if you have performed impersonation inside of SQL Server.
Mando 1 Reputation point

2021-05-21T22:58:28.59+00:00

Haha. I hear ya on the GO thing. But that was what what copied from the other post I linked that had supposedly worked for someone. And thats what I'm trying to get to work. But I guess what it is sounding like is that it is possible with SQL authenticated accounts, but not Windows authenticated ones.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-05-22T09:14:44.15+00:00

But I guess what it is sounding like is that it is possible with SQL authenticated accounts, but not Windows authenticated ones.

Depends on what you mean what is possible. In neither case it is possible to do EXECUTE AS LOGIN and then get self-mapping to work.

But if you want users on server A to access server B with a different account than their own, that different account must be an SQL login, that's correct. There are, however, a few variations how you can deal with the login-mapping.

Answer 4

CarrinWu-MSFT 6,891

Hi @Mando , I saw your discussion with Erland, any attempt to access resources outside of the database will cause EXECUTE AS statement to fail. Please refer to EXECUTE AS (Transact-SQL) to get more information.

Mando 1 Reputation point

2021-05-21T19:43:07.803+00:00

Appreciate the feedback! This article from Database Journal seems to state otherwise though...

"Impersonation across Linked Servers
Impersonation can get a little tricky when the task at hand crosses SQL Server linked servers. If impersonation is being handled using SETUSER, a call that crosses linked servers will fail. The context needs to be reverted to the original user prior to crossing servers.

With the introduction of EXECUTE AS, crossing link servers as an impersonated user or login becomes a possibility. To make this happen, the principle (user\login) needs to be recognized on the linked server. Additionally, the linked servers need to be trustworthy. To make the databases trustworthy, you will need to set their TRUSTWORTHY property to TRUE by issuing an ALTER DATABASE command such as this:

ALTER DATABASE mydatabase
SET TRUSTWORTHY ON"

Share via

Running SQL queries on a linked server using impersonation

4 answers

Your answer