I have a SQL Server service account: domain\sqlservice.
This account is running both SQL Server service and SQL Server Agent Service.
This account has read/write permission to a file share: \\fileserver\Path1, and from the SQL Server and SQL Server Agent jobs, we are able to write to this folder path using the service account (using master..xp_cmdshell. xp_cmdshell is enabled).
This permission got removed by mistake, now we have added it back.
I can use the service account domain\sqlservice to browse to \\fileserver\Path1 and create files and folders (using Windows Explorer), but SQL Server and SQL Server Agent jobs still could not.
I have restarted the SQL Server Agent service (I believe this is account used to access the file share when running the SQL Server Agent jobs).
But it still failed to access the path - I get an access denied error.
I think I will need to restart the SQL Server service to fix this issue (waiting for my maintenance window now).
I want to understand it better, can someone give me some hint on why SQL Server Agent job is related to the SQL Server services running account? And what is the relationship between SQL Server Agent job service account and SQL Server service account? Are we still utilize this account to access fileshare defined in the SQL Server Agent job?
Thank you all very much
@Charlieface’s comment explains the reason:
Double check that SQL Server is definitely still running under that service account, as well as which account has now received permissions to the share. The permissions for xp_cmdshell depend on whether the session has sa rights, see learn.microsoft.com/en-us/sql/relational-databases/…. Quite why you are using xp_cmdshell is a different question: it has major security implications, and should probably be converted into a SQL Agent job written in Powershell.