Cannot create a connection to data source Error rsErrorOpeningConnection in SSRS

Question

I m working on integrating a report into a browser  and I get this error   An error has occurred during report processing   rsProcessingAborted  Cannot create a connection to data source  dsFederatedSample SurveyLevel STG    rsErrorOpeningConnection  For more information about this error navigate to the report server on the local server machine  or enable remote errors     Does this have to do with SQL vs Windows authentication

User · Answer

I had the exact same issue  The cause could be different but in my case  after trying several different things like changing the connection string on the Data Source setup  I found that this was the infamous  double hop  issue  more info here   To solve the problem  the following two options are available  as per one of the responses from the hyperlink       Change the Report Server service to run under a domain user account  and register a SPN for the account       Map Built-in accounts HTTP SPN to a Host SPN     Using option 1  you need to select  Windows  credentials instead of database credentials to overcome the double hop that happens while authentication

User · Answer

I had a similar problem  and being the newbie that I am it took me a while to figure out but I learned the user must have a login in SSMS   I created the logins with the following parameters    Under Server Roles - check sysadmin Under User Mapping - I selected the database and the report server   For each I checked datareader and datawriter Under Securables - I checked anything that would allow the user to connect to the database and view anything I also found that one of the existing logins had denydatareader and denydatawriter checked   Once I removed these it worked    I m not saying this is the best way to do it  just what worked for me   Hope this helps

User · Answer

I had the same issue  Cannot create a connection to data source   Login failed for user    on Windows 8 1  SQL Server 2014 Developer Edition and Visual Studio 2013 Pro  All solutions offered above by other Stackoverflow Community members did not work for me   So  I did the next steps  running all Windows applications as Administrator       VS2013 SSRS  I converted my Data Source to Shared Data Source   rds  with Windows Authentication  Integrated Security  on the Right Pane  Solution Explorer   Original  non-shared  Data Source  on the Left Pane  Report Data   got  Don t Use Credentials   On the Project Properties  I set for  Deployment   Overwrite DataSources  to  True  and redeployed the Project      After that  I could run my report without further requirements to enter Credentials  All Shared DataSources were deployed in a separate Directory  DataSources

User · Answer

More information will be useful    When I was faced with the same error message all I had to do was to correctly configure the credentials page of the DataSource I am using Report Builder 3   if you chose the default  the report would work fine in Report Builder but would fail on the Report Server   You may review more details of this fix here  https   hodentekmsss blogspot com 2017 05 fix-for-rserroropeningconnection-in html

User · Answer

First thing I would try is to get a bit more information on the error - that s a pretty generic message   You could enable remote errors as per the error message and replicate the error for more information   Enable remote errors   Or check the Report Server error logs to see what error was logged    programfiles  Microsoft SQL Server  lt SQL Server Instance gt  Reporting Services LogFiles    The next step would be to connect as the Data Source user to the database  run any code stored procedures that the report is using with the same parameters you re using when running the report  and see if any errors occur

User · Answer

In my case I had in one report many different datasets to DB and Analysis Services Cube  Looks like that datasets blocked each other and generated such error  For me helped option  Use single transaction when processing the queries  in the CUBE datasource properties

User · Answer

The issue is because your data source is not setup properly  to do that please verify your data source connection  in order to do that first navigate to Report Service Configuration Manager through  clicking on the start -  Start All -  Microsoft SQL Server - Configuration Tool -     Report Service Configuration Manager     The open Report Manager URL and then navigate to the Data Source folder  see in the picture below   Then Create a Data Source or configure the one that is already there by right click on your database source and select  Manage  as is shown below   Now on the properties tab  on your left menu  fill out the data source with your connection string and username and password  after that click on test connection  and if the connection was successful  then click  Apply    Navigate to the folder that contains your report in this case  SurveyLevelReport     And Finally set your Report to the Data Source that you set up previously  and click Apply

User · Answer

In my case  this was due to using Integrated Windows Authentication in my data sources while developing reports locally  however once they made it to the report manager  the authentication was broke because the site wasn t properly passing along my credentials    The simple fix is to hardcode a username password into your datasource  The harder fix is to properly impersonate delegate your windows credentials through the report manager  to the underlying datasource

User · Answer

if you use null values in your stored procedure  you will need to set the parameters to accept null values  That worked for me

User · Answer

In SQL Server 2008 in addition to the above two options you have a third option to make this setting through SQL Server Management Studio   1 Start Management Studio and connect to Report Server Instance  make sure you select  Reporting Services  server type    2 Right click on the ReportServer and Select Properties  3 Click Advanced  4 In EnableRemoteErrors  select True   5 Click OK

[reporting-services] Cannot create a connection to data source Error (rsErrorOpeningConnection) in SSRS

Examples related to reporting-services

Examples related to ssrs-2008

Examples related to database-connection