January 7, 2015 at 6:56 am
wiperzeus - apologies for muddying the water.
In your 12/4/2014 10:18:47 PM post you commented that mods to earlier code would need to "take into account both regular and data driven" subscriptions and it wasn't clear to me if the revised code in your 12/5/2014 4:36:42 AM post would work for both...
January 7, 2015 at 8:22 am
Ah, I see...yes, it works for regular and data-driven subscriptions.
~wz
June 18, 2015 at 8:37 am
UPDATE:
I had counted on the XML elements in the ExtensionSettings field to always be in the same order. Today I discovered that this isn't always the case. Sometimes the UN and PW are the 4th and 5th elements...sometimes they're the 6th and 7th. I don't know how or what determines the order of these, but I revised my script so as to get them no matter what order they're in. It's a bit lengthy, but it runs super quick and accounts for any order of elements;
---------------------
--dump subs into temp table because need to perform a few separate updates one at a time. then update actual live table all at once.
SELECT
SubscriptionID
,CAST(ExtensionSettings AS XML) ExtensionSettings
INTO #Subscriptions
FROM dbo.Subscriptions s
WHERE DeliveryExtension = 'Report Server FileShare'
-------username;
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[1] with ("**YourUserNameHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[1]', 'varchar(50)') = 'USERNAME'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[2] with ("**YourUserNameHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[2]', 'varchar(50)') = 'USERNAME'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[3] with ("**YourUserNameHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[3]', 'varchar(50)') = 'USERNAME'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[4] with ("**YourUserNameHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[4]', 'varchar(50)') = 'USERNAME'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[5] with ("**YourUserNameHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[5]', 'varchar(50)') = 'USERNAME'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[6] with ("**YourUserNameHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[6]', 'varchar(50)') = 'USERNAME'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[7] with ("**YourUserNameHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[7]', 'varchar(50)') = 'USERNAME'
----password;
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[1] with ("**YourPasswordHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[1]', 'varchar(50)') = 'PASSWORD'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[2] with ("**YourPasswordHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[2]', 'varchar(50)') = 'PASSWORD'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[3] with ("**YourPasswordHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[3]', 'varchar(50)') = 'PASSWORD'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[4] with ("**YourPasswordHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[4]', 'varchar(50)') = 'PASSWORD'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[5] with ("**YourPasswordHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[5]', 'varchar(50)') = 'PASSWORD'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[6] with ("**YourPasswordHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[6]', 'varchar(50)') = 'PASSWORD'
UPDATE #subscriptions
SET extensionsettings.modify('replace value of(/ParameterValues/ParameterValue/Value/text())[7] with ("**YourPasswordHashGoesBetweenTheseDoubleQuotes**")')
where extensionsettings.value( '(/ParameterValues/ParameterValue/Name)[7]', 'varchar(50)') = 'PASSWORD'
--update actual table in one go;
UPDATE s
SET s.extensionsettings = cast ( T.extensionsettings AS varchar(MAX) )
FROM dbo.Subscriptions s
join #Subscriptions t
ON s.SubscriptionID = t.SubscriptionID
November 21, 2016 at 12:13 am
hi
i have updated the hash but getting this error (Invalid length for a Base-64 char array). Please let us how can we generate the hash passowrod for given text which we can update for subscription password .
i generated using
DECLARE @password NVARCHAR(25) = N'P@ssw0rd';
DECLARE @salt NVARCHAR(25);
DECLARE @hash VARBINARY(64);
EXEC [sp_SaltAndHashPassword] @password, @salt OUTPUT, @hash OUTPUT;
print @hash;
EXEC [sp_VerifySaltAndPassword] @password, @salt, @hash;
November 21, 2016 at 8:16 am
wrvishnu,
I don't know how to generate the actual hash other than using the SSRS front end once time, then looking up the hash that was generated. For our shop this worked fine because we use a non-expiring service account UN and PW, so I only had to enter it once through the front end, then query the table(s) directly to get the hash that the system generated, and use it in my scripts.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply