Search code examples
sql-servervisual-studioreporting-serviceswindows-authenticationsql-server-2019

SSRS - Unable to connect to datasource

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

Solution

  • 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.