ERROR:
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.
SETUP:
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://learn.microsoft.com/en-us/sql/reporting-services/install-windows/configure-the-unattended-execution-account-ssrs-configuration-manager?view=sql-server-ver15
OBSERVATIONS:
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
WORKAROUND:
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 ?
