October 30, 2023 at 12:38 pm
Hi All,
SSIS error:
Recently the following error "Access to the path '\\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied.". i occurring.
The SSIS package is executed through SQL Agent using a Domain service account and running as a job for years
No changes to SQL Server no changes to the fileserver permissions etc.
1st thing checked is the path - and the availability - the path is open to everyone not just the DW admin service account.
No changes have been made to the path according to IT team.
Stuck
October 30, 2023 at 5:45 pm
check the time on both systems and the logon rights of the service account. It naturally needs at minimum logon as a service, it may also need logon as a batch. If permissions are not configured by group policy, the permissions can be changed by a wide array of processes.
October 31, 2023 at 5:48 pm
Another thing to check is the logs. Look at the security logs on the server XXX. Chances are something will be in the log like "access denied".
Also, with it working and suddenly breaking, something changed somewhere. If I had to make a very random guess, I'd say the service account password was updated. If the SQL Agent Service password is updated while the agent is running, the agent will continue to use the old password until the service is restarted. Jobs and such will start without issue, but anything that it needs to do off the server that hosts the SQL Agent Service (such as server XXX) will fail authentication.
Alternately, did anybody mess around with SPN's to try to get Kerberos authentication in place or set up a GPO for Kerberos authentication only (ie no NTLM) without setting up the SPN's as that'll break stuff too.
Short version - something changed that nobody expected to impact your SQL jobs but it did. So I'd start by trying to figure out what changed around when things broke, even if you are CERTAIN it isn't related to SQL, then you can try to determine what caused the problem.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 1, 2023 at 10:37 am
check the time on both systems and the logon rights of the service account. It naturally needs at minimum logon as a service, it may also need logon as a batch. If permissions are not configured by group policy, the permissions can be changed by a wide array of processes.
thank you
So far we have checked the time on both servers and they match for time and date. They do not use a time code server here.
Grant permissions are all looking unchanged
November 1, 2023 at 11:06 am
Another thing to check is the logs. Look at the security logs on the server XXX. Chances are something will be in the log like "access denied".
Also, with it working and suddenly breaking, something changed somewhere. If I had to make a very random guess, I'd say the service account password was updated. If the SQL Agent Service password is updated while the agent is running, the agent will continue to use the old password until the service is restarted. Jobs and such will start without issue, but anything that it needs to do off the server that hosts the SQL Agent Service (such as server XXX) will fail authentication.
Alternately, did anybody mess around with SPN's to try to get Kerberos authentication in place or set up a GPO for Kerberos authentication only (ie no NTLM) without setting up the SPN's as that'll break stuff too.
Short version - something changed that nobody expected to impact your SQL jobs but it did. So I'd start by trying to figure out what changed around when things broke, even if you are CERTAIN it isn't related to SQL, then you can try to determine what caused the problem.
Some further testing - so logged in with the associated sql server service account onto a server and navigated to the share path on file server.
We were able to open all folders at this patch and the xls file at the end of the path.
So we prove that the service account is original password and that access is available
I havent looked at your other comments yet
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply