March 1, 2016 at 3:19 am
Hello
I have 2 server, a SQL ETL build server and a reporting services server
All the ETL work is performed on the ETL server
Data is then moved to the reporting services server
It's moved using backup and restore
I have an issue with backing up
Basically, I have a job with a step to backup the data to a specific network drive
This has worked perfectly for months
Over the weekend, I had to restart the server (ETL one)
Now, the backups fail with, what looks like a permission issue
Access to path ... is denied
As far as I am aware, the step runs as 'SQL Server Agent user Service Account' (I can confirm by looking at the job step)
Nothing has changed with security on the folder that is required
How do I actually check permissions though
My SQL Agent account comes up as NT SERVICE\SQLAgent$MI_SQL_SERVER
I don't actually see this account
Or maybe it's never been there??
I guess the questions are:
1. What could have caused this to stop working?
2. How does SQL Agent access to folder security work (I didn't originally set this up)
Thanks
Damian.
- Damian
March 1, 2016 at 3:56 am
has someone changed the run as on the job step maybe?
running as the virtual NT SERVICE account is not recommended for services that require network access, what has changed since it last ran successfully, check share and NTFS permissions on the share itself
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 1, 2016 at 4:02 am
and is possible open this network device with explorer ? like folder?
for example I have test server which is not in domain , but sometimes I need to get something from domain, so I logon to network disk with my domain account and its working.. .after restart I have to do same , login again with my domain account to network disk...
March 1, 2016 at 4:18 am
I've since tried to change the job owner (me, sa, NT Service/SQLAgent$MI_SQL_SERVER, back to what it was)
No success
Everything else about the job works fine
Run as, for the step comes up 'SQL Server Agent Service Account' and I cannot change this
No issues with the network drive, I can access this
I have a backup job that runs as 'sa' on my reporting services server
This accesses the same folder on the same network servr with no issues
If the job runs as 'sa', how does this work as my sa account does not have access to this folder
I suspect I have missing something obvious in my logic here but can't quite see how this works
- Damian
March 1, 2016 at 4:28 am
DamianC (3/1/2016)
No issues with the network drive, I can access this
It's not what you can access that's important - it's what SQL Server can access. If I remember correctly, jobs owned by sysadmin logins will run under the context of the SQL Server Agent service account. If the owner isn't sysadmin, you can set up a proxy account for it to run under. However you choose to do it, the account in question needs to have the necessary access on the network location.
John
March 1, 2016 at 4:40 am
I just can't see what has changed here
Now concerned that the same will happen if I have to restart the report server
If it's running as the service account on both servers, how is it accessing the server/folder?
Or do service accounts have aliases or parent accounts or something else that defines the server/folder that they can access
- Damian
March 1, 2016 at 4:50 am
The first thing I would try is to change SQL Server Agent to run under a domain account instead of a built-in account. I appreciate that it's frustrating that it's stopped working for no apparent reason, but I think changing the account may well fix your problem.
John
March 1, 2016 at 7:16 am
ok, so I've tried the following and this still does not work
Created a maintenance plan that performs a backup to the network folder
Created me as a proxy user
So, added me as 'Test User' via credentials
Now added the Test User as a SSIS Package Execution proxy account
Gone into the new job that the maintenance plan has created
Made 'sa' the owner of the job (it was me)
Amended the backup step
Changed Run As from SQL Server Agent Service Account to the Test User previously created
Surely this should run the job as me and use my credentials to access the specific folder (I have full permissions against it)
- Damian
March 1, 2016 at 9:13 am
can you post the history from the job?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 1, 2016 at 5:12 pm
Is your service account locked?
net user TheAccount /DOMAIN
March 2, 2016 at 2:48 am
Hmm...
So this morning (UK) I come in and see that the job worked!
Also, I can now perform manual backup steps to the network drive with no issues
All things considered, I don't think I've actually done anything
Positive that it's working
Negative that I have absolutely no idea what caused it
It seemed to have stopped after the service restart at the weekend and restarted at some point through the night
Thanks for the advice
I did find out a little more about how the sql agent works along with proxy accounts etc.
- Damian
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply