SS Agent loses permissions on fileshare.

chrisrdba 471 Reputation points
2022-03-24T20:32:23.557+00:00

Greetings. I've got a few SSIS Packages that are run via job and do things with a file that exists on a fileshare. They can run for weeks without issue, then they'll suddenly blow up claiming they dont have perms and access is denied.

I can workaround this by simply restarting the SS Agent, and have done this workaround several times over the last few months.

Does anyone know of a real fix for this? Thanks!

Windows for business | Windows Server | User experience | Other
SQL Server | Other
{count} votes

1 answer

Sort by: Most helpful
  1. MotoX80 36,401 Reputation points
    2022-03-28T14:40:36.547+00:00

    Sounds like some kind of memory leak or orphan processes that are not terminating.

    On the file server, run computer management and check for sessions and open files from the server that hosts SQL,

    187621-capture.png

    On the SQL server, run process explorer.

    https://learn.microsoft.com/en-us/sysinternals/downloads/process-explorer

    187562-capture1.png

    In the View menu set the lower pane view to show handles and the update speed to 10 seconds. Find the SS Agent process. Processes will be indented for parent/child. Does the SS Agent have multiple sub processes running? Search the lower pane and look for file handles referencing the file server.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.