job not work on linked servers

Category: sql server security

Question

Sergio Soriano González on Tue, 11 Dec 2012 12:46:53


Hi guys,

I have linked two sql servers:

- An instance of Sql Server 2000 SP4,  on a Windows Server 2003
- An instance of Sql Server 2008 R2, on a Windows Server 2008

Both servers are computers in an AD domain.

I did the linking of the instances from sql server 2000, and works OK.

The problem is:
 
I have done a sql job, whose owner is the same as the user who launch it. (User with the same permissions and logins in both instances).

The job was done in 2000, and in the Transact-SQL what I wrote was a select in the linked server (sql 2008).

Well, throws me the following error:

(by spanish machine) ... sorry by the traduction.
'job name': Step 1, 'step name': starts at 12.11.2012 11:12:04...
Post 18456, severity 14: Failed login for user 'NT AUTHORITY \ ANONYMOUS LOGON'. [SQLSTATE 28000]

Searching in internet, after more tests, and testing with others users and credentials,  I keep seeing that the user fails to log onto the server that I have linked, but as I say, the user who launch the job, has credentials and it could login in both servers.

Thank you very much in advance.

Replies

Uri Dimant on Tue, 11 Dec 2012 12:49:54


>>>>Post 18456, severity 14: Failed login for user 'NT AUTHORITY \ ANONYMOUS LOGON'. [SQLSTATE 28000]

What is account that SQL Server Agent runs under? NT AUTHORITY \ ANONYMOUS LOGON?????????

mc_3791 on Tue, 11 Dec 2012 19:59:31


Check the properties of the linked server. It sounds like the option of "Be made without using a security context" for logins not defined in the mappings might be selected. Make sure that the option "Be made using the login's current security context" is selected.

Erland Sommarskog on Tue, 11 Dec 2012 22:55:16


The job runs under SQL Server Agent's service account. Inside SQL Server, SQL Agent can impersonation the owner. This impersonation is null and void when you connect to the linked server, because this impersonation happens inside SQL Server, and Windows is too smart to fall for that trick. That is, when the originating server connects to the remote server it acts like a client, and goes through the same process. There is on secret SQL Server-to-SQL Server chanell. So the target server will ask Windows "who is this guy knocking on my door" and Windows will say "The SQL Server Agent service account". Which seems to be Local Service or somesuch in this case. And it is shown the door.

Sergio Soriano González on Wed, 12 Dec 2012 09:45:44


Hi all, thank for your replies.

For mc 3791

That is not the problem, the security context is established as "established using the current security context logon" and the account has credentials.

For Uri Dimant and Erland Sommarskoq

There is something I not quite understand ...
what is the account that runs under the SQL Agent? I guess this account is the same that the account that launches the job, no?

This account has the same credentials for both servers in logons and database users.

I don't know because is launched by NT Authority\anonymous logon account...(That's the problem).

Great explanation, Erland!,   I seem you understood very good my problem, what should I do?... how can I change the account that launches sql Agent? this, would it help for something?

Many thanks in advance!

V. Keerthi Deep on Wed, 12 Dec 2012 09:52:12


To change the SQL Agent Service account

Start -> SQL Server -> Configuration Tools -> Configuration Manager -> Properties of the SQL Server Agent Service Ex: SQL Server Agent(Instance_Name) -> On the "Log On" tab you could change the service account.

JoeyDj on Wed, 12 Dec 2012 11:00:28


you should define the user in the link server using the "execute as " clause

please consult this link

http://thomaslarock.com/2009/04/execute-as-and-linked-servers/

Sergio Soriano González on Wed, 12 Dec 2012 13:58:26


Hi again,

V. Keerthi Deep:

 I have done it!, but the problem is the same. I get the same error message.

JoevDj:

The account that launches the job was defined, of course, and it's in "execute as" clause.... is an user account of domain with credentials in both servers... 

Thank you for the link, explained the problem, but the author says that he couldn't find any solution... as I (at the moment).

I really appreciate your help, guys. thanks!

Erland Sommarskog on Wed, 12 Dec 2012 22:50:51


Yes, it may work better if SQL Server Agent has the same domain account as service account as the remote server.

Sergio Soriano González on Thu, 13 Dec 2012 11:37:09


I cannot get solve it.

I have decided to do it with a windows service.

Thanks for your help.

Bye.

SKB7 on Thu, 13 Dec 2012 15:51:58


hi,

check sql server agent account which is used for running the job have proper permissions.

http://msdn.microsoft.com/en-us/library/ms143504(v=sql.105).aspx

Thanks,

SKB.