SS Agent loses permissions on fileshare.

Question

SS Agent loses permissions on fileshare.

chrisrdba 471

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!

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-03-24T22:33:20.643+00:00

Not really, but as this is Windows problem more than an SQL problem, I thin you should review your tag list.
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2022-03-25T03:12:24.467+00:00

Hi @chrisrdba ,

Did you that you are using SQL server agent job to run a SSIS package? This SSIS package do some operation on shared folder? It works well for weeks, but suddenly it has some permission issue. Restart SQL server agent service can fix this issue? If I misunderstood, please let me know. Did you have any change for your SQL server agent or shared folder ?

In the Run as list, which account did you select SQL Server Agent Service Account or a proxy account that has the credentials that the job step will use?
chrisrdba 471 Reputation points

2022-03-25T17:59:29.73+00:00

Yes, I think we're on the same page. Perms are fine and no changes.
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2022-03-28T09:00:34.187+00:00

So i did not misunderstood your issue? Which account did you choose for this job?
chrisrdba 471 Reputation points

2022-03-28T14:04:40.947+00:00

The account used by the SS Agent.
Tom Phillips 17,771 Reputation points

2022-03-28T14:50:10.96+00:00

What exactly is the error message you are receiving? What is the result of SELECT @@VERSION of the SQL Server?
chrisrdba 471 Reputation points

2022-03-28T17:54:55.157+00:00

Will grab the exact message the next time it happens.

Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64) May 27 2021 17:34:14 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

1 answer

Your answer

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-03-24T22:33:20.643+00:00

Not really, but as this is Windows problem more than an SQL problem, I thin you should review your tag list.
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2022-03-25T03:12:24.467+00:00

Hi @chrisrdba ,

Did you that you are using SQL server agent job to run a SSIS package? This SSIS package do some operation on shared folder? It works well for weeks, but suddenly it has some permission issue. Restart SQL server agent service can fix this issue? If I misunderstood, please let me know. Did you have any change for your SQL server agent or shared folder ?

In the Run as list, which account did you select SQL Server Agent Service Account or a proxy account that has the credentials that the job step will use?
chrisrdba 471 Reputation points

2022-03-25T17:59:29.73+00:00

Yes, I think we're on the same page. Perms are fine and no changes.
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2022-03-28T09:00:34.187+00:00

So i did not misunderstood your issue? Which account did you choose for this job?
chrisrdba 471 Reputation points

2022-03-28T14:04:40.947+00:00

The account used by the SS Agent.
Tom Phillips 17,771 Reputation points

2022-03-28T14:50:10.96+00:00

What exactly is the error message you are receiving? What is the result of SELECT @@VERSION of the SQL Server?
chrisrdba 471 Reputation points

2022-03-28T17:54:55.157+00:00

Will grab the exact message the next time it happens.

Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64) May 27 2021 17:34:14 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

Answer 1

MotoX80 36,401

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,

On the SQL server, run process explorer.

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

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.

chrisrdba 471 Reputation points

2022-03-28T18:00:17.163+00:00

@MotoX80 you're thinking a memory leak is causing my perms issues?

I'll try this the next time it occurs.
MotoX80 36,401 Reputation points

2022-03-28T18:22:17.823+00:00

If you don't need to make any changes to the file permissions, and restarting the agent solves the problem, then that points to some kind of problem within the memory space of the running agent process.

That's not necessarily a classic software bug, it could be something that your SSIS packages are doing, like not closing file handles or something like that. Without more details it's hard to say what your system is doing.
chrisrdba 471 Reputation points

2022-03-28T19:49:09.717+00:00

Thanks -- will post more the next time it happens.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-03-28T21:00:32.417+00:00

Just in addition to what MotoX80 is saying: "Access is denied" can mean two things: 1) No permissions. 2) File locked by other process. Since a restart of SQL Server Agent fixes the issue, there is all reason to assume that the other process is a child of Agent.

Share via

SS Agent loses permissions on fileshare.

1 answer

Your answer