I have Report Builder 3.0 deployed on my local machine pointing to a remote SQL Server 2008 R2 instance outside of my local domain. Within this local Report Builder 3.0 instance I am able to open up previously created rdl files from the remote SQL Server 2008 R2 instance that I am connecting to. However, if I try to create a new report on my local instance of Report Builder 3.0 and attempt to use the same data source as was used to create these existing rdl files, I receive the error: "Unable to connect to data source. The user credentials provided in the connection string do not have permission to connect to Reporting Services." So why can I open existing rdl files from this remote SQL Server 2008 R2 instance, but not create new ones even though I'm using the same data source as the existing rdl files?
The error "Unable to connect to data source. The user credentials provided in the connection string do not have permission to connect to Reporting Services" means the credential does not have permissions to connect to the report data source. In order to retrieve data from a remote database engine, the user credential must be authorized and authenticated.
In this case, when we open a previously created report, but doesn't preview it in the Report Builder, the report won't try to connect to the report data source. However, once we try to preview the previously created report, the report builder will try to use current user's credential to connect to the report data source. Based on your description, the current machine is outside of the local domain, the user credential may not be available. That is why the error occurred.
To work around the issue, we can set the credential for the shared datasource to be "Stored Credential". Once we set the credential to be stored credential, the shared datasource will use the stored credential instead of the user credential to access the data source.
If there is anything unclear, please feel free to ask.
Jin Chen - MSFT
The data source's security on the Report Server is set to "Credentials stored securely in the report server" and when I preview a previously created report in Report Builder 3.0 that uses this data source, the report opens correctly without any errors. It is when I attempt to create a new report using this same data source in Report Builder 3.0 that I am presented with the error message noted in my initial question. I don't understand why the data source can be accessed in one instance but not the other? So I think I have the data source set up correctly per your reply, am I missing something?
Here is the solution that made this work for me with a very similar problem:
1. Open your SQL Server Management Studio as an administrator. (This is very IMPORTANT! Right-click on the SQL Server Management Studio icon and then click "Run as Administrator")
2. Connect to Reporting Services choice
3. For Server Name field type in localhost or machine name if you are on a remote computer
4. After you are connected to the Reporting Server, right-click the localhost item in the object explorer window
5. Select SECURITY >> ROLES and right-click the Properties option for the Content Manager choice
6. Check the VIEW MODELS and MANAGE MODELS checkboxes and select OK
You just need to make sure that your login is set for CONTENT MANAGER permissions in SSRS for the folders you are deploying to. This is done from the ReportManager and the SiteSettings to add your login (if not already) and set it for the Content Manager Role. Then make sure your login is added to the folders you are deploying to. Or add your login to the home folder. That should cascade into subfolders automatically. You may need to have to Run Internet Explorer as an Administrator to make these selections visible.
I also had to set the domain user that I wanted to allow to do this as the "system user" role thru site settings. That role i checked the option 'execute report definitions' thru SSMS for the system user role so that they can preview reports from the report builder.