SSRS - Slow SQL Connection Opening with ActiveDirectory User
Hi Everybody TL;DR: We are facing a problem with very slow SqlConnections (~20sec per connection) from a specific ReportServer to a Microsoft SQL Server and an Analysis Service Instance. Details see below. The current setup: SQL, SSAS and SSRS instances all installed on the same (Windows) server. There are two datasources configured on our SSRS (one connecting to SQL and one connecting to SSAS). The login-credentials for both datasources are configured with the same AD-user. Our problem: The SSRS-Reports are slow to load (it takes up to several minutes to render the report). We have viewed the log file and could see, that the sqlConnection opening takes up to 20 seconds. And that for each connection! On other ReportServers we have installed, it takes only a few milliseconds. It's not only the first call after some standby, but also every subsequent call. We have also made some performance tests for the specific SQL and MDX queries. The queries themselves are fast. Also the connection to the DB via OLEDB-driver (with the same AD-User) is instantaneous. What we have already tried: - During our analysis, we found the SPNs weren't registered. This has since been fixed: still the same poor performance. - We changed the credentials of the SQL-datasource on the ReportServer to a SQL-user: the test connection was now instantaneous. - But since we need an AD-user for the SSAS-datasource, we tried a totally new AD-user and used its credentials for the datasources: still the same poor perfomance. The next step that comes to mind, would be to completely remove and re-install SSRS, but we'd like to avoid this, if it turns out to not improve the situation at all. Are there any other steps that come to mind, we should/could try before a re-install? Thanks in advance for your help, Toby
Creating an expression for the connection string
How do I create an expression for the snowflake connection string? Since I need to pass the username dynamically as a parameter to the connection string based on the user logged into the SSRS server, I need to convert the connection string to an expression. I need some suggestions on how to convert the snowflake connection string to an expression.Connecting SSRS with snowflake as user viewing the report
As of now I am using the SSRS reports by connecting to my snowflake account by specifying the credentials or by using a shared connection. But now we should be able to view the reports with respect to each user by using the user specific snowflake account. So whenever a user logs in to the ssrs report, In the backed the queries should be fired from the current user snowflake account insted of using the shared connection or creds provided . Basically I want to use the feature shown in the image Need some help in setting the connection this way.
