November 23, 2009 at 11:53 am
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
November 23, 2009 at 1:05 pm
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
November 23, 2009 at 1:34 pm
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"
November 23, 2009 at 1:38 pm
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
November 23, 2009 at 2:09 pm
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
November 23, 2009 at 3:04 pm
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).
November 23, 2009 at 3:05 pm
so do I :). job is owned by sa.
November 23, 2009 at 3:19 pm
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"
November 23, 2009 at 3:28 pm
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"
November 25, 2009 at 2:02 pm
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.
November 25, 2009 at 2:54 pm
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