November 9, 2006 at 9:37 am
I have come accross a rather funny state with one of the sql servers i manage.
The Sql server is sql server 2000(sp4), residing on a windows server 2003 standard edition service pack1.
I ran a backup of all the databases on this server with no problems at all. But when i use the database maintainance plan to schedule the backups, it fails.
When i check the job from sql server agent it doesnt change the job icon to red and there is no history for the job.
Secondly when i check the history of the job from the databse maintainance window, it seems to have disappeared. On checking the sql server error logs or agent logs it doesnt report there has been any errors on the server.
The only place where i can get information on whats going on is the windows error logs.
Below is a copy of the log
"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan5'' (0x04466652C114104684A9D751B9FE264B) - Status: Failed - Invoked on: 2006-11-09 15:35:35 - Message: The job failed. Unable to determine if the owner (3P) of job DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan5' has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs).
After reading this error i try to run the job again using all sorts of logins including the SA one. Yet it comes up with the same error.
Finally, the most anoying part is i try to run the syslogins and sysusers stored procedures from QA and it comes up saying these users are not in the databse, yet i can see them within enterprise manager, login with them and even run jobs manually with them.
Someone please please help
vdavid
November 10, 2006 at 2:25 am
I don't use maint plans for lots of reasons - however - some basic checks. Jobs run under agent service so the account the agent runs as should ideally be the same as the sql service runs under.
I usually set database and job owners to sa, makes life much easier. Try using tsql scripts to backup the databases - you might get better error messages. I suspect ownership issues.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 12, 2006 at 7:58 am
Please confirm that the SQL Server Agent service is running under the expected account and then confirm that the SQL Server Agent service account has the the expected SQL Server privileges, probably the system administrator role.
Regarding the system procedures, such as sp_helplogin, is there no output or do you get an error ?
If you get no output, I have also encountered this problem which was caused by Active Directory Group Security Policies not granting the SQL Server account sufficient priveleges. If group policies are not in effect, then the local security policy can also cause this behavior. Check the windows security event log to see if there any failure events recorded. After the security policy is changed, SQL Server and the Agent will need to be restarted.
SQL = Scarcely Qualifies as a Language
November 13, 2006 at 5:29 am
Thanks all,
I finally found a away around it. It was a permissions problem. The sql server services and sql agent was configured to use the local system as the service account. The Ntuser\systems was using the builtin\administrator user which was initially removed. I added this user and it solved the problem.
vdavid
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply