Integrity Job Failure

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

more ▼

asked Oct 03, 2010 at 11:35 PM in Default

avatar image

1 1 1 1

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

Oct 03, 2010 at 11:40 PM Cyborg

Not just the job history, but also the log file written out by the SSIS process.

Oct 04, 2010 at 12:47 AM ThomasRushton ♦♦

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."

   2) "**Alter failed for Server 'XYZ'**."...
Oct 04, 2010 at 11:12 PM jinuvk
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Oct 05, 2010 at 08:14 PM

avatar image

40.9k 39 95 168

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 04, 2010 at 06:08 AM

avatar image

12.1k 30 36 42

its wrking fine wen i tried for tsql.

Oct 04, 2010 at 11:18 PM jinuvk

Has your instance been changed to Windows Authentication?

Oct 05, 2010 at 05:25 AM Blackhawk-17

no its still in mixed mode.

Oct 05, 2010 at 08:01 PM jinuvk
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 07, 2010 at 12:43 AM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 03, 2010 at 11:35 PM

Seen: 2400 times

Last Updated: Oct 03, 2010 at 11:35 PM

Copyright 2018 Redgate Software. Privacy Policy