October 25, 2007 at 12:25 pm
I have posted this in the MSDN, and I have looked around MSFT and Google, and SQLServerCentral, and I cannot for the life of me find any real information.
Standard Configuration (multiple servers, all have same issue):
Windows Server 2003 Standard x64 SP1 - 5.2.3790
SQL Server 2005 Enterprise x64 SP2 - 9.0.3042
SQL Server Service and Agent Services run under the same Domain\ServiceAccount and Password.
On the SQL Server, DBA Groups are given permissions by their Domain\GlobalGroup, not individually.
Jobs created by the individual DBA are owned by that DBA Domain\DBAUserName.
When the server reboots, for instance after patching or maintenance, the jobs owned by DBAs will fail with the following error:
The Job Failed. The owner () of job does not have server access.
If I edit the job following reboot, reassign the job owner to the Domain\DBAUserName, it will work again, until the next Windows Reboot.
I can also add the Domain\DBAUserName individually to the server and give appropriate permissions, but that is what we are trying to avoid!
Any assistance will be GREATLY appreciated. Thank you!!
The problem I really struggle with is that we are allowed to add Domain Groups to SQL Server to give groups of people that work together on the same project the same access, but we cannot allow that GROUP to own the job. If I create a job, and someone else on my team needs to work on it becuase I am not here (or no longer working here), then an SA has to reassign the job to the person working on it TODAY. Tomorrow, someone else from the team might need to work on it. This just does not make sense to me . . .
October 25, 2007 at 3:03 pm
I've had similiar issues, check to make sure that your service account is part of the Local Admins of the machines. If not then that account must have the following to successfully work
Act as Part of the Operating System
Traverse Checking
Lock Pages In Memory
Log on as a Batch Job
Log on as a Service
Replace a Process Level Token
Hopefully that helps
Ryan
October 25, 2007 at 3:26 pm
I don't believe that is the issue, becuase if you needed the SQL Server/Agent Service Domain account in the local admin role, the installation would have placed them there, instead of inside their own SQLServer2005....$servername$instancename Local Groups. I am not sure, but I can't find a Microsoft document that lists any reason why one should have this account in the Administrator Group on the server.
This is really unusual. It fails before even attempting to start the first step:
Date10/24/2007 3:01:03 PM
LogJob History
Step ID0
Server
Job Name
Step Name(Job outcome)
Duration00:00:00
Sql Severity0
Sql Message ID0
Operator EmailedENT_SQL_OPERATOR
Operator Net sent
Operator PagedENT_SQL_OPERATOR
Retries Attempted0
Message
The job failed. The owner () of job AngelEmployeeFeed does not have server access.
I just can't find a reason. We installed Enterprise edition within the last month and SP2 at the same time (so it is the most recent version of SP2. I know they had an issue before March 07).
October 25, 2007 at 3:31 pm
But the answer is right in the log. The user doesn't have access to the server.
Try adding them and see what happens, do it on a test machine first if you can duplicate it.
I have all of my SQL Servers set up with a Domain account acting as the Service Account and it is added to the Local Administrator group of each server, and I don't have problems with any of my jobs, except for the ones that I screw up when I create them.
October 25, 2007 at 3:44 pm
The answer is right in the log. And I really want to thank you for trying to help me. The log says that the user () does not have access to the server. That would be Domain\DBAUserName. We have the security setup that the Domain\DBAUserName gets his\her permissions from the Domain\GlobalGroup.
Clarification:
Server --> Security --> Logins
Domain\DBAGlobalGroup
(no individual Domain\DBAUserName accounts here!)
But, the Job is owned by Domain\DBAUserName.
When the job fails following a reboot, it SHOULD read 'The user (Domain\DBAUserName) does not have access to the server. Not, The user () does not have access to the server (which I read the () to mean ('NULL')). It is like the Agent service cannot rectify the Domain\DBAUserName to his\her proper Domain\DBAGlobalGroup to verify permissions.
Again, if I add the Domain\DBAUserName for EACH DBA to the SERVER Security Logins, then their jobs work fine. Our problem is that we do not want to have 50 DBA's individually on the server, when we can have 5 DBA Groups. Why jobs are not able to be owned by the DomainGroup is completely beyond me, but that is my struggle.
Again, Thank You for your help, Ryan. I appreciate it!
October 25, 2007 at 8:30 pm
James L Kerr (10/25/2007)
I don't believe that is the issue, becuase if you needed the SQL Server/Agent Service Domain account in the local admin role, the installation would have placed them there...
no it won't. need to do that yourself. sql server will not presume that u want to run any of its' services under local admin account -- in fact quite the opposite, it starts by assuming you won't (though we usually do).
is the DBA Group local admin on the server?
October 26, 2007 at 7:36 am
No, the DBA Group is not local administrators on the machine. We don't want the DBA's to have that much access to the server. The Data Architecture team (my team) is the primary group that has access to the server. Now, we have other machines that do not have this problem, and none of the machines have the SQL Server Service Account in the local administrators role on the server.
Does Microsoft suggest placing the SQL Server Service Account in the Local Administrators role for a specific reason, just out of curiosity?
October 26, 2007 at 9:47 am
Does Microsoft suggest placing the SQL Server Service Account in the Local Administrators role for a specific reason, just out of curiosity?
I don't have an answer to your initial problem, but SQL service accounts do not necessarily have to belong to Local Admin.
I found this in BOL (http://msdn2.microsoft.com/en-us/library/ms143691.aspx):
"Microsoft recommends using a domain user account with minimal rights for the SQL Server service, as the SQL Server service does not require administrator account privileges."
It goes on to say that the SQL Agent service account may sometimes require admin privileges, but it depends on how you use it. We use separate domain accounts for the SQL Server and SQL Agent services, neither of which belong to the Local Administrators group, and haven't had any problems.
October 26, 2007 at 11:43 am
Thank you, MIJ. That helps me understand that aspect, but as far as the initial problem, I am STILL at a loss. I just don't have anything telling me what is actually happening at the time the job runs . . . none of the event logs had anything. If I can replicate it on my test server, not production, then maybe I can setup a Profiler . . .
But, until then, has anyone else had the same problem before??
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply