<div>
Just wondering if transfering SQL DBs to SSD could help.
</div>


<div>
All the Databases are on SSDs already (both the dev machines and AWS are on SSDs) - the rest of the performance is very fast it is just this authorisation step that is slow.
</div>


<div>
If you browse to the Data Source in the SSRS Web Portal and click Test Connection, does it still take 6 seconds before it comes back showing Connected successfully?
</div>


<div>
Have you tried to use a SQL user account in the Data Source or a Windows user account? <br />
<br />
Also Can you try to browse the report manager on the SQL DB server and see if the issue persists.
</div>


<div>
Hi Amir<br><br>The Data Sources all use a SQL user account, browsing on the server produces the same delay as browsing on a client.
</div>


<div>
So, let's troubleshoot SQL Server performance problems using the SSMS Performance Dashboard. <br />
Select the server in SSMS from Object Explorer, and from the right-click context menu choose Reports &gt;&nbsp;Standard Reports and open the Performance Dashboard- then please click on the hyperlinks to navigate to additional report for further drill through and see if there is any present performance killers.
</div>


<div>
We can also fire up the Activity Monitor from the same right-click context menu, this monitor provides an overview of the active queries among other useful information about SQL Server processes and how these processes affect the current instance of SQL Server. I hope you can find the cause of the delay.
</div>


<div>
Additionally, you can use SQL Server Extended Events from SSMS-&gt; Management-&gt; Extended Events, &nbsp;It is a very useful tool to see in real-time what SQL queries are being executed against your database and diagnose the issues.
</div>


Director/SQL Developer/BI Manager

Paul Donson

<div>
Thanks Amir. Firstly, I have looked at the Performance Dashboard - the processor for SQL is at 0% - interestingly when I open up SSRS Report Manager, the CPU stays at 0%.<br>Activity Monitor shows the same, 0% CPU - the only thing that registers is Batch Requests/Sec which goes up to 9 when I fire up Report Manager.
</div>


<div>
Thanks Andrei. I have checked the memory usage and when I log in to SSRS the used RAM goes up from 2821 to 2824 MB. I have changed the maximum RAM in SQL to be 2048MB (the virtual server has 8GB). This hasn't made any difference to the log in speed.
</div>


<div>
It coukd be the problem is that your application pool is set to shutdown after a certain time limit.<br />
<br />
Go to IIS, under application pools, right click on the one being used by Reporting Services and go to properties. &nbsp;On the performance tab, uncheck &quot;Shutdown worker processes after being idle for (time in minutes):&quot; &nbsp;<br />
You might want to also uncheck the boxes for recycling the worker processes in the Recycling tab; recycling the worker process will also make the next open of the page take longer. &nbsp;You could set this up to only recycle on the weekend (based on the number of minutes), or whenever is more acceptable.
</div>


<div>
Oops! please ignore my previous comment, the above comment only applies if you are running side-by-side deployments of older and newer versions of Reporting Services, which you are not.
</div>


<div>
Thanks Amir, I was a bit puzzled about the IIS comment as I'm running 2019!
</div>


<div>
Paul, have you tried other browsers, such as Edge, Firefox? (just for testing)<br />
try browing with the IP address of the SSRS server and see if it make any difference.
</div>


<div>
Hi Amir, yes I have tried Edge, Chrome, Firefox and IE (even Safari on an iPad!) and the same happens in all of them.<br><br>Using the IP Address makes no difference.
</div>


<div>
Hi Paul, I am at production site, we are running &nbsp;SSRS 2017 with no lateny issues- I'm checking the ssrs sleep time on our server, it's showing 720. could you please check it on your server and let me know what number is set for you.<br />
<br />
You could modify the setting in the rsreportserver.config file at <br />
<br />
C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\Report Server\<br />
<br />
&lt;RecycleTime&gt;720&lt;/RecycleT<wbr />ime&gt;<br />
<br />
Let me know how you get on.
</div>


<div>
Thanks Amir, I have explored this before. Even when awake SSRS still has the same latency with this api. My recycle time is set to 1440.<br><br>I suspect that it is because the server is not on a domain and it is timing out while looking for one (I use AzureAD for user authentication but Windows 2019 Server cannot be joined to it).<br><br>An interesting point is that if the server has multiple active network connections then the delay increases proportionately as though it is searching for something (like a domain controller). I have replicated the problem every time by creating a windows server 2019 instance (on AWS or Hyper-V) - not joining a domain and installing SQL 2019 + SSRS. I have the problem on every machine I have created. I did create on installation on a Work PC that was joined to a domain which then didn't have this issue but being on a domain isn't an option to me.<br><br><br><br><br><br><br><br><br><br>
</div>


<div>
Cheers Paul- I'm thinking the same that it is because your ssrs server isn't on a domain and not having a DNS might cause the latency- but usually it shouldn't be a delay if you browse by server ip address which you did and still no joy. <br />
Another setting I was going to ask you to disable is in network connection properties of your server -&gt; Advanced Tab -&gt; Receive Side Scaling.<br />
Alternative approach could be to create a dummy report that runs every 10mins, this keeps the connection to the database server active. another approach could be to schedule a job that will handle a report call periodically ( for example every morning before everyone rushes to the office). <br />
I know the above are not proper solutions but as you mentioned probably the main cause of this issue is that your server is not on a domain.
</div>


<div>
Or report caching in SSRS portal might help.<br><br><a href="https://filedb.experts-exchange.com/incoming/2021/06_w26/1563958/Untitled.png" rel="ugc"><img src="https://filedb.experts-exchange.com/incoming/2021/06_w26/1563958/Untitled.png" class="fr-fic fr-dib"></a>
</div>


Untitled.png

<div>
OK, I have tried disabling Receive Side Scaling which didn't have any effect. I wouldn't have thought caching would make any difference as it is the portal authorisation that is the issue, <a href="http://localhost/reportserver" rel="ugc">http://localhost/reportserver</a> has instant access and runs the reports instantly too. I'm guessing ReportServer doesn't use <a href="http://localhost/reports/api/v2.0/me" rel="ugc">http://localhost/reports/api/v2.0/me</a><br><br>I have tried looking at the code of the "me" API at <a href="https://app.swaggerhub.com/apis/microsoft-rs/SSRS/2.0#/Me/GetUserDetails" rel="ugc">https://app.swaggerhub.com/apis/microsoft-rs/SSRS/2.0#/Me/GetUserDetails</a> (line 4071) but it doesn't mean an awful lot to me...
</div>


<div>
I've looked at the code line 4071 I have no idea either.
</div>


<div>
I don't suppose we can spoof a Domain Controller for it? Networking isn't my field so not sure where to start. I have thought about creating a second AWS instance (possibly a free one) to act as a Domain Controller and get it to sync with Azure AD - it seems like overkill for my needs but might be an interesting project (with lots of YouTube videos!).<br><br>Do you have any other ideas?<br><br>
</div>


<div>
Good idea Amir, I will try that. At least we will know where the problem lies even if we don't have a solution...
</div>


<div>
Brilliant. &nbsp;Let us know how you get on.
</div>


<div>
OK, so I set up a virtual domain controller and added my dev machine to the domain and the 6 second delay disappeared. So it at least proves the theory but I have no idea what to do about it... I don't want a domain controller as AzureAD does everything I need (apart from this!)<br><br>Any ideas gratefully received...<br><br>Thanks.<br><br>
</div>


<div>
Hi Paul,<br>That is great- At least we identified the issue. So you need to consider having a domain controller in your network to be able to resolve the 6sec delay but you said you don't want a DC!?<br><br>If you want I can send you instructions on how to deploy a domain controller in Azure?<br><br>But before doing that, please be aware you probably need to join the client PCs in your network to the domain- also making backup of their data, documents etc and copy them over into their new profiles which is a bit time consuming depend on your organisation size.<br><br>Regards<br>Amir<br><br>
</div>


<div>
Thanks Amir. Does an Azure DC need an on-premise DC to work? Also, I only subscribe to Microsoft 365 Business Basic so not sure if it is included. We are a tiny business (but growing) of only 3 people so I can't really justify any extra cost.
</div>


<div>
You're welcome Paul- It could be either on-premise or Cloud whichever suits your work.<br>As far as I know Azure is included in Microsoft 365 Business Premium not O365 BB but you still have to pay for VM creating service which you need for DC. Probably easier and cheaper for you to implement an on-premise DC.
</div>


<div>
You're welcome Paul.<br />
Cheers<br />
Amir
</div>


<div>
<div class="content wysiwyg-content fr-view">
I have a Windows Server 2019 implementation running SQL Server 2019 and SSRS 2019, I do not have a domain controller so the Windows Server and SSRS use Workgroup security and SQL uses Mixed Mode.<br><br>Everything works fine and while it can be fiddly to set up SQL and SSRS generally work OK in a Workgroup environment.<br><br>The only issue I have is that when I first load the Report Manager (http://localhost/reports) there is a delay of 6 seconds (this is fairly consistent). I have traced this to the api <a href="http://localhost/reports/api/v2.0/me" rel="ugc">http://localhost/reports/api/v2.0/me</a> - Chrome reports the delay is in this and indeed if I paste this into a web browser it takes 6 seconds to respond. A point of note is that <a href="http://localhost/reportserver" rel="ugc">http://localhost/reportserver</a>&nbsp;does not have this issue.<br><br>I have tried this on various dev machines on Hyper-V on my laptop and I now have a production AWS EC2 Server which has exactly the same issue.<br><br>I know 6 seconds isn't a massive issue in the great scheme of things but I have got kind of obsessed with it...
</div>
</div>



I have a Windows Server 2019 implementation running SQL Server 2019 and SSRS 2019, I do not have a domain controller so the Windows Server and SSRS use Workgroup security and SQL uses Mixed Mode.
Everything works fine and while it can be fiddly to set up SQL and SSRS generally work OK in a Workgroup environment.
The only issue I have is that when I first load the Report Manager (http://localhost/reports) there is a delay of 6 seconds (this is fairly consistent). I have traced this to the api
http://localhost/reports/api/v2.0/me
- Chrome reports the delay is in this and indeed if I paste this into a web browser it takes 6 seconds to respond. A point of note is that
http://localhost/reportserver
 does not have this issue.
I have tried this on various dev machines on Hyper-V on my laptop and I now have a production AWS EC2 Server which has exactly the same issue.
I know 6 seconds isn't a massive issue in the great scheme of things but I have got kind of obsessed with it...

SQL Server Reporting Services is slow to authenticate via the reports/api/v2.0/me api

The Microsoft Server topic includes all of the legacy versions of the operating system, including the Windows NT 3.1, NT 3.5, NT 4.0 and Windows 2000 and Windows Home Server versions.

Microsoft Server OS

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.