Cannot create a connection to data source

New Page

Question

CC-8522 avatar picture

Click to vote against 0 votes” 0 Click to vote against

CC-8522 requested • December 10, 2021 at 10:42 p.m. | Joyzhao-MSFT edited • December 15, 2021 at 7:18 am

SQL Server 2016 to 2019 now SSRS Unable to create a connection to data source

We upgraded from SQL Server 2016 to 2019, and our reports on the report server now show:

An error occurred while processing the report. (rsProcessingAborted) Unable to create a connection to data source ‘DataSource1’. (rsErrorOpeningConnection) A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server cannot be found or is unreachable. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Unable to open a connection to SQL Server) Network path not found

If I download the report and/or run the original from my box through Visual Studio, the report works.

Sql-Server-Reporting-Services

Comment

Comment Hide 0 Save

Comment

5 |1600 characters characters required remaining characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB in total.

Home / Microsoft / Dynamics GP / Resolving SQL Server Reporting Services “Unable to create a connection to data source” error

Resolving SQL Server Reporting Services “Unable to create a connection to data source” error

By Paul Newsom – February 26, 2015

Have you ever received this error when trying to run a SQL Server Reporting Services report?

An error occurred while processing the report. Unable to create a connection to data source ‘XXXXXXXX’ For more information on this error, access the report server on the local computer or enable remote errors.

SQL Server Reports 1

 

This is a common error in SQL Server Reporting Services reports caused by incorrect security settings. Let’s take a look at SQL Server Reporting Services reports and how to troubleshoot this type of error:

SQL Server Reporting Services (SSRS) reports are one of the best ways to get and examine data in Microsoft Dynamics GP. They offer the ability to export reports to different formats (including Excel), are extremely customizable and can even display information in graphical form (charts and KPIs) instead of just text. They are also available in Dynamics GP out of the box. So why is security such an important issue?

Primero, debemos comprender por qué la seguridad no es lo mismo que la seguridad de nuestro usuario de Dynamics GP. Dynamics GP utiliza inicios de sesión de SQL para la autenticación de usuarios dentro de la propia aplicación. Para evitar que estos usuarios inicien sesión directamente en la base de datos y revisen toda la información que tenemos almacenada en nuestro sistema ERP, Dynamics GP cifra estas contraseñas. Básicamente, esto significa que no podemos usar estos inicios de sesión para nada excepto un producto de Dynamics GP (esto incluye Dynamics GP, Management Reporter y soluciones específicas de terceros de Dynamics GP). Lo que necesitamos es una forma diferente de proteger estos informes.

Esto nos lleva a una forma diferente de autenticación usando credenciales de Windows. Así es como protegeremos nuestros informes de SSRS. Podemos proteger a usuarios individuales, aunque recomendaría usar un grupo de seguridad de Active Directory para facilitar la administración. Me gusta comenzar dentro de SQL Server Management Studio. Deberá tener permisos elevados dentro de Management Studio para configurar la seguridad, por lo que recomiendo una cuenta de administrador o la cuenta ‘sa’.

Lo primero que tendrá que hacer es crear el inicio de sesión o grupo de Windows dentro de SQL Management Studio. Realmente es menos crear un nuevo inicio de sesión que informar a SQL sobre un inicio de sesión o grupo de Windows existente. Dentro de SQL Management Studio, expandiremos la sección Seguridad, haremos clic con el botón derecho en Inicios de sesión y seleccionaremos Nuevo inicio de sesión.

SQL Server 2 Reports

Eso nos llevará a esta ventana:

SQL Server 3 Reports

Notice how we have the radio button for Windows Authentication selected and not SQL Server Authentication. From here, we will want to search for our windows logins or groups that we need to secure.

SQL Server 4 Reports

Within this window, we will type the name of the user or the group that we want to grant security for.

Make sure the location is set to the domain which the users exist in and that the object types include groups.

Once you have found the object which you want to secure, click ok on this window. We will want to go back to the New Login… window that we started on. We have selected our user, but we have not really given them any security yet. From the left hand menu, we will select User Mapping so we can grant permissions for our reports.

SQL Server 5 Reports

This will show a list of all of our databases which exist on this SQL server. The first thing we will need to do is check the box which is marked Map for any databases we want the users to have access to. Users will need access to the system database (usually called DYNAMICS), as well as any company databases on which they want to run reports. Once we have mapped the user to the database, we will need to grant them permissions on the mapped databases as well.

SQL Server 6 Reports

On the DYNAMICS (or system) database, all users will need public, as well as the rpt_all_user role. This is a role which is created when Dynamics GP is installed and the database is created. It will grant the required security on database objects, but nothing more. It does not give unfettered access to the database the way granting the DYNGRP role would.

The roles on the company databases look a bit different, although they should look familiar:

SQL Server 7 Reports

We will again need the public role, though from there, what is required will depend on your unique security needs. The rpt_* roles you see above are similar to the role selections you are offered when you first login to Dynamics GP as a new user. Some of them will be obvious as to their purpose; “rpt_power user” will grant access to everything that is available from a reporting perspective. Some will be less obvious though, and you may need some help. This link will download a Microsoft Dynamics GP SRS Report Roles document which can help with identifying which roles will grant permissions to which reports. It does require a customer source login to download, so if you are having trouble accessing the content, reach out to your Dynamics GP partner. Be careful when granting these permissions, anything you grant to the windows users can be used in SSRS, Excel, Access or any other program which can connect using an ODBC connection. The good news though is that you will not need to duplicate your efforts in order to grant security to Dynamics GP Excel reports.

The final step will be to officially create the login. Click the OK button on the New Login… window and it will do everything to create the login with the security you just defined.

That is all you need to do in order to grant security on the SQL side of things for SSRS report. My next blog will discuss the considerations of granting security using the SSRS report manager website. If you have questions or comments about this tip, please leave them below.

RSM has 30 years of experience helping clients maximize their Dynamics investment. If you are looking to optimize your Dynamics GP solutions, contact our professionals to learn how we can help.  Our professionals can be reached at [email protected] or 855.437.7202.

By:  Paul Newsom – Colorado Microsoft Dynamics GP partner

866556f40baf24da7db47a1a9d277baa

Paul Newsom

Receive Posts by Email

Subscribe and receive notifications of our new articles by email.

E-mail address *

Choose the categories: Data Analytics Dynamics 365 Business Central Dynamics 365 Customer Engagement Dynamics 365 for Finance and Operations Dynamics GP eCommerce IT Infrastructure NetSuite Cloud ERP Sage Intacct Microsoft 365 Leave this field blank if you are human:

Popular Items

Save time importing records with the mail merge macro

January 11, 2017

Resolving SQL Server Reporting Services “Unable to create a connection to data source” error

February 26, 2015

Publish Batches for Microsoft Dynamics GP — Part 3: How to Fix Batches Stuck on Publish

October 15, 2014

Problème de connexion à Dynamics GP – “Cette connexion a échoué. Essayez de vous reconnecter ou contactez votre administrateur système »

26 mars 2020

Dépannage des problèmes de synchronisation des données Management Reporter et Dynamics GP

1 novembre 2016

Impossible de créer une connexion à la source de données Erreur (rsErrorOpeningConnection) dans SSRS

Poser une question

Demandé il y a 9 ans, 8 mois

Modifié il y a 3 mois

Vu 142 000 fois

Cette question montre l’effort de recherche; c’est utile et clair

24

Cette question ne montre aucun effort de recherche; ce n’est pas clair ou pas utile

Gardez cette question.

Afficher l’activité sur ce post.

Je travaille sur l’intégration d’un rapport dans un navigateur, et j’obtiens cette erreur :

An error occurred while processing the report. (rsProcessingAborted) Could not create a connection to data source ‘dsFederatedSample_SurveyLevel_STG’. (rsErrorOpeningConnection) For more information about this error, access the report server on the local server machine or enable remote errors

enter image description here

Does it have to do with SQL vs Windows authentication?

  • reporting-services
  • ssrs-2008
  • database connection

compare

Share a link to this question

Copy link CC BY-SA 3.0

Follow

Follow this question to receive notifications

editedJanuary 7, 2015 at 10:26 PM

User avatar of JasonMARcher

JasonMARcher

13.6k 22 22 gold badges 55 55 silver badges 51 51 bronze badges

askedJanuary 29, 2013 at 5:46 PM

Caffeinated user avatar

Caffeinated Caffeinated

11.4k 39 39 gold badges 117 117 silver badges 210 210 bronze badges

0

Add a comment | 

10 answers 10

Sorted by: Reset to default

Highest score (default) Trending (recent votes count more) Date modified (newest first) Date created (oldest first)

This answer is helpful

18

This answer is not helpful

Save this response.

View activity on this post.

The first thing I would try is to get a bit more information about the error – it’s a pretty generic message.

You can 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\<SQL Server instance>\Reporting Services\LogFiles\

The next step is to connect as the datasource user to the database, run any code/stored procedure the report uses with the same parameters you use when running the report, and to see if any errors occur.

compare

Share a link to this answer

Copy link CC BY-SA 4.0

Follow

Follow this answer to receive notifications

modified on July 8 at 13:25

gotqn user avatar

gotqn

38.4k 44 44 gold badges 153 153 silver badges 233 233 bronze badges

answeredJanuary 29, 2013 at 6:41 PM

User avatar of Ian Preston

Ian Preston Ian Preston

37.9k 8 8 gold badges 92 92 silver badges 89 89 bronze badges

1

  • This just happened to me, it turns out that I no longer have permission to execute a stored procedure required by my report.

    – Cincinnati Programmer

    Jul 31, 2017 at 14:02

Add a comment | 

This answer is useful

5

This answer is not useful

Save this answer.

Show activity on this post.

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.

Share

Share a link to this answer

Copy link CC BY-SA 4.0

Follow

Follow this answer to receive notifications

edited Dec 11, 2019 at 23:44

Eddy Jawed user avatar

Eddy Jawed

427 5 5 silver badges 17 17 bronze badges

answered Mar 30, 2016 at 12:19

jamal ahmad user avatar

jamal ahmad jamal ahmad

51 1 1 silver badge 1 1 bronze badge

Add a comment  | 

This answer is useful

4

This answer is not useful

Save this answer.

Show activity on this post.

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):

  1. VS2013 SSRS: I converted my Data Source to Shared Data Source (.rds) with Windows Authentication (Integrated Security) on the Right Pane “Solution Explorer”.

  2. Original (non-shared) Data Source (on the Left Pane “Report Data”) got “Don’t Use Credentials”.

  3. On the Project Properties, I set for “Deployment” “Overwrite DataSources” to “True” and redeployed the Project.

enter image description here

After that, I could run my report without any further requirement to enter credentials. All shared data sources have been deployed in a separate “DataSources” directory.

enter image description here

enter image description here

compare

Share a link to this answer

Copy link CC BY-SA 3.0

Follow

Follow this answer to receive notifications

answeredJanuary 20, 2017 at 7:50 am

Avatar of Michael Alfes

Michael AlfesMichael Alfes

101 1 1 silver badge 2 2 bronze badges

0

Add a comment | 

This answer is helpful

4

This answer is not helpful

Save this response.

Afficher l’activité sur ce post.

Dans mon cas, cela était dû à l’utilisation de l’authentification Windows intégrée dans mes sources de données lors du développement de rapports localement, mais une fois qu’ils sont parvenus au gestionnaire de rapports, l’authentification a été interrompue car le site ne transmettait pas correctement mes informations d’identification.

  • La solution simple consiste à coder en dur un nom d’utilisateur/mot de passe dans votre source de données.
  • The harder fix is to properly impersonate/delegate your windows credentials through the report manager, to the underlying datasource.

Share

Share a link to this answer

Copy link CC BY-SA 3.0

Follow

Follow this answer to receive notifications

answered Jan 19, 2018 at 20:11

raterus user avatar

raterus raterus

1,840 16 16 silver badges 22 22 bronze badges

Add a comment  | 

This answer is useful

2

This answer is not useful

Save this answer.

Show activity on this post.

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

Share

Share a link to this answer

Copy link CC BY-SA 3.0

Follow

Follow this answer to receive notifications

answered Jun 12, 2015 at 20:58

user avatar of jeer65

jeer65 jeer65

51 3 3 bronze badges

Add a comment  | 

Cette réponse est utile

deux

Cette réponse n’est pas utile

Enregistrez cette réponse.

Afficher l’activité sur ce post.

If you are using null values ​​in your stored procedure, you will need to set the parameters to accept null values. It worked for me.

compare

Share a link to this answer

Copy link CC BY-SA 3.0

Follow

Follow this answer to receive notifications

modified on March 10, 2016 at 2:07

User avatar of johnnyRose

johnnyrose

6,980 17 17 gold badges 42 42 silver badges 61 61 bronze badges

answered on March 1, 2016 at 11:20 am

Genkis user avatar

Genkis Genkis

21 1 1 bronze badge

1

  • 3

    Do you mind expanding your answer with an example?

    –Antonio Ribeiro

    March 1, 2016 at 11:29 am

Add a comment | 

This answer is helpful

of them

This answer is not helpful

Save this response.

View activity on this post.

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

Share

Share a link to this answer

Copy link CC BY-SA 3.0

Follow

Follow this answer to receive notifications

edited Jul 20, 2016 at 13:14

answered Jul 20, 2016 at 11:32

Avatar of Anton Arkhipkin

Anton Arkhipkin Anton Arkhipkin

321 3 3 silver badges 15 15 bronze badges

Add a comment  | 

This answer is useful

2

This answer is not useful

Save this answer.

Show activity on this post.

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

Share

Share a link to this answer

Copy link CC BY-SA 4.0

Follow

Follow this answer to receive notifications

answered May 24, 2018 at 13:05

anonymous user avatar

anonymous anonymous

103 12 12 bronze badges

Add a comment  | 

This answer is useful

1

This answer is not useful

Save this answer.

Show activity on this post.

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

Share

Share a link to this answer

Copy link CC BY-SA 3.0

Follow

Follow this answer to receive notifications

answered May 13, 2017 at 0:57

User avatar of user2063329

user2063329 user2063329

433 2 2 gold badges 5 5 silver badges 14 14 bronze badges

Add a comment  | 

This answer is useful

0

This answer is not useful

Save this answer.

Show activity on this post.

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):

  1. Change the Report Server service to run under a domain user account, and register a SPN for the account.
  2. 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.

windows credentials

Share

Share a link to this answer

Copy link CC BY-SA 4.0

Follow

Follow this answer to receive notifications

answered Jul 29, 2020 at 13:02

afkhero user avatar

afkhero afkhero

16 5 5 bronze badges

Add a comment  | 

Highly active question. Earn 10 reputation (not counting the association bonus) in order to answer this question. The reputation requirement helps protect this question from spam and non-answer activity.

Not the answer you’re looking for? Browse other questions tagged

  • reporting-services
  • ssrs-2008
  • database-connection

or ask your own question.

  • The Overflow Blog
  • Una conversación con Matt Hicks de Red Hat en su camino de desarrollador a CEO (Ep. 494)

  • ¿Te perdiste nuestra conferencia Flow State? Ponte al día con todas las sesiones.

  • Destacado en Meta
  • Los marcadores se han convertido en guardados

  • Mejoras en la bandeja de entrada: marcado de notificaciones como leídas/no leídas y filtro…

  • ¡Revisor por la borda! O una solicitud para mejorar la guía de incorporación para nuevos…

  • Mise à jour collective : membres reconnus, articles et GitLab

  • Do I need to explain other people’s answers in code?

Report this ad

Visit the chat

Leap

0

rsProcessingAborted & rsErrorOpeningConnection

Related

3

SQL Server Reporting Services in TFS

4

SQL Server 2008R2 SSRS reports cannot connect to data source: Could not create a connection to the data source

472

Unable to connect to server – A network-related or instance-specific error

0

TFS reports not opening

0

Unable to read the following data row error in the subreport

0

Reporting Service in TFS 2013

0

SQL Server 2014 data source not working on SQL Server 2008 R2 report server

0

SSRS (Sql Server 2016) Report error when running from IIS is ‘Failed to create a connection to data source ‘DSTEST’. (rsErrorOpeningConnection)’

burning questions from the net

  • Why are electrolytic capacitors still made?
  • Grammar says starting “I like Pearl hesitated to…” with “I like” is fine, but my teacher says it’s grammatically incorrect
  • What level of logging should be used for an expected but (potentially) bad occurrence?
  • The computer can’t do anything that I can’t do with pencil and paper.
  • Can you use Soul Catching Gloves to effectively convert temporary hit points into healing?
  • How can I keep these stripes together?
  • Does the Crusher exploit make no sense for a small character?
  • What is the definition of “third curiosity”?
  • Could a pure sign language society be as advanced as a speech society?
  • Collation conflict when comparing sql_variant to varchar literal
  • How can I change the end of the line in tikz?
  • What extra number did I dial?
  • How can an attacker change the address to receive someone’s reward?
  • Zottffssentettffssen
  • Remove cases for models that require searching for multiple points in a list
  • How do compilers work in a language that doesn’t allow recursion?
  • Why does the “Tanks for Ukraine” discussion focus on the Leopard 2?
  • Find a word in the dictionary of all possible words
  • Time dilation in an elliptical orbit
  • Natural armor versus mage armor, which takes precedence?
  • Create a paytable of size 3×3
  • How do human police capture (non-lethally) much larger and more dangerous races?
  • How do “dotted inseam = one beat” teachers handle “irregular compound” measurements (e.g., 5/8)?
  • Is it reasonable for my teacher’s director to prohibit me from having private communications with my co-director?

question flow

Subscribe to RSS

question flow

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.


Video Cannot create a connection to data source

Related Posts

Free chat room code for my website

Contents1 How to set up a free chat room on the website.2 3 comentarios2.1 Trackbacks/Pingbacks2.2 Submit a Comment Cancel reply3 How to Create a Chat Room Website…

Background image full screen css

Contents1 Cómo – Full Page Image1.1 Example1.2 Example2 CSS background image tamaño tutorial: how to codify a complete page background image3 Perfect Full Page Background Image3.1 Méthode CSS géniale,…

WordPress leverage browser caching

Contents1 Aproveche el almacenamiento in hidden del navegador1.1 Will it works for my website?1.2 Where are plugin options1.3 Some JavaScript files still display under Leverage Browser Caching1.4…

WordPress post to facebook page

Contents1 How to Automatically Post to Facebook from WordPress1.1 Download Now: How to Launch a WordPress Website [Free Guide + Checklist]1.2 1. Create an IFTTT account.1.3 2….

Download images from wordpress media library

Contents1 How to export your WordPress media library1.1 Download maintenant : How to launch a WordPress website [Free Guide + Checklist]1.2 How to export your WordPress media…

WordPress single post template

Contents1 How to Create Custom Unique Post Templates in WordPress2 Post Template Files2.1 author.php2.2 Fecha.php3 Handbook navigation4 How to Create Custom Single Post Templates in WordPress5 Video…