I'm trying to migrate reports over to a new SSRS 2019 server and one particular report gets called from an excel macro where it reads in the contents of a file located at a given filepath. I've tried the methods of defining the report path as described on the Microsoft site which is

http://server/ReportServer?/foldername/reportname¶metername= "filepath"

I've tried sever different ways to pass the file url in as a parameter but every time it comes up as an error >path of the item is not valid. The file exists on a network server and if I run the report manually from the report sever I can just pass in the network url as follows "\\\\\filename.txt" and it works as expected. When I try to run this by passing in the filepath into the url and running from a browser it tells my invalid path. Long story short, how do you pass in a network server filepath that is not local to the report server as a param?

We are encountering authentication issue when refreshing the report in SSRS 2019 web portal. Report Server is configured to run using **Virtual Service Account**. In the *RSReportServer.config* file, **RSWindowsNTLM** authentication type is used. The report is connecting to the data source (internal product) using *custom ADO.NET Data Provider* which supports integrated Windows Authentication. Data source is configured with **As the user viewing the report** credentials. Test connection works. When the user opens the report for the first time, report opens successfully. In our product log, we see that the connection was made using logged on domain user account who opened the report. When the user refreshes the report after some time (3 minutes), report fails to load due to authentication error. In our product log, we see that the connection was made using service account (machine account of the SSRS server) and authentication fails because the service (machine) account is not allowed to access our product. The same configuration works successfully in SSRS 2016. Not sure why the report in SSRS 2019 is connecting to data source using service account instead of logged on domain user account even though it's configured to use the user account viewing the report. I have experimented with **RSWindowsNegotiate** and **EnableAuthPersistence** set to **false** in *RSReportServer.config*. The issue still exists. Any help/guidance would be appreciated.

This is will be my first SSRS migration and please bear with me if some points don’t make any sense. I need to migrate the Reporting server but need to keep the same name so that the reporting URL don’t change. Sources Server A – Has the database and the reporting databases Server B only has the reporting server installed. Destination Server C- New SQL Server will be installed with all the databases Server D will have the reporting server installed. Below is a summary • Backup SSRS databases on source server • Backup Encryption Key on source server • Restore SSRS databases on target server • Restore Encryption Key on target server • Remove old server name from the Keys table on the target server My confusion lies with the 1) What if I want the new server (Server C and Server D) to have the same name of Server A and Server B hence my reporting Web Service URL and the Web Portal URL don’t change. http://ServerB/Reports http://ServerB/ReportServer My thought process was Rename Server A and Server B before migration and name Server C and Server D with the old name of Server A and Server B. After that the Web Service URL and the Web Portal URL will pick up the default name which will be the old one. Please correct me if I am wrong and is there any thing else that I need to be aware of which might break the reporting 2) After the migration do we need to move the rds files from the old server to the new server as well and since the names are the same so do we still need to modify the data sources?

I have a standard select query which returns a set of results :-

lang-SQL
SELECT
	c.CASEID,
	c.Event,
    c.ACTION,
	c.CYCLE,
	c.PROPERTYTYPE,
	c.COUNTRYCODE,
	c.STATUSCODE,
	c.CRITERIA,
	c.DATE,
	c.TITLE,
	c.DESCRIPTION
FROM CASES c

WHERE c.DATE = GETDATE() 
and  c.EVENT in (1, 2, 3, 4)

I'm trying to use some of the fields from each row of the query as parameters for a stored procedure which returns a single row of 4 columns :- Amount1, Amount 2, Cost, Currency. The stored procedure would run as :-

lang-SQL
exec AmountsCalc c.CASEID, 105, 96, c.ACTION, c.EVENT, c.CYCLE

This would return :- | Amount1 | Amount2 | Amount3 |Cost|Currency| |:---- |:---- | :-----| :-----| :-----| | 10.00| 20.00 | 30.00|40.00 |GBP | I'd like to combine the stored procedure result with each row from the original query where the c.CASEID is the link between each row of the results and SP, to use in an SSRS report. I have spent a lot of time looking for an answer and have come up with a solution, but it doesn't seem like an efficient solution and I was hoping there might be a better one but I'm at the limits of my experience. My solution is to have a dataset in SSRS with the original query, pass the caseid to a parameter and then have another dataset query that runs the stored procedure for each caseid in the parameter. Then use a lookup expression between the first dataset and the stored procedure dataset. As the query for the stored procedure is using almost an identical select query as the original, it seems like there might be a better way than running the query twice. The query I’m using to retrieve the data from the stored procedure is :-

lang-SQL
DECLARE @CASEFEESCURSOR CURSOR,
		@CASEID INT,
		@Event int,
		@Cycle smallint,
		@Action nvarchar(10)

DECLARE		@SPResults TABLE (
			AMOUNT1				decimal (11,2)  NULL, 
			AMOUNT2				decimal (11,2)  NULL,
			COST				decimal (11,2)  NULL,
			CURRENCY			nvarchar(3)		NUll
)


Create	table #TEMPCASEFEES (
			CASEID				int,
			AMOUNT1				decimal (11,2)  NULL, 
			AMOUNT2				decimal (11,2)  NULL,
			COST				decimal (11,2)  NULL,
			CURRENCY			nvarchar(3)		NUll
			)

SET @CASEFEESCURSOR = CURSOR FOR
--main query with minimal select fields to match main query in report 
			SELECT
				c.CASEID,
				c.Event,
				c.ACTION,
				c.CYCLE,
				c.CRITERIA,
		FROM CASES c
		WHERE c.DATE = GETDATE() 
		and  c.EVENT in (1, 2, 3, 4)
		and c.CASEID in @pCASEID --parameter from first dataset in ssrs report 
	ORDER BY c.CASEID;

OPEN @CASEFEESCURSOR;  

--- load first row           
FETCH NEXT FROM @CASEFEESCURSOR 
INTO  @CASEID, @Event, @Action, @Cycle, @CRITERIA

WHILE @@FETCH_STATUS = 0  
BEGIN  
        --- execute proc 
		INSERT INTO @SPResults
        EXEC AmountsCalc @CASEID, 105, 96, @Action, @Event, @Cycle

		INSERT INTO #TEMPCASEFEES (CASEID, Amount1, Amount2, Cost, Currency)
		SELECT @CASEID, Amount1, Amount2, Cost, Currency FROM @SPResults

		DELETE FROM @SPResults      
        
		--- get next row           
        FETCH NEXT FROM @CASEFEESCURSOR
        INTO @CASEID, @Event, @Action, @Cycle, @CRITERIA
END;  
CLOSE @CASEFEESCURSOR;  
DEALLOCATE @CASEFEESCURSOR;

select * from #TEMPCASEFEES order by CASEID

Any suggestions would be appreciated. I have looked at turning the called stored procedure into a function but I don't think I can as it calls another stored procedure. I also can't edit the stored procedure. I do have a working solution but would like to know if there is a better way to do this.

I have a SQL Server Availability Group for HA and a Distributed Availability Group for DR. I also have SQL Server Reporting Services running on all Availability Group nodes. For various historical reasons, we have connections between user databases in the Availability Groups, and the ReportServer database. Yes, I know, cross database queries are bad; suffice to say there is no appetite to change the line of business app that uses the ReportServer database directly. Our applications also rely on SSRS reports for generating customer-facing documents in real time, making SSRS a single point of failure if the ReportServer database was hosted on a single machine and that machine was down. This has been working perfectly in our on-premises setup where the two node AG exists on a single subnet. The Reporting Services services on each node connects to the ReportServer database via the AG listener. When a failover occurs, a SQL Server Alert fires a SQL Server Agent Job which restarts SSRS via a CmdExec step via a proxy from a local account that has zero access to the machine aside from the ability to stop and start the SSRS Service. We're moving our entire infrastructure into Azure (on Azure VMs since that is the only way to get FileStream support). This necessitates running our Availability Group SQL Servers on different IP subnets, mandating the use of MultiSubnetFailover=True in connection strings. First, the unanswerable question; why doesn't Microsoft provide a mechanism for SSRS to use MultiSubnetFailover=True in its connection to the ReportServer database? That would solve the problem entirely. I'm assuming SQL Server Reporting Services 2022 still doesn't have that capability (I *know* SSRS 2019 doesn't have it). Since Microsoft doesn't support MultiSubnetFailover=True for SSRS ReportServer database connectivity, I need to reconfigure the SQL Server Reporting Service service on each node when a failover occurs, so that I can point each SSRS service at the then-primary node. This is trivial to accomplish via the command line, using the rsconfig.exe command:

C:\Program Files\...\rsconfig.exe -c -i SSRS -s  -d ReportServer -a Windows

The account running the above command is [*required*](https://learn.microsoft.com/en-us/sql/reporting-services/tools/rsconfig-utility-ssrs?view=sql-server-ver16#permissions) to be a member of the local Administrators group. For testing, I added the proxy account to the local Administrators group on our dev server, however the rsconfig command above still fails because there is a security policy that prevents members of the local Administrator group logging in to the machine over the network. When I manually run the command out of a cmd shell on the VM, it works fine, however when the proxy runs the command it returns the following error: > system error: Logon failure: the user has not been granted the requested logon type at this computer. ('Access this computer from network')). The step failed. I can configure the server to allow the given user to access the computer from the network however that goes against our policy. If the account that runs RSConfig could be given the granular permissions it needs, instead of membership in the local Administrators group, that would also solve the problem, however I don't know the granular permissions it needs, and I'd bet rsconfig.exe checks it has membership in the local Administrators group at startup anyway. Does anyone have any smart ideas about how I should get SSRS to work in Azure considering the constraints: 1. MultiSubnetFailover=True is *required* for Distributed Availability Groups in Azure (see [here](https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-dnn-interoperability?view=azuresql#distributed-availability-group) and [here](https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-overview?view=azuresql#connectivity)) . 2. Members of the local "Administrators" group cannot log in over the network by policy. 3. The ReportServer database needs to be co-located with certain application databases in a high-availability configuration. If there was a way to modify the connection string stored in the DSN in the rsreportserver.config file, and encrypt it in a way that SSRS could decrypt it, that would solve the problem, but obviously the format of the DSN, and the encryption SSRS is using is a black box at this point.

Having issue with the SSRS Web Portal URL not loading. The Web Service URL loads (eventually) but not the Web Portal. I am upgrading from SSRS 2012 to 2019 and followed the steps found here. As a test I also created a blank ReportServer and ReportServerTempDB databases and that will not load either. I setup a SQL trace to see if I am hitting the database (DB engine and SSRS service are on same VM along with my testing) and it keeps running the same following commands over and over:

exec GetCurrentProductInfo 
exec GetAllConfigurationInfo 
exec GetAnnouncedKey @InstallationID=''

declare @p5 int
set @p5=NULL
exec AnnounceOrGetKey @MachineName=N'', @InstanceName=N'SSRS', @InstallationID=',@NumAnnouncedServices=@p5 output
select @p5

exec PollEventsForRSProcess @NumberOfEvents=4

And This Command:

declare @BatchID uniqueidentifier

set @BatchID = newid()

UPDATE [Notifications] WITH (TABLOCKX)
	SET [BatchID] = @BatchID,
	[ProcessStart] = GETUTCDATE(),
	[ProcessHeartbeat] = GETUTCDATE()
FROM (
	SELECT TOP 4  [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
	(ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] = t1.[NotificationID]

select top 4
		-- Notification data
		N.[NotificationID],
		N.[SubscriptionID],
		N.[ActivationID],
		N.[ReportID],
		N.[SnapShotDate],
		N.[DeliveryExtension],
		N.[ExtensionSettings],
		N.[Locale],
		N.[Parameters],
		N.[SubscriptionLastRunTime],
		N.[ProcessStart],
		N.[NotificationEntered],
		N.[Attempt],
		N.[IsDataDriven],
		SUSER_SNAME(Owner.[Sid]),
		Owner.[UserName],
		-- Report Data
		O.[Path],
		N.[ReportZone],
		O.[Type],
		SD.NtSecDescPrimary,
		N.[Version],
		Owner.[AuthType],
		SR.[SubscriptionResult]
	from 
		[Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID]
		inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID
		left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
		left outer join [SubscriptionResults] SR on N.[SubscriptionID] = SR.[SubscriptionID] AND CHECKSUM(convert(nvarchar(max),N.[ExtensionSettings])) = SR.[ExtensionSettingsHash]
	where 
		N.[BatchID] = @BatchID
ORDER BY [NotificationEntered]

Seems like a continuous loop. I eventually stopped the trace after a few dozen iterations through those same commands. Thinking it might be something in setup I temporarily added an execution account that matched an SA account in SQL but that did not help. My SSRS Service is running as the same account from the old 2012 service and lastly I deleted the encryption keys in hopes I could get something to show since I have all the data source passwords and there are only a few and a hand full of reports. Did verify SSRS Service account had correct permissions in SQL.

I have a customer consolidating and upgrading two different SQL instances into a new 2019 cluster. Both of those older instances have an SSRS deployment on them. 2019 doesn't allow multiple SSRS instances on the same server, so I was wondering if it is possible to merge two different SSRS databases. Or am I going to have to go the route of upgrading/migrating one instance and then export/import the reports and data source definitions from the second instance? If that is the answer, then is this powershell module the best way to do that and are there any gotchas that I need to be careful of? (ReportingServicesTools https://github.com/microsoft/ReportingServicesTools) . --- Late update to this question as the customer postponed the migration by a few months. Following on from the answer provided below, I ran the following commands to merge the reporting server instances. On source server create a folder to hold exported objects, e.g. C:\SSRS_Txfr The following command will start at the top level folder and recurse down the folder tree exporting most SSRS objects. Out-RsFolderContent -ReportServerUri 'http://:80/reportserver ' -RsFolder / -Destination "C:\SSRS_Txfr" -Recurse -Verbose On target server, create folder to hold exported objects and copy over from source server, again example used is C:\SSRS_Txfr Create a folder in SSRS to hold transferred reports to avoid accidental overwrite or objects not copied due to existing object with the same name New-RsFolder -ReportServerUri 'http:///reportserver ' -Path / -Name SSRS_Transfer -Verbose Then load the exported objects retaining folder structure Write-RsFolderContent -ReportServerUri 'http:///reportserver ' -Path "C:\SSRS_Txfr" -Destination /SSRS_Transfer -Recurse -Verbose I will note that the customer had a number of Models in their source SSRS server that the scripts would not transfer. The customers BI team picked those up as well as updates to the Data Sources post-transfer.

I have inherited a very weird anomaly in SSRS. A new SSRS instance was created using 2019 and it appears it is using the same ReportServer DB as previous 2016 instance. This may have been done to avoid porting thousands of reports and associated items. The 2016 instance seems to still be running, however, the account used to access the ReportingService db is no longer valid, thus any attempts to access a report using the 2016 url end with a 'cannot access the report server database.' When I access a report in 2019 I can render it in the report manager. When I invoke the same report, using the 2019 instance's url, through a wcf service call to ReportService2010.Render(), I get the same error as if I were accessing the services vis the 2016 instance. Alos, I know the 2016 version is trying to render the call made to 2019 because the log information about the render attempt and "can't connect to report server database" error only show up in 2016's error log. I have re-checked the iis log and see the that the wcf service is calling the 2019 server to request the render with 200 result (it is fire and forget so a 200 is always returned if the endpoint is accessible). It seems like the wcf is in fact calling the 2019 instance and requesting a report, however, the logging for that request is being done over at the 2016 instance of SSRS. Could this be something not well configured in the report sever database or is there anywhere in the database that could give me a better idea why the wrong instance is attempting to render the report?

On Windows Server 2019,installed SSRS 2019 and configured in report server. when I previewing the reports, all icons will be rendered in capital letters .In server side, any settings need to changed to make it work? Any help is appreciated.

I am working on migrating a number of reports from an SSRS 2012 install to a SSRS 2019 install. These are all pretty simple and straight forward reports, built in microsoft report builder. A number of the reports use an oracle data source, these reports are giving me a heck of a time. The following outlines the steps I have taken related to the oracle reports 1. I installed ssrs 2019, set it up per our team standards 2. Installed the oracle 19c administrator client 3. Installed the latest ODP.NET client (18.3) 4. Copied the tnsnames.ora file to both the admin client and the ODP.net client homes 5. Ensured the TNS_ADMIN environment variable was set 6. I made sure there was an entry in the rsreportserver.config file for oracle, which there is 7. Ensured I was able to resolve the oracle service name, by testing and making sure tnsping was able to resolve the name, and it was successful I am still getting the following error, when trying to view the report from the ssrs server, or when trying to test the data source connection in the report builder. > An error has occurred during report processing. (rsProcessingAborted) > An attempt has been made to use a data extension 'ORACLE' that is > either not registered for this report server or is not supported in > this edition of Reporting Services. (rsDataExtensionNotFound) I have run through all the troubleshooting steps that I can think of, and I am not making any progress.

SQL Server Reporting Services are no longer part of the installation media since SQL Server 2017. They are delivered as a separate Installer . The new Installation Wizard gives no option to install multiple (named) instances of SSRS 2017 on the same machine. Is that still possible?