Search code examples
ssasdatasourcekerberosreportbuilder3.0

Create data source in Report Builder 3.0 to a data cube using "current windows user" credentials

I have Report Builder 3.0 installed on my local PC. I am creating a new report and adding an embedded data source to a SQL Server Analysis Services database.

When I build the connection string of the data source, choosing the server name and the database name, I click 'Test Connection' and receive a message saying "Test connection succeeded". So far, so good.

success!

I close the connection properties with the OK button, and on the Data Source Properties window I click the 'Test Connection' button. This time I get an error saying "The connection either timed out or was lost".

fail!

If I ignore the error then I can successfully add a dataset to my report and add data from the dataset in to the report design, but when I try to run the report (on my local PC) I again get an error connecting to the data source.

My best guess is that the connection that succeeded is running under my credentials, whereas the connection that fails is running as some other credential and so needs to use Kerberos delegation to pass my credentials along, but that is only a guess and even if I am correct I am at a loss to understand how to fix the issue - I don't know what other credentials may be being used and I have already set SPN's for the Analysis Services service account.

Solution

  • So it turns out that it was a Kerberos issue as I suspected, and I was also correct that Report Builder was testing the connection using some process running under another authentication context.

    It turns out that when setting up Report Builder (and I had forgotten it) that you specify a default SSRS Report Server (see screenshot). It must be that when testing data sources or running reports that it connects to that default Report Server and does the work from there - I was assuming that everything was running locally!

    Eureka!

    Once I figured that out it was just a case of finding a good guide on how to configure SSRS for Kerberos and everything started working. In my case the only bit that I hadn't already done was to add the <RSWindowsNegotiate/> setting to the AuthenticationTypes in the rsreportserver.config file