November 21, 2007 at 3:21 am
Hello all,
last night a job failed on a W2003 SQL2005SE SP2 machine. It has been running without problems.
What changed is that I disabled the BUILTIN\Administrators account on this machine yesterday.
The owner of the job is in the Domain Admins group, and the Domain Admins group is a member of the local admin group on the machine.
Is the disabling of the BUILTIN\Administrators account the reason that the job does not run? I know that by disabling BUILTIN\Administrators, the local admin members are no longer sysadmin on the SQL Server.
The owner of the job is also member of a domain group dba, and this group is sysadmin on the SQL Server of the machine, but not domain admin.
Thanks in advance for helping.
Hans
November 21, 2007 at 3:45 am
Who is the owner actually?
I would say to assign a valid sql login as owner to your job. Other than you're administering the instance through that job (this requires sysadmin), or administer the whole domain (domain admins group?!), assign the right permission level to the sql login here, for eg "backup operator" if it runs backups; BOL has plenty of details about instance and database security.
November 21, 2007 at 3:52 am
Thanks for swift reply michaela.
The owner is a domain group account, who is also member of the domain admin group.
The job is a maintenance plan job, backups and optimizations.
The backups are not saved on the local server, but on another server on the same domain.
In this case, can a sql account have enough permissions to access the server where the backups will be placed?
Hans
November 21, 2007 at 4:03 am
If you want to take backups over the network then you have to use a domain account. The usual practice is to take backups locally then to move them to other disk (remote server) or to tape; this is actualy MS recommendation.
Going back to your issue, it looks like your initial owner does not have access anymore to the sql instance. So grant access again or change the owner, but if changing it you would have to check its permissions to write the bkp to the other server.
November 21, 2007 at 4:20 am
I will try to change the owner and keep you posted.
Thanks.
Hans
November 22, 2007 at 12:12 am
If you've disabled builtin admins and your account is not either a member of another group that does have permission to the SQL server or the login has been granted access to the server that's likely the cause of your problem.
Quickest fix/test would be to grant the specific login the necessary permissions in SQL Server.
November 23, 2007 at 1:55 am
I tried that, but I'm missing something and I can't figure it out.
To recap:
- The job is running under the SQLSERVERAGENT service
- Apparently it is important who is the owner of the job.
- The owner of the job is a member of the domain admin group
- The domain admin group is a member of the local admin group on the server where the SQL Server is running
- So the job owner is SQL Server sysadmin (via the BUILTIN\Administrators group).
In this set up, the job runs.
When I disable the BUILTIN\Administrators, the job does not run.
So I added the owner of the jog specifically as a login of the SQL Server, and granted it sysadmin (and serveradmin) permissions.
The job still does not run.
I also added the service account that is used for SQLSERVERAGENT as a login to the server, and granted that account sysadmin and server admin permission; and the service account is also member of the domain admin group. The job still does not run.
There must be a way to have the BUILTIN\Administrators disabled and still run the job, or not?!
Hans
November 23, 2007 at 3:02 am
Some extra information about the job: the job is a backup task to makes a backup on a different server. So I realize that the owner needs access to the backup folder on that machine. Because the owner is a member of the domain admin group, this access is granted.
November 23, 2007 at 7:50 am
I found a warning about the job in the Windows Event Viewer.
It says:
'
The job failed. Unable to determine if the owner ( ) of job DB Backup Job for DB Maintenance Plan 'Maint1' has server access (reason: The EXECUTE permission was denied on the object 'sp_sqlagent_has_server_access' database 'msdb', owner 'dbo'. [SQLSTATE 42000] (Error 229)). '
Has anyone seen this before? And found a solution?
Hans
November 26, 2007 at 5:51 am
I've had the same problem before. Sort of an intermittent thing. If you switch the owner of the job to sa and run it, it should run successfully. Then switch it back to the one that fails now and it will continue to run successfully. Then, one day, it will fail again with the same error.
Seems to me that on a run, it cannot determine if the owner of the job is in a group that has sysadmin rights on the instance. Then, it does not try and check again until you change the owner of the job.
Could always leave it as sa.
November 27, 2007 at 2:19 am
Grant SQLAgent explicit permission to be able to login to the SQL Server. Place the SQLServerAgent Login in Admin Group on the local Server/Box.
November 27, 2007 at 3:12 am
Thank you for the reply.
I have tried this, but it does not work. I get the message 'Unable to retrieve steps for job ...' .
Only when I enable BUILTIN\Administrator again, the job runs.
Hans
November 27, 2007 at 3:37 am
So SQLAgent Account is sysadmin on SQL Server, and Local Admin on OS. Then Change the Job owner to "sa" and Try again.
November 27, 2007 at 4:29 am
First, go into your SQL Server Configuration Manager and verify that NONE of your services are running under the Built-In Administrators account. It doesn't have to be the SQL Server Agent that's running that way. It could be your Browser or just the SQL Server Service.
Second, make sure both servers in questions have specifically been added in the Windows OS Local Admin group. Don't just assume that because it's part of the Domain Admins group that it'll have access to both servers. Make sure it physically does.
Third, make sure there isn't a DENY permission on the shares or in SQL Server that might actually be preventing access. The way to do this is to go to a command prompt and type "Net Group MyDomainAcct /domain" without the double-quotes. This will give you a list of all groups the User Acct is a member of. Check each and every one of them for deny permissions as noted above.
Let us know the results of the above steps. Make sure you've re-disabled the account so you can see if you get an error again (or even a different error).
Just a footnote. Having your SysAdmin Domain Account as a member of the Domain Admin group is a Very Bad Thing. If that account gets compromised, you have just compromised your entire domain, not just your SQL Server. Make it just a normal Domain User account with certain rights such as Login as a Service and Process Token.
January 30, 2008 at 2:25 am
BEACHDBA (11/26/2007)
I've had the same problem before. Sort of an intermittent thing. If you switch the owner of the job to sa and run it, it should run successfully. Then switch it back to the one that fails now and it will continue to run successfully. Then, one day, it will fail again with the same error.Seems to me that on a run, it cannot determine if the owner of the job is in a group that has sysadmin rights on the instance. Then, it does not try and check again until you change the owner of the job.
Could always leave it as sa.
This is exactly what i am seeing. My windows login WILMSLOW\johnmo is in a security group called wilmslow\#DBA, and that group is a sysadmin. The group does not have domain admin rights or local admin rights. My account has local admin rights explicitly.
The sql agent account (and service account) run as wilmslow\syssql which is a domain admin, and is not explicity granted access in SQL. The SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER login group is a sysadmin and has not been altered in any way.
My jobs were setup by myself with Wilmlsow\johnmo as the owner, and were happily working for the last month until last night after a reboot none of them would start. All said The job failed. The owner () of job TestDataRecieved does not have server access. So to get them running i changed them to sa, as it was the early hours of the morning and after such a long day i just needed sleep.
As BeachDBA says, if i now change the owner back to wilmslow\johnmo, they work fine, i just dont want to take the risk that they might all stop again so im leaving them as sa.
If it matters im running Standard Edition SP2 no CU applied.
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply