Cannot run a job to update table on a linked server

Question

I am running SQL Server 2012 on a server (server 1) and have set up a job to run a stored procedure which will summarize data from a series of tables and update a table on a linked server (server 2) which is also SQL Server 2012. I can successfully run the stored procedure on it's own, but when the scheduled job runs, I get the following error:

Executed as user: NT AUTHORITY\NETWORK SERVICE. The OLE DB provider "SQLNCLI11" for linked server "222.222.222.222" reported an error. Authentication failed.
[SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "222.222.222.222".
[SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI11" for linked server "222.222.222.222" returned message "Invalid authorization specification".
[SQLSTATE 01000] (Error 7412). The step failed.,00:00:00,16,7412,,,,0

I suspect this is a security issue, but cannot find the settings that will let me run this.

• Hannah Vernon: Check which account the SQL Server Agent Service is running under (using SQL Server Configuration Manager on the server).
• Aaron Bertrand: And check how security is set up under Server Objects > Linked Servers > right-click 222.222.222.222

Answer 1

The SQL Server 2012 builds that were released after SQL Server 2012 was released. You must apply a SQL Server 2012 hotfix to an installation of SQL Server 2012. The fix for this issue was released in Cumulative Update 1 for SQL Server 2012. For more detail please read: https://support.microsoft.com/en-in/help/2652582/fix-invalid-authorization-specification-error-message-when-you-run-the

Answer 2

Include the local login "NT AUTHORITY\NETWORK SERVICE" enter image description here

• Hannah Vernon: Doing that will allow any remote service running as the Network Service account on the same domain to run anything including a DROP DATABASE command against the linked server. I would highly recommend not adding the NT AUTHORITY\Network Service to your SQL Server like this.