Report Builder 3.0 - Dataset Credentials Do Not Work

Question

I am using SSRS 2008 R2 and Report Builder 3.0. I set up a shared data source via the browser (http://myserver/reports/) wherein the "credentials are stored securely in the report server" to use with multiple reports. Testing the connection via the browser works.

Now, I fire up Report Builder 3.0. I start a new report and "Add Data Source". I select the "Use a shared connection", browse to the server, and select the above data source. Click "Test Connection" and all is well.

I then try to "Add Dataset". In the dialog, I select "Use a dataset embedded in my report", I select my data source added above, type in the SQL string and click OK. The "Enter Data Source Credentials" appears. No matter what permutation of valid domain (like the domain admin account and others) or SQL Server (like the 'sa' account) credentials plus checkboxes I use, I get an "Unable to connect to data source".

Security settings on the shared data source allow BUILTIN\administrators and domain\administrator all roles. The data source does work, as some legacy migrated reports run fine. Report Builder says the data source connects okay. But, I cannot create new reports, nor modify existing ones.

Any suggestions?

Answer 1

After much gnashing and wailing seems like Report Builder uses information in definitions in Report Server to then run queries locally by itself, and not through Reporting Services.

My mistaken belief was that when I did something like Refresh Fields when setting up a Dataset, Report Builder was going through Report Services to get the relevant output and/or metadata. This seems to not be the case.

I had defined the connection string of the shared data source as Data Source=(local);Initial Catalog=<database name>. Now, this works when you are in Reporting Services' browser interface, but Report Builder seems to read the connection string and use it for itself. Reporting Services on my local development machine does not have any of the setup on the target server running these reports, so anything I would use will fail.

Changing the connection string to Data Source=<server name>;Initial Catalog=<database name>, instead of localhost, allowed Report Builder to work properly. (RB still popped up "Enter Data Source Credentials". I used my Windows credentials, which is a valid login on the target server.)