Error using Linked Server from SQL Agent Job but works from my login

• SQLFan-933088

  Mr or Mrs. 500

  Points: 549

  More actions

  March 16, 2018 at 11:53 am

  #344436

  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:\dataUpload\upload_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 SERVICE\SQLSERVERAGENT. 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 SERVICE\SQLSERVERAGENT. 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 SERVICE\SQLSERVERAGENT?? All I could find when adding the folder permissions was SQLSERVERAGENT)
  • I've tried to go to Windows\ServiceProfiles\AppData\Local\Temp 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 SERVICE\SQLSERVERAGENT 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. 

  Can anyone help?? I've searched the forum and tried what I could find, but some solutions here aren't detailed enough for me, especially around the Linked Server security settings. I need baby steps for this, if that's what is causing this problem. i.e., what to select under the 'local' list, whether to check 'impersonate,' what to use in the remote login boxes... 

  I would greatly appreciate any help someone can offer! This seems like it should be so straight-forward but it's not at all. I am meeting with the client next week for project "wrap up" but I can't seem to get over this hurdle and really need to. 

  Thanks!

• SQLFan-933088

  Mr or Mrs. 500

  Points: 549

  More actions

  March 16, 2018 at 1:06 pm

  #1983636

  I stumbled on the fix and want to share it here for others who come along with the same problem. 

  Apparently, because my linked server is to an Excel file via the OLE DB provider "Microsoft.ACE.OLEDB.12.0," the remote login setup in the Linked Server's security window has to be "admin" with no password. 

  Here are the exact things I changed and got my stored proc to actually load the Excel data to a SQL table from a SQL Agent job...

  1. The SQL Agent job should be owned by a non-sysadmin account. Seems counter-intuitive, but it seems doing this causes SQL Agent to actually use the local/remote user mappings defined in the Linked Server config (details below). In may case, I used a SQL authentication account that is not a member of the SQL sysadmin role. 
  2. In the Linked Server configuration, under the Security tab, I have selected 'Be made using the login's current security context' selected.
  3. In the Linked Server configuration, under the Security tab, under the heading "Local server login to remote server login mappings:", I clicked Add. For the Local Login value, I entered the name of the SQL authentication account that owns the SQL agent job. The impersonate box is not checked. For remote user, I entered "admin". For remote password, leave it blank. Click OK to save the changes. 
     

  This worked for me. I hope it helps someone else!

• Cyclura

  Newbie

  Points: 7

  More actions

  March 12, 2019 at 8:14 am

  #2024223

  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' ;
....


• jinal

  Grasshopper

  Points: 23

  More actions

  June 26, 2019 at 4:55 pm

  #3657808

  Hi ,

  I am facing similar kind of issue.

  I wanted to schedule a job which basically fetches data from JSON API and dumps into the table.

  T-SQL store procedure works perfectly. However, when I try to execute a store procedure as a job, the job runs but It does not populate data. I am using sp_oaMethod, so_oaCreate and sp_oaDestroy system store procedure to achieve that.

  Now, I tried everything like checking permission of SQL SERVER agent account, giving permissions to system store procedure (mentioned as above). The thing is job is not even failing, It runs without giving any results.

  All other jobs uses same SQL server agent account and runs fine. (These jobs does not involve accessing JSON API though).

  I also discussed same issue in MSDN SQL Server Support Site. But, I did not get any solution.

  Please give your suggestions.

   

• Jeffrey Williams

  SSC Guru

  Points: 90313

  More actions

  June 26, 2019 at 6:22 pm

  #3657845

  Create a proxy account that has the necessary rights/permissions to perform this function.  Permissions need to be defined in SQL Server and the file system - and a proxy account will need to be a windows account.

  Once you have the proxy account setup and added to the appropriate agent subsystem - then set that job step to run as the proxy account.

  For the original post - this would have solved the problem without having to change the ownership of the job.  The proxy account would be utilized for that job step.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• jinal

  Grasshopper

  Points: 23

  More actions

  June 26, 2019 at 8:05 pm

  #3657901

  Hi Jeffery,

  Thanks for your reply.  (Just so you know, I already have sysadmin privileges for my account)

  I am using simple T-SQL statement Exec Sp in my job step.

  I do not see T-sql option under the subsystem while creating proxy.

  What should I select?

  • This reply was modified 5 years, 6 months ago by  jinal.

• Jeffrey Williams

  SSC Guru

  Points: 90313

  More actions

  June 27, 2019 at 6:21 pm

  #3658229

  You are correct - the proxy account would not work for T-SQL agent jobs.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• jinal

  Grasshopper

  Points: 23

  More actions

  July 2, 2019 at 1:06 pm

  #3659269

  Hi,

  Then, how to resolve this problem?

  Thanks,

  Jinal.