Linked server in SQL Server 2005 using NT authentication impersonation

Question

I am trying to Link a server using an NT authentication as the impersonated connection, but this connection will be static to a specific user instead of passing the currently logged-in user's credentials. Is there any way to do this without setting up a new SQL-server account on the server I am linking to and using that for an unlisted user.

• Eric Higgins: The remote server has the permissions granted to the login already? Is the login at the remote server AD or SQL Server auth?
• Jacrys: AD, I am trying to impersonate an NT account that has access and no, we don't have access to the remote server to change user authentication or add users
• Solomon Rutzky: Is the linked server for generic use (as in any random query) or is the usage fairly targeted and just open-ended in terms of who executes the code?
• Jacrys: The usage is targeted inasmuch as it is limited to one specific aspect of the enterprise's business lines and is really only hit for a few transactions, we are just looking for a way to optimize query execution so we are not running on 2-3 different servers, then aggregating the data on the reporting side. Using a linked server would allow us to pull the data all at once, saving user interaction time. And as to your second question, yes it could be one of about 6 people executing the T-SQL commands. I would like to avoid requesting logins for all users if at all possible.
• Solomon Rutzky: Well, I was thinking of something that now I think might not work. I was wondering if you could try setting up a linked server to use "current security context", and then issue an EXECUTE AS LOGIN='the_person_allowed_to_connect_to_remote_server';, and then see if you can do what you need over that linked server. But now I am thinking that impersonation only works on the local machine, unless you have control of the AD server and can enable that one account for "delegation". But otherwise I do not think you can impersonate a Windows Login. Although SQL Agent can via Credentials ;-)

Answer 1

If it was possible to connect to a SQL Server without setting up a security context on that server, that would be a serious security hole. So, there must be a security context.

If you can ride on some existing rights that are already defined, then fine, but likely any existing security context was not tailored for your need. And since you want a specific account it is further complicated.

Perhaps you could have a SQL Agent job created on the RemoteServer that would periodically PUSH the data to a staging table on your LocalServer. (This would also allow the RemoteServer administrators to control what happens, just in case that is an issue.)

Then you would grant rights on your LocalServer to allow the RemoteServer's job to insert data into the staging table on your LocalServer.

Note: When SQL Agent runs a job using a credential it is not impersonating the account, but should be viewed as logging in using the credential's account name and password.