SQL Server Performance

when a maintenance plan fails...

Discussion in 'Contribute Your Performance and Clustering Tips' started by chopeen, Feb 12, 2005.

  1. chopeen Member

    I've used maintenace plans many times and I've never had any problems with them.

    Until yesterday.

    I created a simple maintenance plan - just full database backups of a database using a Simple Recovery model. Every time the backup job started it failed with the following error message:
    Executed as user: NT AUTHORITYSYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

    I tried to run 'EXECUTE master.dbo.xp_sqlmaint ...' using Query Analyzer and I kept getting:
    NULL
    Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
    Copyright (C) Microsoft Corporation, 1995 - 1998
    NULL
    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4064: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.
    NULL

    (6 row(s) affected)

    Server: Msg 22029, Level 16, State 1, Line 0
    sqlmaint.exe failed.

    Finally, I solved this problem by granting access to 'NT AUTHORITYSYSTEM'.

    PS1. I know that this is neither performance nor clustering tip, but after a few hours of struggling with this problem I've just felt like sharing this knowledge with others.

    PS2. Now I am wondering about the possible side effects of granting access to 'NT AUTHORITYSYSTEM'.

    --

    Rediscover the web
    http://www.mozilla.org/firefox/
  2. thomas New Member

    Re: "PS2".... well it means sql server agent is running under the local system account and not under a domain account with the correct level of privilege to the server.

    it is generally considered good practice to revoke the local system account's access to SQL Server and run your sql services under a domain account. however ... it's still up to you. if it only needs access to itself, and you're happy with using local system, and are assured it poses no security risk, no one's going to stop you!

    Tom Pullen
    DBA, Oxfam GB
  3. chopeen Member

    Thanks for your remark, Thomas.<br /><br />The machine I am talking about is used only for tests, but I'll change its configuration - just to know how to set up a domain account, so SQL Server can use it. So be prepared for my questions concerning this problem soon. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br /><br /><i>Rediscover the web<br /<a target="_blank" href=http://www.mozilla.org/firefox/>http://www.mozilla.org/firefox/</a></i>
  4. thomas New Member

    heh, i'm sure you'll figure it out. it isn't exactly the science of rockets, is it?

    Tom Pullen
    DBA, Oxfam GB
  5. satya Moderator

    On a stand-alone computer that is running SQL Server, you can remove the BUILTINAdministrators login from SQL Server and limit this type of access.

    To work out, explicitly add the account that is being used for the service as a SQL Server login. You do need to assign the "Sysadmin" role to this login.

    And I believe MS released one support hotfix for such job failures with 'NT AUTHORITYSYSTEM' account.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page