1

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://docs.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 ?

0

I found this post while researching the same error message.

I am using 'SQL Server Data Tools for Visual Studio 2017’ My database is on SQL 2017, Enterprise.

The problem turned out to be that the credentials switched to "Do not use credentials" After I set it to "Use Windows Authentication" when I created the data source.

I changed it back (below) and saved, problem solved.

Data Sources > Rclick > Data Source Properties

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.