October 31, 2016 at 9:41 am
Hi,
i'm trying to query linked server query from SQL agent job and it's failing with the following error.
i'm able to query it from query analyzer using SQL login authenticaiton. Both servers are in different domain and port has been open between the servers.
“Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’”.
I wanted to query the data from linked server and load in to local table .
Can anyone please help me on this ?
October 31, 2016 at 9:58 am
Seems like you don't have the login credentials set up correctly for the user running the agent task, as it's trying to authenicate to the other server as Anonymous.
Does the user that SQL Agent runs as have permissions to access that server? If not, does the account have permissions to impersonate, and have you set up it's impersonation up within the Linked Server properties. What have you got your default login connection to be if they aren't specified? (I'm going to assume "be made without using a security context").
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 31, 2016 at 10:12 am
Thom A (10/31/2016)
Seems like you don't have the login credentials set up correctly for the user running the agent task, as it's trying to authenicate to the other server as Anonymous.Does the user that SQL Agent runs as have permissions to access that server? If not, does the account have permissions to impersonate, and have you set up it's impersonation up within the Linked Server properties. What have you got your default login connection to be if they aren't specified? (I'm going to assume "be made without using a security context").
Thanks for the reply Tom.
Server 1 - SQL agent account is running under NT Service\SQLServerAgent
Server 2 - SQL agent account is running under different service account in different domain
so, i'm not sure if server 1 agent account has access to server 2 and also user that runs as sql agent job has access in both instances.
Linked server current login context is "Be made using the login's current security context"
Can you please explain more about this ? If not, does the account have permissions to impersonate, and have you set up it's impersonation up within the Linked Server properties. What have you got your default login connection to be if they aren't specified?
October 31, 2016 at 10:20 am
It sounds as if you're suffering from the double-hop problem. You need either to create a SQL login on the remote server for the linked server object to use, or to configure Kerberos on the remote server.
John
October 31, 2016 at 10:29 am
John Mitchell-245523 (10/31/2016)
It sounds as if you're suffering from the double-hop problem. You need either to create a SQL login on the remote server for the linked server object to use, or to configure Kerberos on the remote server.John
Hey John,
I have created the user in remote server and the linked server is actually working when tested from SSMS using SQL login but fails with the same error when used with windows authentication (with my login and i'm SA on box and sql instance )
October 31, 2016 at 10:40 am
Is Server1 the Server that is running the job? You should really be using a real account, rather than default if you need to authenticate to other areas of your network. NT Service/SQLAgentAccount is a local account, it won't exist on the 2nd server. As result, it'll have to use anonymous authentication, which, I very much doubt is enabled on your second server.
I imagine you might be doing this for more than one job, so I would suggest ensure you use a network account, with proper permissions, to run the SQL Agent Service on Server1. You'll need to then ensure that it has the correct permissions to access Server2, either by having a login on that server or using impersonation (which is set up in your Linked Server Properties).
Edit:
Robin35 (10/31/2016)
John Mitchell-245523 (10/31/2016)
It sounds as if you're suffering from the double-hop problem. You need either to create a SQL login on the remote server for the linked server object to use, or to configure Kerberos on the remote server.John
Hey John,
I have created the user in remote server and the linked server is actually working when tested from SSMS using SQL login but fails with the same error when used with windows authentication (with my login and i'm SA on box and sql instance )
Does also sound like double hopping is a problem as well then.
Edit: Nasty Typos.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 31, 2016 at 10:42 am
Indeed. The double-hop issue means you can't use Windows authentication for your linked server unless you have Kerberos configured on the remote server. Have a read about it. I haven't provided any links because you'll be able to find them yourself easily if you do a search.
John
October 31, 2016 at 11:24 am
John Mitchell-245523 (10/31/2016)
Indeed. The double-hop issue means you can't use Windows authentication for your linked server unless you have Kerberos configured on the remote server. Have a read about it. I haven't provided any links because you'll be able to find them yourself easily if you do a search.John
Thanks John. I took a quick look at the double hop issue and it's solutions to configure Kerberos. Also , some blogs mention to use sql authentication and i did use the sql authentication to create linked server.
Can you confirm that my statement is true ? I have used SQL authentication to create linked server and it worked perfectly fine, i was able to access the data. Only when i set up a SQL agent job to run the linked server query I get this error and this is because it involves sql agent account which is windows authentication and eventually results in double hop issue ?
I tried to to use the SQL account (that i used in linked server creation ) in agent job but still it fails with same error.
Also, one thing to note: While creating the linked server i had to login as sql account and create the linked server, when i login with my id which is windows authentication it failed.
One more note, Server 1 agent account is using NT Services\SQLSERVERAGENT account and Server 2 is using domain service account. i think the problem is server 1 agent account doesnt have access to server 2 ?
October 31, 2016 at 1:40 pm
If you wish to use a SQL Login, and haven't set these up, you need to define these settings. You can find them in your Server Objects, Linked Servers, then right click the server and select properties. Is the account your Agent is runningon defined? What is the default option if the account attempting the connection is not defined?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 31, 2016 at 2:03 pm
Thom A (10/31/2016)
If you wish to use a SQL Login, and haven't set these up, you need to define these settings. You can find them in your Server Objects, Linked Servers, then right click the server and select properties. Is the account your Agent is runningon defined? What is the default option if the account attempting the connection is not defined?
Hey Thom, Sorry. I didn't get your point, what do you mean by "haven't set these up , you need to define these settings" ?
November 1, 2016 at 2:23 am
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 1, 2016 at 3:01 am
Thom's right. Go to the Security tab on your Linked Server properties and change the connection to Be made using this security context, and enter your SQL login name and password for the account on the remote server.
John
November 1, 2016 at 8:55 am
John Mitchell-245523 (11/1/2016)
Thom's right. Go to the Security tab on your Linked Server properties and change the connection to Be made using this security context, and enter your SQL login name and password for the account on the remote server.John
Thom and John, thanks a lot. That worked.
November 1, 2016 at 8:45 pm
Robin35 (11/1/2016)
John Mitchell-245523 (11/1/2016)
Thom's right. Go to the Security tab on your Linked Server properties and change the connection to Be made using this security context, and enter your SQL login name and password for the account on the remote server.John
Thom and John, thanks a lot. That worked.
Glad to hear you got it sorted.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply