xp_sqlmaint unable to log in

Question

Post reply

xp_sqlmaint unable to log in

Philip Kelley

SSCertifiable

Points: 6351
More actions
July 5, 2005 at 4:12 pm

#167441

This one's really hurting my brain. I've got:
- SQL Sever 2000 Deverloper Edition sp3 (+hot fix ".818") on a W2k3 server
- SQL Server and SQL Agent are both running on the same domain account with local admin rights on the box
- Database Maintentance plan is configured to perform a complete backup on a single user database (in simple recovery mode)
When fired off (either by schedule or manually starting the job), SQL Agent comes back with the "generic" failed message--no useful details in either the job history or Windows event log.
When I cut and paste the job command [EXECUTE master.dbo.xp_sqlmaint -PlanID N'<etc>...' ] to query analyzer (on my personal box, using NT Authentication, and I've got SysAdmin rights) and fire it manually, I get back:
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
[Microsoft SQL-DMO (ODBC SQLState: 28000)] Error 18456: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\SYSTEM'.
NULL
(6 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Showing that it's not SQL Agent that's messing up. So where is "NT AUTHORITY\SYSTEM" coming in? is xp_sqlmaint "shelling out" or something? Is the standard default "LocalServer" configuration in ODBC getting in the way? Are the Gremlins eating my diodes?
...Just tried it with SQL Profiler on. There's a failed login event showing up for ApplicationName = "SQLDMO_1", on the server, for loginName "NT Authority/system". No help there(?)
Philip

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

What authentication mode is the server set for (SQL, Windows, mixed)?

And then again, I might be wrong ...
David Webb

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 2

Philip Kelley

SSCertifiable

Points: 6351

July 5, 2005 at 4:46 pm

#571053

It's set to mixed.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

Does the failure happen before the plan executes (does the backup complete)? I have seen instances where it's the notification that has the id problem, not the plan itself, and the message doesn't reflect that. If that's not the problem, wiser heads than mine will have to take over...

And then again, I might be wrong ...
David Webb

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 4

As near as I can tell, the connection (SQL Agent or the query analyzer window) calls xp_sqlmaint, it does it's internal DLL thing, then tries and fails to connect to SQL Server, and passes the error back to the caller. No backup is produced, or even started. This behaviour is confirmed by the Profiler trace (shows the failed login), the SQL Agent history log (showing about 1 second execution time), and the maintenance plan history logging (no records get written to those tables).

Philip

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 5

This issue got resolved, sorta. Details can be found in this thread (I hope the link works):

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=201317&p=2

Philip

Dave Morse SSC Rookie Points: 44 More actions · Answer 6

Well, I found myself having the exact problem you initially described in this thread. Due to your excellent troubleshooting, I was able to look for other things that may cause the problem. Since the resolution to your problem was the failed SP3 install and server reboot. I decided that wasn't how I wanted to try to fix the problem. 😀

What I found:

The -BkUpOnlyIfClean option was causing the failure. I found this because I had two routines backing up my server, one for the System dbs, and one for the production dbs. The system backup was working, the production was not. Upon comparison, the only difference was the -BkUpOnlyIfClean command. Running the commands from QA, I removed BkUpOnlyIfClean, and the backup ran fine.

The non working command line in my maintenance plan:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 1C455BB5-418D-4D52-9D49-308A1AA89264 -WriteHistory -VrfyBackup -CkDB -BkUpOnlyIfClean -BkUpMedia DISK -BkUpDB "F:\MSSQL\BACKUP" -DelBkUps 3DAYS -BkExt "BAK"'

The working command:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID F21F2908-355B-499D-B056-BFE2B7FD8FCA -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "F:\MSSQL\Backup" -DelBkUps 2WEEKS -BkExt "BAK"'

I hope this helps anyone trying to fix this problem in the future. Although it does seem I am giving up checking to make sure the dbs are clean before backing them up.

Dave Morse

IT Tech Team

Public Health Department

Santa Barbara County