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 <ParameterValue><Name>DEFAULTCREDENTIALS</Name><Value>True</Value></ParameterValue> 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
