question

blinkbomberog avatar image
0 Votes"
blinkbomberog asked ErlandSommarskog commented

Running SQL queries on a linked server using impersonation

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://docs.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].


sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered blinkbomberog commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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


0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @blinkbomberog,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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.

1 Vote 1 ·

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.

0 Votes 0 ·
Show more comments
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered blinkbomberog published

Hi @blinkbomberog, 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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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"


0 Votes 0 ·