Backup running under wrong account
-
Hi Everyone
I currently run a full backup every sunday with daily transaction log backups. The Sql Server Agent is running under a domain service account. All the files are being backed up to a shared drive on another server. My daily transaction logs are backed up as the service account. The weekly full backups however are run as NT AUTHORITY\SYSTEM for some reason I don't understand. This works great for creating the backups but when I go to restore the backups I get an OS error 2 file not found because NT AUTHORITY\SYSTEM does not have permission to read from that shared drive. I'm not even sure how they get written there to be honest.
A few weeks ago I redid my maintenance plan and it ran as the service account for at least the first week. It's now a couple months later and I just tried to do a restore and found it had reverted to the SYSTEM account.
Any help would be greatly appreciated. I can of course restore the backups by copying the files between the servers and restoring them one at a time but I would really like to just use the restore task in Management studio. Thank you.
Nathan
-
SQL Server backups will run in the context of the SQL Server service account, not the SQL Server Agent service account.
I don't think this is your problem. I think the problem is the system you are trying to restore to - which is probably running under the local system account.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I'm trying to restore to the same system I did the backup from. Both the Sql Server and the Agent run under the same account.
-
Can you show a few things:
1. The backup code (or is this only in maintenance plans).
2. What is the "run as" setting in the Agent job for the maintenance plan.
3. Can you double check the account being used for the SQL Server service? Are there instances involved?
4. Who's the owner of the files in the file system?
-
1. The backup code (or is this only in maintenance plans).
It is just a maintenance plan Full backup
2. What is the "run as" setting in the Agent job for the maintenance plan.
The owner of the job is the Domain Administrator. I can't find any "run as" settings
3. Can you double check the account being used for the SQL Server service? Are there instances involved?
There is only the default instance and I have confirmed that it is running under the service account
4. Who's the owner of the files in the file system?
The file is owned by the Domain Administrator
Thank you to the people helping me look into this. It has been very frustrating and I am out of ideas as to what could be causing this.
-
nathan 7372 (11/17/2010)
1. The backup code (or is this only in maintenance plans).It is just a maintenance plan Full backup
2. What is the "run as" setting in the Agent job for the maintenance plan.
The owner of the job is the Domain Administrator. I can't find any "run as" settings
3. Can you double check the account being used for the SQL Server service? Are there instances involved?
There is only the default instance and I have confirmed that it is running under the service account
4. Who's the owner of the files in the file system?
The file is owned by the Domain Administrator
Thank you to the people helping me look into this. It has been very frustrating and I am out of ideas as to what could be causing this.
Okay - why is the job owned by the domain administrator? That is begging for problems - and is a serious security risk. Or, did you mean the file is owned by the domain service account?
I personally recommend using 'sa' to own all jobs - since I have made sure nobody has access to that account it is safer than using a domain account. It also insures that the agent jobs run under the context of the service account - and not the user account for the job.
You definitely have something messed up in the configuration. Not sure where the problem is, but the error is stating that it is running under the local system account and that is why you cannot access the file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Ok, I changed the job ownership to sa. I had just left the ownership as the domain administrator because that was what my predecessor did and I didn't know there was a security risk involved. Out of curiosity why is it bad to have the job owner as the domain admin?
-
nathan 7372 (11/18/2010)
Ok, I changed the job ownership to sa. I had just left the ownership as the domain administrator because that was what my predecessor did and I didn't know there was a security risk involved. Out of curiosity why is it bad to have the job owner as the domain admin?You do realize that the domain administrator is the highest privileged account on your network - right? That means, it has full access to everything in that domain - with no restrictions.
That would give any agent job full rights to your domain and could easily execute any command using xp_cmdshell or operating system command and hack your network.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
So my weekly backup ran after I changed the job owner to "sa". It still ran under the NT Authority account though.
-
As has been stated multiple times - this means the service account running SQL Server is not set to a domain account. Not sure what else to tell you - but that is what that is telling you.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
The only ones that aren't the service accounts are the reporting services and the integration services. Does the agent somehow run through integration services?
-
If you are using a maintenance plan - which is an SSIS package, it is possible that it is running through integration services.
I think it depends on what version level you are at - I know that the RTM version and maybe SP1 required integration services installed to run maintenance plans. After that, it was no longer required.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply