|
Hi, i have a maintenance plan of checking database integrity which has been running fine.But suddenly the job is failing continuously.When i ran the job using t-sql its running fine.I dont understand the reason why its failing as a part of maintenance plan.Pls reply ur suggetions... Thnx in Advance
(comments are locked)
|
|
Based on your comment about on the error "Could not obtain information about Windows NT group/user 'sa', error code 0xffff0002" it does in fact appear to be a security related issue. For one, SA is a local SQL user that really shouldn't be used. Do you use a system account for maintenance task such as the one that you configure SQL Server Agent to run under. Try changing the owner of your Integrity job.
(comments are locked)
|
|
Sounds like a security issue at first blush. Can you run the T-SQL as the user the job runs under? Error messages would be a great help here. its wrking fine wen i tried for tsql.
Oct 04 '10 at 11:18 PM
jinuvk
Has your instance been changed to Windows Authentication?
Oct 05 '10 at 05:25 AM
Blackhawk-17
no its still in mixed mode.
Oct 05 '10 at 08:01 PM
jinuvk
(comments are locked)
|
|
What's happening here is SQLAgent is trying to launch the task while logged in as sa, but SQL Server is expecting the login credentials of a Windows user rather than a SQL Server user. Create a dedicated account for the SQL Server Agent, give it the appropriate permissions within Windows & within SQL Server, change the SQL Server Agent service so that it logs in with those credentials and restart it. Then you should be good.
(comments are locked)
|
Can you post the Error message you are getting so that it will be really useful for us to give the solution easier? Please Check the job history for error messages
Not just the job history, but also the log file written out by the SSIS process.
for system db integrity check the message outcome is "Alter failed for Server 'XYZ'."...and for user db is 1) "Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.Could not obtain information about Windows NT group/user 'sa', error code 0xffff0002."