When running from Report Builder a user may see the error, The permissions granted to user BLACKBAUDHOST\xxx are insufficient for performing this operation. Depending on what Report Builder action is used, the error message may change. When adding or modifying a Dataset > Query designer, the user is prompted to sign in. Regardless of the credentials used, the following error is thrown, "Unable to connect to data source 'BBInfinity'. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)"
There are still some limitations to the use of Report Builder since it is geared towards being an Intranet solution, such as whilst you can edit and view reports via Report Builder, it will have problems Querying Datasets. The workaround in this case is to edit and save the report back to the application and then test from within the application any datasets, etc. For info the cause of this issue is that Report Builder is downloaded (In the case of opening this directly from SSRS) or ran from the clients local machine at their site/location, when trying to query the datasets, as these are not “local” to their location, (I.E You cannot simply hit our SQL servers from outside our network) it cannot make the connection and will endlessly prompt for credentials.
For our hosted clients, the only option is to not use Query to create reports. The problem is that Microsoft Report Builder tries to connect directly to the database server when validating the query statements as outlined in the bug. It’s a known issue and they have no plans to address it. However, our clients can use the Report Model Generator (found in the Analysis functional area) to create reports. The process is more complicated, and not as user friendly. They also can not directly edit the SQL; they need to build a model, then build reports off of that. Here’s a link to some of MS’s documentation http://technet.microsoft.com/en-us/library/dd220475.aspx
Steps to Duplicate
1. Click on Analysis 2. Click on Report Explorer 3. Choose a report under the Ad-hoc Query Reports folder and click Edit report. 4. After Microsoft SQL Server Report Builder launches > Enter credentials > Click Run > Error occurs