question

ChuaLiangWei-4173 avatar image
0 Votes"
ChuaLiangWei-4173 asked ChuaLiangWei-4173 answered

SQL Maintenance Plan Wizard - Schedule Reorganize Index (Login Failed for user error)

SQL Maintenance Plan Wizard - Schedule Reorganize Index (Login Failed for user error). How can we fix the error as we do not have issue using the Id to login and it has sysadmin right.


201716-image.png


sql-server-general
image.png (753.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered

Login Failed for user error

And what's not clear on the error message?
Check if the SQL login exists and have the permissions to execute the job with all it's tasks.
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChuaLiangWei-4173 avatar image
0 Votes"
ChuaLiangWei-4173 answered ErlandSommarskog commented

the login is exist with sysadmin right. The permission level no sufficient? Where can we check which login has permission to execute schedule job.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Check the SQL Server errorlog. There should be more details on exactly why the login attempt failed.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @ChuaLiangWei-4173,

Any update for this thread? Did you read your SQL server error log? If you get any other related error message, please share us the information. This maintenance plan run under SQL agent service account? Which account that running SQL agent service? Did the account is a sysadmin account or has permission to execute this job? To execute ALTER INDEX, at a minimum, ALTER permission on the table or view is required.


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChuaLiangWei-4173 avatar image
0 Votes"
ChuaLiangWei-4173 answered DirkHondong commented

This the error grab from the job history

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/13/2022 00:00:00,EPM Reindexing.Subplan_1,Error,,<DB>,EPM Reindexing.Subplan_1,,,The job failed. The Job was invoked by Schedule 10 (EPM Reindexing). The last step to run was step 1 (Subplan_1).,00:00:03,0,0,,,,0
05/13/2022 00:00:00,EPM Reindexing.Subplan_1,Error,1,<DB>,EPM Reindexing.Subplan_1,Subplan_1,,Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 13.0.5026.0 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 12:00:00 AM Error: 2022-05-13 00:00:02.59 Code: 0xC00291EC Source: {FD7724A7-9959-45DA-B972-7C82B0073AF4} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2022-05-13 00:00:02.59 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2022-05-13 00:00:02.69 Code: 0xC0024104 Source: Reorganize Index Description: The Execute method on the task returned error code 0x80131904 (Login failed for user 'svradm'.). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Error: 2022-05-13 00:00:02.72 Code: 0xC0024104 Source: {EC71C8C1-C082-4FC4-A966-841D1A254BB4} Description: The Execute method on the task returned error code 0x80131904 (Login failed for user 'svradm'.). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2022-05-13 00:00:02.72 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:00:00 AM Finished: 12:00:02 AM Elapsed: 2.391 seconds. The package execution failed. The step failed.,00:00:02,0,0,,,,0
05/12/2022 15:59:39,EPM Rebuild Index.Subplan_1,Error,,<DB>,EPM Rebuild Index.Subplan_1,,,The job failed. The Job was invoked by User svradm. The last step to run was step 1 (Subplan_1).,00:36:29,0,0,,,,0
05/12/2022 15:59:39,EPM Rebuild Index.Subplan_1,Error,1,<DB>,EPM Rebuild Index.Subplan_1,Subplan_1,,Executed as user: NT Service\SQLSERVERAGENT. Started: 3:59:39 PM Finished: 4:36:06 PM Elapsed: 2186.7 seconds. The package execution failed. The step failed.,00:36:29,0,0,,,,0
05/12/2022 15:40:04,EPM Reindexing.Subplan_1,Error,,<DB>,EPM Reindexing.Subplan_1,,,The job failed. The Job was invoked by User svradm. The last step to run was step 1 (Subplan_1).,00:56:04,0,0,,,,0
05/12/2022 15:40:04,EPM Reindexing.Subplan_1,Error,1,<DB>,EPM Reindexing.Subplan_1,Subplan_1,,Executed as user: NT Service\SQLSERVERAGENT. Started: 3:40:04 PM Finished: 4:36:06 PM Elapsed: 3361.94 seconds. The package execution failed. The step failed.,00:56:04,0,0,,,,0

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ErlandSommarskog commented

Hi @ChuaLiangWei-4173,

Executed as user: NT Service\SQLSERVERAGENT.

This job executed as SQL server agent service account. So suggest you to change your SQL server agent service account to admin account. Or an account that has ALTER permission on the table or view.


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So suggest you to change your SQL server agent service account to admin account.

Admin account where? NT Service\SQLSERVERAGENT is sysadmin on SQL Server (assuming that the connection is to the local server). If you mean an account which is a Windows administrator, that is absolutely against best practive.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Again, did you check for messages in the SQL Server errorlog?

Then again, this may be a good opportunity do what most other people are doing: switch to Ola Hallengren's maintenance solution, which relieves you from the DTS/SSIS component. http://ola.hallengren.com.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChuaLiangWei-4173 avatar image
0 Votes"
ChuaLiangWei-4173 answered

how can configure to allow my SQL ID to have ability/right to create maintenance plan wizard as my ID do not show that option.

anyone can show the path.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.