Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sql server 2000 backup/authentication to network share - access denied os error 5 Expand / Collapse
Author
Message
Posted Monday, November 23, 2009 11:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 12:21 PM
Points: 9, Visits: 90
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
Post #823440
Posted Monday, November 23, 2009 1:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
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
Post #823475
Posted Monday, November 23, 2009 1:34 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
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"
Post #823491
Posted Monday, November 23, 2009 1:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
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
Post #823494
Posted Monday, November 23, 2009 2:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
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
Post #823516
Posted Monday, November 23, 2009 3:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 12:21 PM
Points: 9, Visits: 90
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).
Post #823539
Posted Monday, November 23, 2009 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 12:21 PM
Points: 9, Visits: 90
so do I :). job is owned by sa.
Post #823540
Posted Monday, November 23, 2009 3:19 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
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"
Post #823545
Posted Monday, November 23, 2009 3:28 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
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"
Post #823549
Posted Wednesday, November 25, 2009 2:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 12:21 PM
Points: 9, Visits: 90
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.
Post #824933
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse