July 31, 2012 at 3:22 pm
I've created a back up plan on one DB using the Maintenance Plans Wizard and the regular way (New Maintenance Plans). Everything looks to be setup correctly but when I try to execute the plan to see if it'll run properly, the plan fails to execute giving me an "Execution failed" message. Looking at the SQL Server Agent job history it seems like my SQL Server can't determine if my Windows NT group/User ID has the appropriate server access. I'm able to do basically everything I want to inside the server but execute the maintenance plan. Does any body know how to change my server access so I can get my maintenance plans running?
July 31, 2012 at 3:55 pm
What is the error message?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2012 at 4:00 pm
"- Execute maintenance plan. Backup Weekly (Error)
Messages
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details."
There was no log file in the maintenance plan log so I checked SQL Server Agent job history and found the message stating the server access issue.
July 31, 2012 at 4:02 pm
mrtrich99 (7/31/2012)
"- Execute maintenance plan. Backup Weekly (Error)Messages
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details."
There was no log file in the maintenance plan log so I checked SQL Server Agent job history and found the message stating the server access issue.
Please post that error (from the agent history).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2012 at 4:07 pm
SQLRNNR (7/31/2012)
mrtrich99 (7/31/2012)
"- Execute maintenance plan. Backup Weekly (Error)Messages
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details."
There was no log file in the maintenance plan log so I checked SQL Server Agent job history and found the message stating the server access issue.
Please post that error (from the agent history).
07/31/2012 14:06:30,Backup Weekly.Subplan_1,Error,0,SERVER1,Backup Weekly.Subplan_1,(Job outcome),,The job failed. Unable to determine if the owner (XX\mrtrich99) of job Backup Weekly.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'XX\mrtrich99'<c/> error code 0x5. [SQLSTATE 42000] (Error 15404)).,00:00:00,0,0,,,,0
July 31, 2012 at 4:21 pm
I presume you are able to create, delete, alter files in the location referenced in the maintenance plan?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2012 at 4:26 pm
SQLRNNR (7/31/2012)
I presume you are able to create, delete, alter files in the location referenced in the maintenance plan?
yes, i was the one that created the maintenance plan. i can delete and alter that plan as well. if that is what you meant by "files in the location referenced in the maintenance plan"
July 31, 2012 at 4:30 pm
Nope. I mean the location on disk where the maint plan is trying to access to write backups, logs, or delete files. Have you tried running the steps of the maint plan outside of an agent job?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2012 at 4:37 pm
SQLRNNR (7/31/2012)
Nope. I mean the location on disk where the maint plan is trying to access to write backups, logs, or delete files. Have you tried running the steps of the maint plan outside of an agent job?
Yup I have admin rights on the server and all drives and folders. I copied the sql script from the plan and executed on management studio and it worked fine
August 1, 2012 at 2:34 pm
mrtrich99 (7/31/2012)
SQLRNNR (7/31/2012)
Nope. I mean the location on disk where the maint plan is trying to access to write backups, logs, or delete files. Have you tried running the steps of the maint plan outside of an agent job?Yup I have admin rights on the server and all drives and folders. I copied the sql script from the plan and executed on management studio and it worked fine
Is this 'XX\mrtrich99' your user or a service account? Does the displayed domain exist? Is that the domain that the computer is on? Double-check to make sure that the computer has not switched to WORKGROUP.
Jared
CE - Microsoft
August 1, 2012 at 2:49 pm
Go to the properties of the job in the Job Activity Monitor and check to see who the owner is set to on the General tab.
If it is your user ID ( Which I suspect it is) change it to a SQL service account or to an account with SA equivalent permissions. Then try to run the job again. I have seen the same issue after I altered a maintenance plan and this has always worked for me.
Hope it helps.
- James
August 1, 2012 at 3:00 pm
Is this 'XX\mrtrich99' your user or a service account? Does the displayed domain exist? Is that the domain that the computer is on? Double-check to make sure that the computer has not switched to WORKGROUP.
Actually I was going to add to my previous post that the computer domain is different than my network domain. So my network domain is "XX" while the computer domain is "Domain.YY". I'm not sure why everything in SQL server works fine for me except for the Maintenance Plan.
August 1, 2012 at 3:12 pm
james.thomas (8/1/2012)
Go to the properties of the job in the Job Activity Monitor and check to see who the owner is set to on the General tab.If it is your user ID ( Which I suspect it is) change it to a SQL service account or to an account with SA equivalent permissions. Then try to run the job again. I have seen the same issue after I altered a maintenance plan and this has always worked for me.
Hope it helps.
- James
this solution seems to be working. changed the properties to have an SA permission
August 1, 2012 at 7:18 pm
mrtrich99 (8/1/2012)
james.thomas (8/1/2012)
Go to the properties of the job in the Job Activity Monitor and check to see who the owner is set to on the General tab.If it is your user ID ( Which I suspect it is) change it to a SQL service account or to an account with SA equivalent permissions. Then try to run the job again. I have seen the same issue after I altered a maintenance plan and this has always worked for me.
Hope it helps.
- James
this solution seems to be working. changed the properties to have an SA permission
Of course it does, you changed it to a login whose permissions worked. You need to go back and look at how the server is on a different domain than your own, that can cause issues if not configured correctly.
Jared
CE - Microsoft
August 1, 2012 at 8:16 pm
Please take this as advice rather than criticism.
I'm going to chime in here with something that is, on a technicality, off topic. Understanding Active Directory permissions is an important part of doing SQL development or DBA work in any environment. You need to have a handle on this before you start trying to do admin work. Even if AD permissions are not your direct responsibility, you should understand the ramifications of AD to the work you are doing. You're probably going to run up against this again at some point.
I say this as someone who got his accidental-DBA feet wet nearly 10 years ago. I started out as a run-of-the-mill sysadmin so I understood GPO and AD pretty well. My SQL Server deployments were superior to that of my predecessor because I understood the ramifications of what I was doing. You should too.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply