July 23, 2012 at 1:50 pm
Hello All,
I was aware that a subscription could send a report to a file share but just got into the specifics. I assumed the SSRS service account would need permissions but I see there is a different model.
In the subscription setup the end-user needs to enter in a user name and password. I don't particularly like this model. Clearly my users can't enter in their own username because their password expires.
What's the best practice here? Do I create a AD user with no password expiration and then hand that account over to end users?
July 24, 2012 at 3:17 am
What user have you assigned to the unattended execution account in the SSRS configuration?
If its blank I would recommend setting one up, then any reports which write files on a subscription use that account to run under.
July 24, 2012 at 9:19 am
Thanks for your response.
I have a domain account setup as the execution account (not the service account).
If I create the subscription I can enter this execution account and password. If I give the execution account permission on the network share the file will be generated.
I don't see how this provides any benefit, I assume I could just create any account (not the SSRS execution account) and do the same thing.
The user name and password is required when creating the Windows File Share subscription. Seems like a flawed model, I'd prefer my end users to never have to enter a password in that is not their own. Is the password stored in the subscription? And of course the password will expire.
Preferably there would be an option in the subscription creation to use the SSRS service account or the execution account. Pardon my griping...
July 25, 2012 at 2:52 am
Sorry, my bad, been a couple of years since worked with file share subscriptions.
Yes the username and password is stored within the subscriptions table in the executionsettings column, but they are hashed. But will be compared against the hash when running so a hash mismatch will cause a failure.
I can see where the flaw would be, I should think that there should be a check box which grays out the options and says something like "Use Unattended Execution Account" that way you never have to supply a username and password, it can simply pick it up from the SSRS config files.
Maybe one to raise a connect bug on asking for that functionailty to be built into a SP or a future release.
In light of that can do 1 of three things,
1. dont let users setup their own subscriptions (we dont let them here as they made a right mess of it first time we let them have that functionality)
2. create a new account with a none expiring password and supply it to the users
3. supply the unattended execution account details to the users
April 23, 2014 at 9:01 am
Hi everyone,
I was researching this exact scenario ...how to handle the expiring password problem and found this thread. I'm a bit late to the game, but I've found a workaround for this problem.
I worked up a bit of code which I put into a sql server agent job that simply identifies all ssrs subscriptions that are set to drop a file onto windows file share. It updates the username and password for these subscriptions to the same credentials (which do not expire) as the execution credentials. The job can be run at whatever frequency you deem appropriate, whether hourly, daily, weekly, etc.
This "solution" allows me to hit all my goals:
1) Users create and manage their own subscriptions with their own credentials. (They don't know that as soon as they do it, the job resets the credentials, so to them it is invisible and seamless)
2) Users never have to update their subscriptions' PW when their active directory PW is changed
3) The execution credentials are not shared or known among users or anyone who shouldn't know them.
One thing I actually like about the report server config. is that when they edit the subscription, even after my job updates the credentials to the execution login, the user is required to re-enter their own username and credentials to save their edited changes, thus eliminating the possibility of users hijacking those exec credentials. They cannot use them in any way.
Thought I'd share this since it took care of the issue, and looks like those on this thread were facing the same thing.
~wiperzeus
June 10, 2014 at 12:44 pm
wiperzeus, would you be willing to share the code for your solution?
June 10, 2014 at 1:17 pm
No problem-
Keep in mind that the username and password are stored in XML datatype as a hash value. I obtained the hash values for the execution account's Username and PW by manually creating a subscription, then checking the XML field. In the below code, you'll want to add your own hash values- you'll easily see where to do so.
I'm sure there is a niftier way to strip apart XML datatypes, but this code is super quick and easy 🙂
~wiperzeus
-------------------------
-------------------------
Use ReportServer
go
----this section updates the password;
update sub
set sub.extensionsettings =
left(convert( varchar(max) , sub.ExtensionSettings ) ,patindex('%<Name>USERNAME</Name><Value>%', ExtensionSettings) + len( '<Name>USERNAME</Name><Value>' ) - 1 )
+ 'enter username hash here'
+
right( convert( varchar(max) , sub.ExtensionSettings ) , (len( convert( varchar(max) , sub.ExtensionSettings) )
) - patindex('%</Value></ParameterValue><ParameterValue><Name>PASSWORD</Name>%',sub.ExtensionSettings) + 1 )
from reportserver.dbo.Subscriptions AS sub
where sub.DeliveryExtension = 'Report Server FileShare'
--this section updates the password;
update sub
set extensionsettings =
left(convert( varchar(max) , sub.ExtensionSettings ) ,patindex('%<Name>PASSWORD</Name><Value>%', ExtensionSettings)
+ len( '<Name>PASSWORD</Name><Value>' ) - 1 )
+ 'enter password hash here'
+
right( convert( varchar(max) , sub.ExtensionSettings ) , (len( convert( varchar(max) , sub.ExtensionSettings) )
) - patindex('%</Value></ParameterValue><ParameterValue><Name>RENDER_FORMAT</Name>%',sub.ExtensionSettings) + 1 )
FROM dbo.Subscriptions AS sub
where sub.DeliveryExtension = 'Report Server FileShare'
June 10, 2014 at 2:09 pm
Thanks! That will save us some coding time for sure! And also the headache of dealing with failed subscriptions every few months!
June 10, 2014 at 2:56 pm
Glad to help!
December 4, 2014 at 3:18 pm
Update: the above code works beautifully with standard subscriptions, but is not robust enough to handle data-driven subscriptions.
Our company had never used data-driven subscriptions until the past few weeks, and the above code will need to be revised to take into account both regular and data driven, otherwise your data driven subscriptions will fail, and cause sundry errors in your RS.
~wz
December 4, 2014 at 9:36 pm
This code works well- it's also cleaner, since I've now been learning better how to deal with XML.
SELECT
SubscriptionID
,CAST(ExtensionSettings AS XML) ExtensionSettings
INTO #Subscriptions
FROM dbo.Subscriptions
WHERE DeliveryExtension = 'Report Server FileShare'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[4] with ("USERNAME-GOES-HERE")')
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[5] with ("PASSWORD-GOES-HERE")')
UPDATE s
SET s.extensionsettings = cast ( T.extensionsettings AS varchar(MAX) )
FROM dbo.Subscriptions s
join #Subscriptions t
ON s.SubscriptionID = t.SubscriptionID
January 5, 2015 at 2:48 pm
This is so timely for me and so necessary. I'd wondered what everyone was doing to address this -- probably as was first posited, creating a subscription account with non-expiring password?
Just a few qs: in your xml solution, where your dummy text is, you meant "enter username's hash value", not the plain text username, and the same for the password, right?
Also, I had been entering a single user's credentials for all the subscriptions, but upon inspecting the XML I find the hash values are all different. And this is the account I want to now use programmatically through this method. So now when I inspect the XML to get the hash values, I have a variety of values to choose from. Any one of them will work?
And after applying your solution, all the hash values are the same? Is that correct? And no issues with that?
January 5, 2015 at 3:05 pm
Hey Grasshopper,
Thanks for asking. Yes, you are correct- you'll enter the hashed value for both the UN and PW, not the 'human legible' versions :-).
At our shop, we use a non-expiring service account whose UN and PW both work...I simply don't want to tell anyone what that UN and PW are. So the first thing I did was create a subscription via the front-end using that UN & PW.
Then I queried the table and got the UN hash and PW hash, which I use as my 'master'; I force all fileshare subs to assume that UN & PW.
When I run my update, it's against all subs where DeliveryExtension = 'report server fileshare', and so all of them end up with the exact same values for UN and the exact same values for PW. It works perfectly for our case because that particular service account has permission to read/write/modify in the target fileshare folder structures.
We have had zero issues, and this quick update runs on a daily agent job during non-peak hours at night. I'm betting you can do pretty much the same thing. You can test by setting up a sub with a bogus UN & PW (that you know won't work), then run the update and see if that sub succeeds after the update.
wz
January 6, 2015 at 4:54 pm
Any hints, tips or code that will handle both flavors of reports?
January 6, 2015 at 9:04 pm
Which two flavors do you mean? (Email delivery extension doesn't require credentials)
~wz
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply