Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
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?
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.
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.
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.
- 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.
Last Update: 2010-03-30
About the author
View all my tips