Mystery? Why Job schedule failing under user account
-
Dear DBAs,
I created a logshipping on both SQLServer 2000. The job owner is my nt account. I have a local admin account and Sql security mode is mixed. It fails, I have to change to sa then it runs.
This is the long mystery to me and our team. Could any one help to find out why? or give me any thoughts.
Thanks a lot
-
Is your account a member of the sysadmin fixed server role on that SQL Server (also remember to check Windows groups, such as BUILTIN\Administrators if you are in the local administrators on the server SQL Server runs on)?
K. Brian Kelley
@kbriankelley -
You should never have a scheduled sql agent job run under your security credentials.
One issue is when your password changes sql agent will remember your old password causing you to get locked out of the system
You should always use windows domain accounts that are set up as service accounts with privileges to the sql server resources, and any other resources the sql jobs need to complete ie Saving backups to a different server and such.
-
Thanks guys
sql agent job is running under windows domain accounts, not my account. The job owner is automatically created under my account. Why it fails even if I am a member of global admin group which the group is the member of local admin.
-
Try checking the SQLServer Service. It may have different login credentials than the account you believe you are using.
-
I've seen the same thing and with replication jobs. Just last week I had a transaction log backup job that was created via a maintenance plan fail under a user account with permission problems. The user account was an administrator on the server as well as a SysAdmin. My STD is to run these under SA, but every now and then, one sneaks by when I make an alteration. I've seen the same thing with transactional replication distribution agent jobs. I've assumed it's an authentication problem of some kind but have not had time to look into it. All servers in question are running at least 8.00.818.
Question: if SQL Server is running under local system account - does that affect in any way the abilty of Agent to run job jobs owned by network accounts? I thought no but recently had some odd errors.
I understand that if you run SQL under your personal network account, when you change a password SQL can lock you out as it tries to restart services, but owning a job and changing your password has not affect. SQL doesn't authenticate you via your password for job execution.
-
One more point, I don't think this would affect you since you say the account was a global admin, but the job owner has to have rights to create any log files the job creates (like step logs). They are created by the agent account, but rights to update or create logs are checked against job owner.
-
Thanks Guys
Both SQL Server account & SQL Agent account uses a domain account we created. This account has local admin on both SQL Server machines for log shipping. I still wonder why it only works under sa, just a curious.
Kenny
-
When you look at job history and expand the job steps, what error do you in the last step to run?
K. Brian Kelley
@kbriankelley -
Hi Kelley,
Here is the error message:
On Source Server:
The job failed. Unable to determine if the owner ('Domain\my account') of job Transaction Log Backup Job for DB Maintenance Plan 'LogShipping for ETRADE' has server access (reason: Could not obtain information about Windows NT group/user 'Domain\my account'. [SQLSTATE 42000] (Error 8198)).
On Destination Server:
The job failed. Unable to determine if the owner ('Domain\my account') of job Log Shipping copy for NRIO0009PMS.ETRADE_logshipping has server access (reason: Could not obtain information about Windows NT group/user 'Domain\my account'. [SQLSTATE 42000] (Error 8198)).
The job failed. Unable to determine if the owner ('Domain\my account') of job Log Shipping Restore for NRIO0009PMS.ETRADE_logshipping has server access (reason: Could not obtain information about Windows NT group/user 'Domain\my account'. [SQLSTATE 42000] (Error 8198)).
Thanks a lot
Kenny
-
It's a known issue. The workaround from Microsoft is to configure the job to run as sa. More here:
How to troubleshoot a SQL Server 8198 error (834124)
PRB: 8198 Error Message Returned from Job Owned by Windows NT Authenticated User (241643)
K. Brian Kelley
@kbriankelley -
You might want to try to reboot your ADS. It should fix the issue.
-
Even reboots sometimes don't fix the issue. Hence the reason MS recommends using sa.
K. Brian Kelley
@kbriankelley -
When I got the above error message, I spent quite a time changing the maint job owner to sa on 30 servers. So those jobs runs succesfully. Rebooting the ADS fixed the problem completely
-
I think I know your problem.
Make sure your domain account is the same domain with the SQL
Server that you try to run the job. If they're not (for example
Prod.com vs Dev.com) then ask the System Engineer whether
there is a Trusting relationship between those 2 domains.
The error often indicated that they're not the same and no trusting
relationship. Any job run with "SA" should be run, because it has
all the privileges/rights within the local server (and other resource
beside the local server if the SQLService account is a domain account). Hope this will help.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply