SSRS - Unable to connect to datasource

1
votes

Context: I have an installation of SQL Server 2019 installed on my local workstation and within an SSRS Report Project I'm trying to create a dataset that connects to this SQL Server datasource. I keep raising this error

"Unable to connect to datasource 'XXXX'"

EDIT: I realized clicking through the details there's a more descriptive message here: 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.

enter image description here

I'm thought I was using Windows Authentication. This is a very strange error because prior to this step when I create the datasource and test the connection the test succeeds. How is it that the datasource connection properties dialog recognizes the server, database name and successfully authenticates my windows creds in the connection test but suddenly the dataset dialog cant connect...?

How have I tried to fix this: I thought this could be an issue with the ODBC driver I'm using so I've installed both Microsoft ODBC Driver 13.1 & 17 for SQL Server which is not resolving the issue.

Question: Does anyone know why this is happening and how to fix it?

enter image description here

2
sql-servervisual-studioreporting-serviceswindows-authenticationsql-server-2019
Can you click the "details" button and show that also? – Wouter
"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." – emalcolmb
AH I just realized in the datasource looks like I hadnt actually explicitly selected "Use Windows Authentication (integration security)" I did now and the dataset menu is able to use it. Thanks! – emalcolmb

2 Answers

0
votes

Answer: Turns out under the "Shared Data Source Properties" dialog box, within the "Credentials" tab, the radio dial for "Use Windows Authentication (integrated security)" needed to be selected.

I assume the reason the connection test succeeded was because SSRS guessed at the correct authentication method, but it still needs to be explicitly selected for the dataset to connect properly.

0
votes

Step 1:- Click on Data Source Select Add Data Source It show two options in Left corner of Data source property window called General and Credential. Select Credential. Note (if Use Windows Authentication(Integrated security) is already selected also DO not Press Ok Follow Below Procedure steps. First choose Do not use Credentials(Radio Button) and Do not press Ok. then select Use Windows Authentication(Integrated security) Press OK. Now Data source1 is Created.

Step 2:-Click Right Button on Data Source which is New created -> Select (Data Source Property) Click Embedded Connection Radio Button Select Type as: Microsoft SQL Serve For Connection String Click on BUILD/EDIT Button In Connection Property Enter your Server Name (Sql Server Name) Select or enter name select Database name Click Test Connection Click Ok Once this is completed.

Step 3:-Right click on Date Source Which you are Created Click on ADD DATASET In Data Set Properties Enter Dataset Name Select Data Source which you created. Enter Query or Select Query designer and Click Ok.

Note Please follow Step 1 Properly this work only for Windows Authentication. If you Have any Doubts Mail Me My Mail ID:- [email protected].