DB Maint Plan cannot generate backup
-
Sorry for cross-posting this (this was on the Adminstration forum), but that thread went nowhere and after a week I still can't get a grip on this. Here's what I've got:
- SQL Sever 2000 Developer Edition sp3 (+hot fix ".818") on a W2k3 server
- SQL Server and SQL Agent are both running using the same domain account with local admin rights on the box
- A database maintentance plan is configured to perform a complete backup of a single database (it's in simple recovery mode)
When the job is fired off (either by schedule or manually starting), SQL Agent comes back with the "generic" failed message: "The job failed. The Job was invoked by Schedule 7 (Schedule 1). The last step to run was step 1 (Step 1)." No help there.
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 run that, 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.
(This shows that it's not SQL Agent that's messing up.)
So where is "NT AUTHORITY\SYSTEM" coming in? Monitoring a DBMP-generated backup on another server with SQL Profiler shows all the commands being submitted via a login established for account the services are configured with, not NT AUTHORITY\SYSTEM.
The flow of control is: SQL Agent starts the job, it calls xp_sqlMaint, which shells out to xpStar.DLL, which "does stuff" and eventually establishes a new connection to SQL Server. On most boxes it "passes on" the same authentication token, but here it switches to NT AUTHORITY\SYSTEM. Why? (Oh, and SQL Profiler shows the failed login attempt was made by "SQLDMO_1".)
No, I don't have BUILTIN\Administrators configured. No, I'm not going to add it or NT AUTHORITY\SYSTEM just to get this working, I'll uninstall and reinstall first--but I'd rather know why this is happening.
Theres nothing in the registry for XPSTAR, the funky low-level access rights for the service account's login (guff like "Replace a process level token") are configured the same as for a working server, and I'm running out of straws to grasp at.
Philip
-
Philip,
Can you give some more details like:
- where do you store the backup file (local or remote)?
- Do you have a login for the SQL Service / Agent account ?
What's in the Maintenance Plan history? Usually that gives some other info than the job history.
SQLDMO is a COM interface designed to do do SQL actions without having to enter complete & difficult SQL statements. (scripting jobs is easy using SQLDMO). I guess XPSTAR just uses this to figure out what the db recovery model is and things like that.
JP
-
Forgot to mention that we have the same kind of setup here. No builtin\administrator login.
JP
-
Backups are stored locally, on the same physical device as the database files.
The same domain account is used for both SQL Server and SQL Agent. It has local admin rights on the box, and is configured in SQL Server as an NT login with SysAdmin rights.
There is absolutely nothing written in the database maintenance plan history tables, as I've only set up the one attempted plan so far. (This is probably due to xp_sqlmaint being unable to connect to the database server to write this information.)
Philip
-
Well you should have a BUILIN/Administrators account defined. It is the default setup. You can add it by hand using as a standart user, and you must set it to be in the sysadmin server role.
This NT group allows logins from users that are in the administrator group. If it is sysadmin, it gives equivalent privileges to SA to thoses users.
Running an extended stored proc requires this amount of privileges. The db maint extended stored proc probably tries to connect to system using the local system account, which is an admin account qualified by "NT AUTHORITY\SYSTEM". This is why the maintenance plan fails. As an administrator is doesn't have access to SQL because the admin. group is not allowed to SQL
-
The BUILTIN\Administrators logins is not required to operate SQL Server. It is the default, but, when securing an installation of SQL Server, it is an "industry best practice" to remove it--or, rather, replace it with something more robust. The following are a few of the articles on SQL Server Central that discuss this topic.
http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritysecurityadmins.asp
http://www.sqlservercentral.com/columnists/ckempster/sql_server_security.asp
http://www.sqlservercentral.com/columnists/bknight/10securingyoursqlserver.asp
Philip
-
Philip,
you're right. builtin\administrators is not required. But let's get back to the topic. You couold try to ste NTFS auditing on the folder in which you create the backups. May be this will give you a clue about what's going on on the disk. I suggest you als o set auditiing on in SQL Server (right click server in EM).
I assume you've checked all your SQL Log files?
JP
-
I do not know how to enable NTFS auditing on a folder--might you have any links to information on this subject? I suspect this won't be the problem, as I'm not getting any "disk access denied" messages, but rather "can't log in to SQL Server" ones. However, at this point I'll try most anything.
SQL Auditing can show succesful and/or failed logins. I have positively determined that a failed login event (the attempt by "SQLDMO_1" mentioned earlier) occurs when the job attempts to run.
The SQL Agent log file only shows that the job run failed, without any paritcular information as to why (the message is in my first post). The SQL Server log shows nothing regarding this activity.
Maybe I can blame Active Directory? I (a) have very little experience with AD, and (b) do not have access to it on this network.
Philip
-
Is the domain account for sql server and the agent service defined to SQL ? If they are, then are they assigned to the sysadmin role ? Also, when you log in ad execute the query are you logging into the same domain as well ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
If by "defined to SQL" you mean "granted login rights to the SQL Server instance", then yes, they are, and they have been added as memebers of the SysAdmin server role. And yes, when I run the query "by hand", I'm logged in to the domain (and via NT authentication to SQL Server) on an account in the same domain. (There's only the one domain here, no trusted domains.)
A few more facts of dubious relevance: there's some question as to whether SQL Server 2000 service pack 3 or 3a was applied before the ".818" bug fix, and the demo(?) version of reporting services was installed on the box as well.
Philip
-
Have I mentioned recently that I hate databases in general, and SQL Server in particular?
Now it works.
I don't know why, and I probably never will know why.
All I do know is this: The machine had some kind of old demo version of reporting services installed on it. This recently version expired, and they proceeded to upgrade to the full version. Now, by "they", I mean this one guy who's got NT 4.0 Sysadmin experience, and who is now somewhere between a developer and, oh, call it advanced research/analysis dude in our business. He took it upon himself to do this installation process, which went something as follows: uninstall old reporting services, go through steps to install new reporting services on a different box, which process includes applying service pack 3 to the database server, so he started to install sp3, but it blew up because (a) he doesn't have SysAdmin rights on that SQL Server instance, and (b) service pack 3 had already been installed on that box long ago anyway. The first I knew of any of this was when he asked me what was wrong with the server. (Didn't tell me what he'd been doing, no, that obviously wasn't a factor.) So I find that SQL has mysteriously been set to single-user mode, and start fussing around trying to determine if our system is being hacked by an outsider. Eventually the fact that an aborted attempt to apply sp3 was gets mentioned, vindicating my justification in not giving him SysAdmin rights. A few mouseclicks later everything's back to normal, and he proceeds his on merry way. And now, three days later, playing a hunch, I find that the maintenance plan backup now works. (The key database seems to have grown to 4G from 10M in those three days, but that's par for the course.)
Feh.
Philip
-
I feel you pain Philip
... been there a couple of times before in my career 
... At times like that I try to remember that "Any day on this side of the dirt is a good one !"
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Was pouring through forums everywhere trying to fix a similar problem. The transaction log backup part of Maintenance Plan was failing with the same error you were getting. Followed all of the above suggestions and then some and found that for some reason 2 of my less-used databases had the recovery model set to 'simple' instead of 'full'. Switched them to 'full' and voila! Not sure if that would have been your problem, but I thought I'd share it anyway.
-
Philip,
I'm glad it's solved. I agree with Rudy, it's allways frustrating when someone messes up things and tries to keep it secret...
Like you, I never guessed it was in single user mode!
JP
-
A quick clarification: the backup problem had been going on for weeks, while the single user mode issue just cropped up yesterday (when they tried to apply the service pack). I just spelled that whole charade out, on the off-chance that something in there was what fixed the overall system.
Philip
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply