May 1, 2015 at 7:23 am
Hello,
I have a SQL Server 2012 SSIS package that runs fine from within SSIS and from the Project Catalog in Integration Services, but when I try to run it from a job, it failes. If I set the job owner as my Windows Login I get error "The job failed. Unable to determine if the owner (chahq\kferrara) of job Horizon Care Manage to BIX Import has server access (reason: Could not obtain information about Windows NT group/user 'chahq\kferrara', error code 0x5. [SQLSTATE 42000] (Error 15404))." If I set the job owner as NT AUTHORITY\SYSTEM I get this error: "Horizon_CareManage:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "The Microsoft Access database engine cannot open or write to the file '\\BIXSQLDEVW01\d$\Horizon\Care Management Report\223537011_PCMH_033115_CareManagement12mo.xls'. It is already opened exclusively by another user, or you need permission to view and write its data."."
The package is reading data from an Excel file on a different server and importing it to the Database.
Any Ideas? My SQL Server Agent is started under the Network Service account and SQL Server is started under the Local System account.
Also if I run from the file system, I get the same results.
Thank you.
Karen
May 1, 2015 at 9:52 am
Looks like security issues. Note that the SQL Agent has to have rights here. The Network Service account doesn't necessarily have rights to get to the files you're working with.
Can you get rights added to the service account and test that? Also, the job owner looks like it might be a problem. I usually try to make sure that I have job owners that are system users, like sa.
May 4, 2015 at 8:07 am
Thank you for your help. I entered a ticket with our help desk to have a global service account created. Once done, I will set both SQl Server and SQL Server Agent to run under this account and restart the services. I will set this account as the job owner, but I only have one option to run the SSIS package and that is SQL Server Agent. I will grant read/write access to the file folder to the new account.
Will this then work? I should be able to successfully run the job?
Thank you!
Karen
May 18, 2015 at 2:20 pm
I had a new domain account created and started the SQL Server and SQL Server Agent services with this account. I made this account sys admin in my SQL Server and set it as the job owner. Now my package doesn't run from the SSIS catalog -- I get an error that my account (account used in package) does not have access to the database which is untrue as my account is also sys admin.
I still cannot run the package from the job.
May 19, 2015 at 9:23 am
😀
I resolved the problem finally:
1) There was a security issue, but also two other issues. There was a syntax error in one of my environment variables in the SSIS catalog that was causing my account to be denied access to one of the database.
2) I had a domain account created and started SQL Server and SQL Server Agent services with this account. I used that account as the job owner. The job still failed. Since my excel file was on another server, instead of using the \\servername\D$\..... I created a share directly to the folder where my files reside and used the sare name. That fixed the problem accessing the Excel file.
Both issues were fixed and the job is now running successfully.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply