Managing Connection properties of Maintenance plans using Powershell

Question

Managing Connection properties of Maintenance plans using Powershell

rollercoaster43

Ten Centuries

Points: 1306
More actions
June 18, 2014 at 3:45 am

#292505

Hi All,
We are managing the password reset activity from a central server for a login which is used on our servers for executing jobs and maintenance plan jobs.
We are using the below powershell script for changing password which works quite well :
$Items = Import-CSV $ENV:TEMP\PwdList.csv -Delimiter "|"
ForEach ($Item in $Items)
{
$Instance = $Item.ServerName
$Pwd = $Item.Pwd
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server($Instance)
$Server.Logins.Item('adw').ChangePassword($Pwd)
$Server.Logins.Item('adw').Alter()
}
Everything works fine, however, there are some maintenance plan jobs which uses this login. So I have to open the maintenance Plan -> go to manage connection -> local server connection and then change the password there as well. If I do not perform this activity, the maintenance plan fails.
Can someone please let me know if there is a way to handle this as well using powershell so that everything is automated ?
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

I have no access to a SQL Server instance so this is just a longshot (and probably not helpful) so sorry if this wastes your time.

Is there a PowerShell script (dropdown?) button or textbox that you can copy the necessary script for?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

rollercoaster43 Ten Centuries Points: 1306 More actions · Answer 2

Hi Gaz,

I am sorry I did not understand your question 🙁

My question is regarding maintenance plans wherein after changing password of a login, we have to manually reset the password inside the Maintenance plan in the connection manager.

There I cannot see any drop down or button for powershell script .. 🙁

Gary Varga SSC Guru Points: 82166 More actions · Answer 3

Some of the dialogs in SSMS provide the PowerShell commands that perform what is being done through the UI allowing the DBA (for the want of a better term) to copy the script. I don't know that area off the top of my head and was hoping that a longshot suggestion might prove lucky. Sorry that it didn't.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!