Changing passwords for logins used with SQL Server maintenance plans

By: Atif Shehzad   |   Updated: 2010-03-30   |   Comments   |   Related: > Maintenance

Problem

I have changed the password of one of my SQL Server logins used for SQL Server Maintenance Plans. Everything was okay until a SQL Server Agent Job responsible for database backups failed.  The SQL Server Agent Job was created by the Maintenance Plan Wizard. When I created the maintenance plan I did not provide any connection parameters, so how can I synchronize the password change in the SQL Server maintenance plan?

Solution

SQL Server maintenance plans can be created using the maintenance plan wizard or the designer. The designer lets you configure connection parameters for maintenance plans, but the maintenance plan wizard does not.  It uses the current session login for the maintenance plan connection without prompting for any connection parameters . Now whenever you change the password for that specific login, you also need to update the password in the connection settings for the maintenance plan, otherwise the maintenance plan will not work.

Let's take a look at an example.

Configure Test Case

-- Script 1: Create Login and User
-- Create Login to work with
CREATE LOGIN TestUser WITH PASSWORD = 'TestUser'
GO
-- Assign sysAdmin role.
EXEC SP_ADDSRVROLEMEMBER 'TestUser', sysadmin
GO
-- Create database user for Login TestUser
USE AdventureWorks
GO
CREATE USER TestUser FOR LOGIN TestUser
GO

So we have a login TestUser with password 'TestUser' ready to work in the AdventureWorks database. The reason to grant the sysadmin role to TestUser is that the sysadmin role is required to work with maintenance plans.

Follow these steps to create a maintenance plan using the wizard

• Login to SSMS with the TestUser login
• Right click on the Maintenance Plans folder to create maintenance plan using the wizard

• Provide the name Test-MaintenancePlan for distinction. No schedule is required for this test. As we are just simulating a working maintenance plan without any specific requirements, just follow the wizard steps with the default options and just check the task 'Check Database integrity'.

• After several screens a report will be generated like this that confirms the successful configuration.  Just click Finish to close the wizard.

Simulate Problem

A maintenance plan has been created in the Maintenance Plans sub-folder under the Management folder. The Maintenance Plans folder may be in a slightly different location in SQL Server 2005 and 2008, but it will be under the Management folder.

The Job that was created for this maintenance plan is configured to be on demand, without any schedule. Now execute the maintenance plan job by executing the entire maintenance plan or just running the scheduled job in Jobs folder. Important thing to remember is that if your maintenance plan has more than one sub-plan defined then executing the whole maintenance plan will generate the error message. In that case you could execute the individual Jobs created for each sub-plan under Jobs.  However in our case, since only one sub-plan was defined it doesn't matter which way this is run.

 Now to simulate the real problem we will change the password of TestUser to 'Changed' and try to run the maintenance plan job again.

-- Script 2: Change password of TestUser
ALTER LOGIN TestUser WITH PASSWORD = 'Changed'
GO

After the password is changed to 'Changed' re-login to SSMS with the new password to confirm the password change. Then execute the maintenance plan job again.  This time the execution will fail with this message.

"Execution of job 'Test-MaintinancePlan.Subplan_1' failed. See the history log for details."

In the log summary we can see that the job failed due to login failure.

Apply Solution

To fix this we need to synchronize the password in the maintenance plan connection parameters. For this purpose we have to open the plan in design view. Modify or double click the maintenance plan to open it in design view. Then click on the manage connections drop down and select your connection.

A connection properties dialogue will appear as shown below.

Provide the user name and the new password, click OK and save the maintenance plan. The password for TestUser has been synchronized and the maintenance plan will now work again.

Confirm the Solution

To confirm the solution execute the job or maintenance plan once again. This time a success message should be generated.

By using the connection properties in design view of maintenance plan you can update passwords and user information for a maintenance plan.

You can also create a separate login to use for your maintenance plans. Either create maintenance plans using that login or use the connection properties of each maintenance plan to alter settings for that specific login. This will ensure that changing passwords for other logins does not impact your connection properties for your maintenance plans.

Next Steps

• Always make sure to change the password in the connection properties of maintenance plans after changing the password for a login.
• Specify in your server documentation the logins that are involved in maintenance plan connections.
• If you are new to using maintenance plans, click here to read tips about using maintenance plans for different tasks.
• Click here to get BOL help to create a maintenance plan.

About the author

Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips