sql server 2000 backup/authentication to network share - access denied os error 5

  • sql server 2000. multiple servers getting access denied os erro 5 accessing network backup share on backup server (lets call it \\backup\sql$\backupsgohere for now )

    sql server is running under domain account, so does sql agent. they both are given sysadmin

    for some reason sql server when it runs the backup job does not authenticate with the service account it is running under. audit on the share showed in security event log that sql server came with SERVERNAME$ account instead of domain account. another server for some reason came with a different domain account (that it used to be running under... not now so).

    sql servers in question did restart when their service accounts were chaged.

    did anyone see such a case when sql server does not authenticate with domain account it is running under?

    thanks

  • I have to admit I have never seen that. I would generally point you at the share security and then the underlying file system security since that is usually the problem. But an entirely different user..

    You said you restarted SQL and that SQL and agent are running as domain accounts. Do those domain accounts have rights on the share AND the filesystem? Any SQL Agent proxy account?

    CEWII

  • By default the agent job runs under the account that created the job, not the agent account. If you go into the properties of the job, does it show the account that the agent was changed to, or does it show the old account?

    Joie Andrew
    "Since 1982"

  • That is a generally true statement, and a good idea. However if the owner is a SQL login, especially a sysadmin, it does run under the login context of agent. I never ran into it with non-sysadmin since we required every job to be owned by "sa"..

    CEWII

  • Also while looking for something else..

    Does your server domain account have these permissions on the local server:

    Setting Required Permissions

    To perform its functions, SQL Server Agent must be configured to use the credentials of an account that is a member of the sysadmin fixed server role in SQL Server. The account must have the following Windows permissions:

    Adjust memory quotas for a process

    Act as part of the operating system

    Bypass traverse checking

    Log on as a batch job

    Log on as a service

    Replace a process level token

    CEWII

  • Joie Andrew (11/23/2009)


    By default the agent job runs under the account that created the job, not the agent account. If you go into the properties of the job, does it show the account that the agent was changed to, or does it show the old account?

    accounts set up according to BOL, the have full controll for the backuyp hidden share. that's why access denied os error 5 kills me.

    and the best part: my sql servers 2005 do not have acces denied problem. run under same service accounts (same AD group, global).

  • so do I :). job is owned by sa.

  • Also while looking for something else..

    Does your server domain account have these permissions on the local server:

    Setting Required Permissions

    To perform its functions, SQL Server Agent must be configured to use the credentials of an account that is a member of the sysadmin fixed server role in SQL Server. The account must have the following Windows permissions:

    Adjust memory quotas for a process

    Act as part of the operating system

    Bypass traverse checking

    Log on as a batch job

    Log on as a service

    Replace a process level token

    CEWII

    That is a really good point. In SQL Server 2005, SQL Server Configuration Manager adds those rights to the account when it is defined, but not so when done through the services mmc in Windows.

    Joie Andrew
    "Since 1982"

  • I want to say that I remember hearinb about a bug in SQL 2000 about backing up to a network share, but now I cannot find it. I have thought of a couple of other things to try though:

    - Try mapping the share and then trying to perform the backup through the mapped drive (although that may not work if the service account cannot see the drive)

    - Try the steps in this article. It is speaking about backing up from one server to another, so I am not positive that it will work if you are backing up to network storage such as a SAN/NAS. http://windowsitpro.com/article/articleid/14025/why-cant-i-backuprestore-my-sql-server-database-to-a-share-on-another-server.html

    Joie Andrew
    "Since 1982"

  • Elliott W (11/23/2009)


    Does your server domain account have these permissions on the local server:

    Setting Required Permissions

    you mean?

    Elliott W (11/23/2009)


    account that is a member of the sysadmin fixed server role in SQL Server. The account must have the following Windows permissions:

    Adjust memory quotas for a process

    Act as part of the operating system

    Bypass traverse checking

    Log on as a batch job

    Log on as a service

    Replace a process level token

    this is set up in GPO.

  • I'm asking if the user has the right service permissions, I am grasping at straws but I don't have anywhere else to go.. As far as GPO I would verify that these are indeed set for the SQL server login, I don't care what I am told I verify it because my notworking people are wrong sometimes..

    CEWII

Viewing 11 posts - 1 through 10 (of 10 total)

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