When editing a report in report builder, opening the query editor gives the following error
You have specified integrated security or credentials in the connection string for the data source, but the data source is configured to use a different credential type. To use the values in the connection string, you must configure the unattended report processing account for the report server.
1. SQL server 14.0.2027.2 (2017)
2. SSRS 14.0.600 .1274 (2017) configured with an unattended service account
3. Reporting portal is accessed through active directory login
4. The report uses a shared datasource with this connection string
Server=tcp:xxxxx;Database=yyyy;Integrated Security=true; Encrypt=SSPI;Connection Timeout=30;MultipleActiveResultSets=True; Persist Security Info=False;App=SSRS;
5.The Credentials are set to Without Any Credentials
6. Setup as documented by MSFT https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/configure-the-unattended-execution-account-ssrs-configuration-manager?view=sql-server-ver15
1. Viewing reports and subscriptions work as expected
2. If i set credentials to As the user viewing the report, this fixes the issue but breaks subscriptions as they need either stored credentials or no credentials
3. In report builder, doing a Test Connection on the data source, returns Connection created successfully
1. In report builder, changing the datasource from Use a shared connection or report model to Use a connection embedded in my report with the exact same connection string as above allows the query editor to open. However i have to change it back to the using a shared connection after I make the required edits.
The workaround is not ideal for our user base and I would like to fix the underlying issue. Has any one experienced this issue before ?