Learning While Aging

MS SQL Server Agent job failed to run a DTS package

There is a SQL Server Agent job in our server that runs a DTS package to transfer data from one database to another. However, when I checked the job history, I found out the job never ran. The error message that I got from the job history is like this:

The job failed. Unable to determine if the owner (<Domain\Account Name>) of job <job name> has server access (reason: Could not obtain information about Windows NT group/user ‘<Domain\Account Name>’. [SQLSTATE 42000] (Error 8198)).

However, if I execute the DTS package directly from the DTS package list (right-click the package and click Execute Package), everything is fine and no error pops up. So what is going on here?

First of all, it is apparently an access permission problem. The question is, however, why the interactive execution of the DTS package works, but the SQL Server Agent job fails even though the job’s owner is the same as the logon user? The reason is that the above two ways of executing the DTS package use different security context.

The interactive execution will launch the DTS package and execute it on the client side (if you use Enterprise Manager to connect to a remote server), so it uses the same account as the logged-on account.

The SQL Server Agent job uses cmdExec command to call DTSRUN utility to execute the DTS package on the SQL Server. In this case, the SQL Server Agent specifies which account has the ability to run cmdExec command. On our SQL Server, the SQL Server Agent only allows the SQL service account to run cmdExec, but the job’s owner is a domain account, therefore, the job fails every time and gives the above 8198 Error message.

According to Microsoft’s Workaround for fixing 8198 Error Message Returned from Job Owned by Windows NT Authenticated User, changing the job owner to a standard SQL Server user account is the easiest way to fix the error, and it worked for me.

References:

How to troubleshoot a SQL Server 8198 error

PRB: 8198 Error Message Returned from Job Owned by Windows NT Authenticated User

[ratings]

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x