September 26, 2017 at 3:03 am
Hi all
I have a stored procedure that selects data from two instances. If I run the stored procedure it works OK. I need to run it every night so I set up a SQL Agent Job to send out an E-Mail with the results attached. The job fails and in the SQL Server Profiler, it appears to fail because "Linked servers cannot be used under impersonation without a mapping for the impersonated login".
I login using my Windows credentials and I have sysadmin rights on both servers. Microsoft Support have logged in remotely to look at this and have changed both SQL Agent accounts to login as me and played about with the linked server settings but it still doesn't work. Searching for the error online seems to confirm there is a problem with this scenario but I couldn't find a solution. One post said that linked servers are an issue and there are workarounds but didn't give any more detail.
Sorry if I've missed out anything relevant, I'm new to this. Any help or pointers would be appreciated.
Regards
Neil
September 26, 2017 at 3:08 pm
neil.thompson-696906 - Tuesday, September 26, 2017 3:03 AMHi all
I have a stored procedure that selects data from two instances. If I run the stored procedure it works OK. I need to run it every night so I set up a SQL Agent Job to send out an E-Mail with the results attached. The job fails and in the SQL Server Profiler, it appears to fail because "Linked servers cannot be used under impersonation without a mapping for the impersonated login".
I login using my Windows credentials and I have sysadmin rights on both servers. Microsoft Support have logged in remotely to look at this and have changed both SQL Agent accounts to login as me and played about with the linked server settings but it still doesn't work. Searching for the error online seems to confirm there is a problem with this scenario but I couldn't find a solution. One post said that linked servers are an issue and there are workarounds but didn't give any more detail.
Sorry if I've missed out anything relevant, I'm new to this. Any help or pointers would be appreciated.
Regards
Neil
How is the security mapping for the linked server set up and is the owner of the job a sysadmin?
Sue
September 27, 2017 at 1:35 am
Hi Sue
Thanks for taking an interest. The owner of the job is me and I am a member of the sysadmin group on both instances. There are no specific local to remote login mappings and the general setting is "Be made using the login's security context".
Neil
September 28, 2017 at 12:06 pm
neil.thompson-696906 - Wednesday, September 27, 2017 1:35 AMHi Sue
Thanks for taking an interest. The owner of the job is me and I am a member of the sysadmin group on both instances. There are no specific local to remote login mappings and the general setting is "Be made using the login's security context".
Neil
I'd make sure they are no mappings at all - even if you have the Be made using the logins security context selected.
It almost sounds like it's not seeing you as a sysadmin when running the job. Check each step and make sure that nothing is selected for Run as on the steps. You might also consider running an Extended events session or a trace, filter on SQLAgent and see if it's executing:
EXECUTE AS LOGIN
when the job or job step starts. I don't think there is a general issue with this scenario and I've done quite a few this way without any issues.
The other workaround people have mentioned is likely using sql logins where be made using this security context is set to a sql login that is on both servers. In that scenario, the issue is often just related to Kerberos. You could check that as well and make sure the SPNs are created and if services are running under your account, make sure your account (or the service account if you changed it) is selected for delegation. In active directory, the account needs to have the "Account is sensitive and cannot be delegated removed, unchecked on the account settings. On the delegation tab, the trust this user for delegation needs to be selected. If that tab is missing, the SPNs have not been created. You could check the Kerberos configurations using this tool:
Microsoft Kerberos Configuration Manager for SQL Server
Sue
September 29, 2017 at 12:50 am
Appreciate the help Sue but we're reaching the limits of my knowledge. As far as I can tell, there are no mappings at all. There's only one step on the Agent job and there's no Run As set. I couldn't figure out how to run the Extended Events Session but I ran a Profiler Trace and it does Execute As Login with my user name.
In Active Directory, the "Account is sensitive..." is unchecked for me but I don't have a delegation tab so I guess the SPNs aren't created. I don't understand SPNs or Kerberos so I'll have to talk to our server guy about this.
Thanks for the pointers though.
Neil
September 29, 2017 at 8:23 am
neil.thompson-696906 - Friday, September 29, 2017 12:50 AMAppreciate the help Sue but we're reaching the limits of my knowledge. As far as I can tell, there are no mappings at all. There's only one step on the Agent job and there's no Run As set. I couldn't figure out how to run the Extended Events Session but I ran a Profiler Trace and it does Execute As Login with my user name.
In Active Directory, the "Account is sensitive..." is unchecked for me but I don't have a delegation tab so I guess the SPNs aren't created. I don't understand SPNs or Kerberos so I'll have to talk to our server guy about this.
Thanks for the pointers though.
Neil
The job is only suppose to do the the Execute As if the login is NOT a sysadmin and it will impersonate the owner of the job. If the job is doing Execute as Login with your user name, it seems it doesn't see your login as a sysadmin. That makes me wonder if something isn't messed up with the accounts in some way. You may want to execute sp_validatelogins (just execute that as is - no parameters) and see if any or what logins come back as no longer existing in the Windows environment. I'd also check to see if you have a local account on that server as well.
Sue
October 1, 2017 at 4:57 am
I've run sp_validatelogins on both instances and no logins were returned. There's no local accounts on the server either. I've checked under the instance Security\Logins and I'm listed in both instances with sysadmin membership. The only slight difference between the two instances is that in Database\Users on the first instance, the dbo user uses my login name whereas in the second instance the dbo user doesn't have a login name and there is a separate user for me. The first instance is the one that runs the Agent job. I'm not sure if that is relevant?
Neil
October 3, 2017 at 3:17 pm
Not sure but it just seems like something isn't right with the accounts. Sorry, I have no other ideas.
As I mentioned earlier, the scenario will work but the workaround others had mentioned would be to use SQL Server logins instead of windows. Map the linked server using a sql login and create the login on the other server with the necessary permissions for that part of the stored procedure.
Sue
October 4, 2017 at 8:49 am
Thanks for your help Sue. Unfortunately one of the instances is set to Windows only logins so I can't use the workaround yet until I talk to the owner. If I get a solution to the original problem, I'll post it back here.
Neil
November 6, 2017 at 4:43 am
Hi Sue
We've changed the second instance to accept both SQL and Windows logins and the workaround with SQL Logins has fixed the problem.
Neil
November 6, 2017 at 7:56 am
neil.thompson-696906 - Monday, November 6, 2017 4:43 AMHi Sue
We've changed the second instance to accept both SQL and Windows logins and the workaround with SQL Logins has fixed the problem.
Neil
Thanks for posting back Neil - glad you got it working now.
Sue
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply