Sql error 7412

I am using SQL Server 2012. I have scheduled a job to run every 1 hour, with 1 step calling 1 simple Stored Procedure. The Stored Procedure migrates around 1K data from production server to our local server. Linked server has been established successfully. The query used in Stored procedure looks like below.

SELECT ID,Col1,Col2,Col3,Col4,Col5 into #temp from [111.111.111.111].ProdServer.dbo.SourceTable

Insert into Localtable(ID,Col1,Col2,Col3,Col4,Col5)
SELECT ID,Col1,Col2,Col3,Col4,Col5
FROM #temp where ID NOT IN (select ID from Localtable)

The job execution sometimes takes 25-30 secs which will successfully executed. Sometimes it takes 48-50 seconds which results in failure. Please refer failure message below and other details in above screenshot.

Failure Message:

Executed as user: NT AUTHORITYNETWORK SERVICE. Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQLSTATE 42000] (Error 53) OLE DB provider «SQLNCLI10» for linked server «111.111.111.111» returned message «Login timeout expired». [SQLSTATE 01000] (Error 7412) OLE DB provider «SQLNCLI10» for linked server «111.111.111.111» returned message «A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.». [SQLSTATE 01000] (Error 7412). The step failed.

More Details

• The table is locked by nobody and no queries run on it.
• In Linked Server Properties, both Query Timeout and Connection Timeout
  has been set to 0.
• In Server properties remote query Timeout is also set to 600 Secs.

As last option I tried to change Connection Timeout Settings from its default value 15 to 6000 or higher. I could not achieve it. Please suggest solution to execute my scheduled jobs successfully even it takes 48 minutes or slight more.

Right Click on server > Click on properties > Click view connection properties on left pane under connection — to see Connection Timeout Property

1 new observation:
The job is failing if it takes 48 seconds or more to run.

troubleshoot sql server mail problem

Hi,
I’ve been pulling my hair out for 2 days on this… I hope someone can help as I have a hard deadline and cannot get past this maddening linked server problem.

The goal is to run an Agent job that will call a stored procedure. The stored proc uses a Linked server in order to load data from Excel documents stored on the local (to SQL server) file system. It works when I run it manually myself but fails from the SQL Agent job… 

Version:  SQL Server 2012, SP 3, x64
Linked Server definition: 
EXEC master.dbo.sp_addlinkedserver @server = N'UPLOAD_G', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'D:dataUploadupload_G.xlsx', @provstr=N'Excel 12.0'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'UPLOAD_G',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

• When I run a query that uses the linked server from SSMS, logged in with my domain acct, it works. 
• When I run the Stored Proc (SP) that utilizes the linked server from SSMS, it works.

SQL Agent job error: 
Executed as user: NT SERVICESQLSERVERAGENT. Cannot initialize the data source object of OLE DB provider «Microsoft.ACE.OLEDB.12.0» for linked server «UPLOAD_G». [SQLSTATE 42000] (Error 7303) OLE DB provider «Microsoft.ACE.OLEDB.12.0» for linked server «UPLOAD_G» returned message «Unspecified error». [SQLSTATE 01000] (Error 7412). The step failed.

I’m thinking it’s a permission issue, and I’ve searched and Googled for 2 days but I just cannot figure out how to fix it. I’m not sure if the problem is at the file system level or the Linked Server security settings. 

• The SQL Server Agent service logs on as NT SERVICESQLSERVERAGENT. Note that I am not able to change it to Local System. I am a contractor and other people have jobs setup on this server that I am not involved in. 
• The job is owned by a windows authentication account that is in the SQL sysadmin role and a member of the domain Administrators group.
• In the file system, I’ve added the SQLSERVERAGENT account to have Full Control permissions on the folder D:dataUpload. (—> Is this the same account as NT SERVICESQLSERVERAGENT?? All I could find when adding the folder permissions was SQLSERVERAGENT)
• I’ve tried to go to WindowsServiceProfilesAppDataLocalTemp and add permissions for SQLSERVERAGENT but the user doesn’t even come up in the list. And I’ve tried changing object types and Location to no avail. Based on my research and googling, I thought since the server is x64, this setting wouldn’t be necessary. I was going to try it anyway out of desperation. 
• The Security settings in the Linked Server tab are confusing to me, to be honest. I have it set to current login context. I have tried to map local/remote accounts but this is the part that’s murky to me and I deleted the entries I attempted. I tried adding NT SERVICESQLSERVERAGENT as the local account and checked the Impersonate box. Not sure this is correct and I haven’t found much documentation on this particular setting. 
• RPC and RPC Out on the Linked Server are TRUE
• The provider’s settings have Allow inprocess and Dynamic parameters enabled.
• No matter which account I set as the job owner, according to Profiler the login account is always SQLSERVERAGENT. 
• I’ve tried to change the job step’s ‘Run as User…’ setting but when I browse the available list, not all accounts are shown. Even though my account is SQL sysadmin and a in the domain Administrators group, it’s not listed here. 
• I ran Profiler with the OLEDB category of events selected. I have these results if they will be useful. I won’t list them here initially in case it clutters this post too much. 

Thanks for this solution!
This solved my problem after searching for a long while.

Some small addition in case you want to run a report in SSRS wich runs a Stored Procedure which executes a SQL Job:
in the [System Databases > msdb > Security > Users] give the user mentioned the TargetServersRole and under Securables, add the ‘sp_start_job’ and ‘sp_stop_job’ and grant them Execute permissions.
This way your  user is allowed to run a Stored Procedure.

Now you can run the Stored Procedure from within SSRS that is linked to a Linked Server (i.e. Excel File).

CREATE Procedure [dbo].[SP] (...)
AS
EXEC msdb.dbo.sp_start_job N'ExactJobName' ;
....