We have an issue with our user report subscriptions in SQL 2005. When the user's AD password expires, he changes it. The user credentials used to write his reports to a file share then fail unless he updates all of his subscriptions with the new password.
I would like to set all subscriptions that write to a file share to use the same account and have a nightly package that updates the subscriptions with that account.
Has anyone done anything like that or addressed this issue in some other way?
I get get the subscriptions that write to file shares with this statement.
select * from subscriptions
where deliveryextension = 'Report Server FileShare'
The data to be updated is in the field ExtensionSettings