SphereGen

SphereGen Logo

Update Existing Windows File Share Subscriptions to Use Common File Share Account

Microsoft has introduced a new feature called Use file share account in Microsoft SQL Server Reporting services starting with SQL Server 2016. This new feature gives the flexibility to use a common file share account for windows file share subscriptions. To enable this new feature in the SSRS file share subscription, first, you need to configure the file share account using the Reporting Services Configuration Manager tool. Search for Reporting Services Configuration Manager in report server machine and connect to the report server instance name.


In the navigation panel on the left, select the Subscription Settings option and checked to specify a file share account option. You need to specify a valid windows user name and password there.


How to Update existing file share subscriptions to use common file share account

If the number of file share subscriptions is less, you can open and modify subscriptions manually by setting the Use file share account option.


If number of file share subscriptions are large, you can run below T-SQL Script on ReportServer database that will set Use file share account option for all file share subscriptions.
/*T-Sql Script to update all FileShare Subscriptions to use common FileShare Account*/

Declare @SubscriptionID nvarchar(1000),@ExtensionSettings Nvarchar(MAX),@XMLDATA XML;

declare

cur_FIXED_Subscriptions CURSOR for Select SubscriptionID,[ExtensionSettings]

from dbo.Subscriptions

where DeliveryExtension=’Report Server FileShare’;



Open cur_FIXED_Subscriptions

Fetch Next from cur_FIXED_Subscriptions into @SubscriptionID,@ExtensionSettings

While @@FETCH_STATUS=0

Begin

Set @XMLDATA=CAST(@ExtensionSettings AS XML)

—Delete Nodes related to file subscription username and password

Set @XMLDATA.modify(‘delete /ParameterValues/ParameterValue[Name=”USERNAME”]’ )

Set @XMLDATA.modify(‘delete /ParameterValues/ParameterValue[Name=”PASSWORD”]’ )

—Delete Nodes related to file subscription DEFAULTCREDENTIALS (if any) related to fileshare common account

Set @XMLDATA.modify(‘delete /ParameterValues/ParameterValue[Name=”DEFAULTCREDENTIALS”]’ )

—Insert new node DEFAULTCREDENTIALS=True to use common file share account

Set @XMLDATA.modify(‘insert DEFAULTCREDENTIALSTrue into (/ParameterValues)[1]’ )

—Update OLD Render format to NEW Render format

Update Subscriptions

Set [ExtensionSettings]=cast(@XMLDATA as nvarchar(max))

where Convert(nvarchar(1000),SubscriptionID)=@SubscriptionID

Fetch Next from cur_FIXED_Subscriptions into @SubscriptionID,@ExtensionSettings

End

Close cur_FIXED_Subscriptions

DeAllocate cur_FIXED_Subscriptions

About SphereGen

SphereGen logo on white background

SphereGen is a unique solutions provider that specializes in cloud-based applications, Intelligent Automation, and Extended Reality (AR/VR/MR). We offer full-stack custom application development to help customers employ innovative technology to solve business problems.

Learn more about what we do in Application Development: https://www.spheregen.com/application-development

microsoft partner badge
uipath silver partner badge