Share via

SS Agent loses permissions on fileshare.

chrisrdba 361 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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,363 questions
Windows Server
Windows Server
A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.
12,635 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MotoX80 32,911 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.