Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Unanswered: Failing Backup Jobs

    I have a DB Maintenance plan created that performs optimizations, integrity checks, and a database backup. The optimizations and integrity checks work fine, but the backup job fails. The backup job is to write the .bak file to a network share.
    When I change the owner of the job to "sqluser", the job fails at step 0 withe the following error:
    The job failed. Unable to determine if the owner (rsn\sqluser) of job DB Backup Job for DB Maintenance Plan 'Online Database Server Maintenance Plan' has server access (reason: Could not obtain information about Windows NT group/user 'rsn\sqluser'. [SQLSTATE 42000] (Error 8198)).
    Checked with the system admin for this server and sqluser has priviledges to do everything, including write to the network share.

    So, now I change the owner of the same maintenance job from sqluser to "sa". I now get this error:
    Executed as user: RSN\sqluser. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

    Checked the settings for MSSQLSERVER and SQLSERVERAGENT in Control Panel. Everything seems to be in order according to the other threads I have read.

    For grins, I tried to ensure the sqluser password on the SQL Server Agent was correct. When I try to enter the new password I get this:
    sql server agent startup account could not be verified
    I click yes (it asks if I want to continue anyway).
    Is the password not being registered properly in the agent?

    One other thing, the sqluser user is listed under Security|Logins as connecting with Windows Authentication. We have several other servers that are set up the same and work fine. What am I missing? I'm pulling my hair out!

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    What version of SQL (include service pack) and what's the version (and service pack) of OS?

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Dec 2005
    Posts
    4
    OS is:
    windows 2000 sp 4

    SQL Server is:
    Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just to humor me, stop the SQL Agent, wait 90 seconds, then restart it. Once the SQL Agent is running again, retry your job as rsn\SQLUser and let us know what you find.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Posts
    269

    Cool

    ---------------deleted------------------------------
    Pat has given the answer
    Last edited by mallier; 12-15-05 at 04:59.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  6. #6
    Join Date
    Dec 2005
    Posts
    4
    Okay, did as you suggested. Got the same results. Make the owner of the job RSN\sqluser and get nothing. Failure at step 0.
    Unable to determine if the owner (RSN\sqluser) of job DB Backup Job for DB Maintenance Plan 'Online Database Server Maintenance Plan' has server access (reason: Could not obtain information about Windows NT group/user 'RSN\sqluser'. [SQLSTATE 42000] (Error 8198)).
    Then switch the owner of job to 'sa' and it fails at step 1 trying to connect as sqluser, then sqluser account is locked out.

    Here's a synopsis of what is happening when I run the job manually in Query Analyzer:
    Statement executed:
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID C6C603F6-67CF-4087-8FE5-02014CD3D0B8 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "\\160.132.140.75\sql_backup\onlinedb" -DelBkUps 4WEEKS -CrBkSubDir -BkExt "BAK"'

    Results from Query Analyzer are attached in .txt file.
    Attached Files Attached Files

  7. #7
    Join Date
    Dec 2005
    Posts
    4

    The Plot Thickens

    In an effort to make sure that we had some backups saved somewhere, I decided to create a new backup plan (called 'Temp DB Backup Plan'). I pointed the backups to go to the D: drive on the server.

    I ran the job as the owner (RSN\sawyerad) and got this error:

    The job failed. Unable to determine if the owner (RSN\SawyerAD) of job DB Backup Job for DB Maintenance Plan 'Temp DB Backup Plan' has server access (reason: Could not obtain information about Windows NT group/user 'RSN\SawyerAD'. [SQLSTATE 42000] (Error 8198)).

    Okay, no problem right? I switched the owner to sqluser and got the same error:

    The job failed. Unable to determine if the owner (RSN\sqluser) of job DB Backup Job for DB Maintenance Plan 'Temp DB Backup Plan' has server access (reason: Could not obtain information about Windows NT group/user 'RSN\sqluser'. [SQLSTATE 42000] (Error 8198)).

    So, I switched it to run with the owner sa. All ran fine. To the D: drive.

    Does that shed anymore light to you on what could be happening? Not to me, but I thought it might be helpful. I am assured by the system admin folks that the sqluser has all privs to do whatever with the server. I have even logged onto the server and can map successfully to the network share to which I want to copy the backup files.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •