MS SQL and sa created tasks

[awaggoner]

I have just used ERPM to change the sa password on a MS SQL server. I currently have several Jobs created (backup and maintenance) when I was logged into the MS SQL Server management Studio. Now the Jobs will not run, even when I manually execute them while logged in as the sa account.

I have changed the connection properties of one of the Jobs to use the domain administrator account, and it will run. However, once I start to manage the the domain administrator account with ERPM, I would suspect this Job would fail again.

Is there a way to push the sa password changes into the maintenance/backup Jobs so they will work? Will I need to use an unmanaged account for these jobs?

Thank You,
Alan

[Joe]

You basically have two choices.

The first is to run these jobs as an unmanaged account. This is obviously not ideal which leads to the second option, being to use the custom propagation capabilities of ERPM.

Custom propagations allow you to express additional processes and locations to which you would like ERPM to discover and propagate password changes outside of the standard locations in which we look. ERPM does not, as a standard update credentials in SQL jobs such as backup and maintenance jobs. This will require you to express the custom propagation you wish ERPM to include in your password change jobs. You will find the ability to build a 'master list' if you will of these custom propagations under the Settings | Discovery and Propagation | Discovery and Propagation Defaults... drop down list. The standard locations that we look for account usage is listed under Application-defined search items. You can extend these discoveries and propagations under the User-defined search elements window. You will also have the option to add these same custom propagations under the Propagation Settings tab of an individual job. As stated earlier, because we don't update credentials in SQL specific jobs you can use a script such as a T-SQL script to update these credentials in the SQL backup and maintenance jobs and call that script in accordance with the password change job of your SQL accounts or if you wish to update the domain administrator account and continue to run the jobs under that. To run a T-SQL script in accordance with the password change job, you will select the Propagation Settings tab of the password change job | Run Arbitrary Process to Find/Update Credentials | Change | specify the path of the script to call, plus any necessary parameters and the credentials under which the arbitrary process must run on the Credential Info tab; finish by selecting the system set to which you wish to run the custom propagation.