Automate stored credentials updates

Question

Post reply

Automate stored credentials updates

ttdeveloper

SSCrazy

Points: 2370
More actions
November 29, 2016 at 11:26 am

#315374

Good Day
Long story short and I'm pessimistic ill get a fix for this. We are using stored credentials for one of our shared data sources using a service account.
The account password changes every 90 days and we are trying to automate the password reset process. Does anyone know where if possible we can access the password string (I've read from MSDN that its encrypted so i may not have any luck on this)?
If we cant access the password string anywhere, does anyone know of a clever way of automating the password change outside of going through report manager every 90 days.
Its getting to the stage where we have email subscriptions using the service account and every now and again, they fail to generate because the password hasnt yet been updated.
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 1

Could you look at this article about SSRS credentials

[/url]

Lowell SSC Guru Points: 323504 More actions · Answer 2

the obvious fix is to set up a specific account for the SSRS account that does not password expire.

on my SSRS instances, all reports use shared data sources, so that the connection info is centralized; changing one of those shared data sources to use an updated password then affects all reports that used that shared connection; that avoids having to change each report, of course.

At least if it's centralized, it's easier to update manually, but i'm not sure if you could put something together that re-publishes the updated shared connection automatically,since at some point, someone has to enter the new password; i'll fiddle with it a bit and report back.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Thom A SSC Guru Points: 99126 More actions · Answer 3

Lowell (12/6/2016)
the obvious fix is to set up a specific account for the SSRS account that does not password expire.

What we do, that account has very limited permissions on the network, and again, is very limited on our SQL server (only having read and execute permissions on a couple of databases).

Like yourself, we also use a shared datasource, so, if for whatever reason we need to change the password, it's not such a problem there.

For saving of files, however, this is a problem, as each subscription has to have these credentials saved... Hence, again, having a non-expiring account.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ttdeveloper SSCrazy Points: 2370 More actions · Answer 4

Thanks so much for getting back guys. So we have a number of shared data sources in the system connecting to SQL boxes and yes to all your points, those connections use local logins that never expire. However the one we are trying to automate is out of our hands. It connects to our Teradata DW and the DBAs have that set to expire every 90 (some service account huh).

Anyhow - the automation process for our linked servers and SSIS environment variables is up and working, seeing those are just SQL scripts that we schedule.

The piece we are struggling with right now as i mentioned is just the SSRS stored password. So I may not get anywhere with this one aside from a manual change in reporting manager.

I appreciate all your responses to this.

Thank you

ttdeveloper SSCrazy Points: 2370 More actions · Answer 5

Could you look at this article about SSRS credentials
[/url]

Good to know article - i still need a way to build the expression to always use the current password. it doesn't really cover a scenario where pw has expired, needs changing and new pw updated to expressions. Somehow seems like a lot of moving parts.

Very useful to know though but i don't think this solves for my current problem.