locked
Connection string from Microsoft Report Builder to SQL Server does not work. RRS feed

  • Question

  • I have SQL Server, community edition, Developer 2017. It works very well, I have no problem with it. I also have Visual Studio 2017 with most of the applications in C# or C++. I have numerous connections from those applications to my SQL Server databases. They work very well. Now I need to set up a Report Builder and in general perfect the reporting service which I haven't even begun. The stumbling block is I cannot make a connection between the Report Builder and SQL Server databases although the connection string seems to be OK:

    Data Source=DESKTOP-54NQG25\admin;Initial Catalog=AdventureWorks2017

    I get errors that The Report Builder which I invoke as an administrator cannot connect to the datasource.

    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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

    I do have a default, unnamed instance of SQL Server. It does not require me to enter a password.

    I also tried other databases with the same negative result. WHAT IS THE PROBLEM?

    Thanks, - MyCatAlex





    • Edited by MyCatAlex Sunday, November 24, 2019 7:48 PM
    Sunday, November 24, 2019 5:36 PM

Answers

  • Well after numerous attempts I finally connected to Sql Server Dev Ed. AdventureWorks2017 database from the Report Builder, a stand alone application. I am at the end of my wits. Look at how it worked.

    Thursday, December 12, 2019 10:47 PM

All replies

  • Hi MyCatAlex ,

    Please try enabling SQL Server Browser services.

    Monday, November 25, 2019 12:53 AM
  • Could you give me better details how to do it step by step. I tried to tinker with SQL Server Management Studio to set up Reporting services and got lost.

    Thank you, - MyCatAlex

    Monday, November 25, 2019 1:11 AM
  • Could you give me better details how to do it step by step. I tried to tinker with SQL Server Management Studio to set up Reporting services and got lost.

    Thank you, - MyCatAlex

    Here is a link. Hope this will help.

    https://docs.microsoft.com/en-us/dynamics-nav/how-to--start-sql-browser-service

    Monday, November 25, 2019 1:16 AM
  • I just googled and found a suggestion that I need to modify this file RsReportServer.config. I don't have it in my system. I will try your link.

    Thank you, - MyCatAlex


    Monday, November 25, 2019 1:27 AM
  • I've seen this page under the link you posted many times today. It is an old suggestion. There is no more SQL Server Configuration Manager. That must have been for SQL Server 2012 or earlier.

    Sorry, - MyCatAlex

    Monday, November 25, 2019 1:38 AM
  • I've seen this page under the link you posted many times today. It is an old suggestion. There is no more SQL Server Configuration Manager. That must have been for SQL Server 2012 or earlier.

    Sorry, - MyCatAlex

    That was a mistake. Sql Server Configuration Manager does exist but it has to be invoked via an .msc file located in some obscure Windows directory. So, what's the result? The result is that SQL Server Reporting Services has been running all along in SQL Server but for some reason it is indicated for SQLEXPRESS only. This is the picture.

    I still cannot connect REPORT BUILDER with SQL Server.

    Monday, November 25, 2019 3:41 PM
  • As first suggestion and the picture of your post,  right click the SQL Server Browser (the second last one in your picture) and choose Start .

    Then Try if you could make the connection work.

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 26, 2019 1:49 AM
  • As first suggestion and the picture of your post,  right click the SQL Server Browser (the second last one in your picture) and choose Start .

    Then Try if you could make the connection work.

    Lukas


    Lucas hi,

    That did not work, but I found an .msc file "comexp.msc" and also another file "services.msc" just now and activated SQL Server Browser which is now on automatic start. Still I cannot connect through the Report Builder and while trying I get a message that data source is not found or I don''t have credentials. I am the administrator on this computer and I can restart SQL Server any time.

    Thanks, - MyCatAlex





    • Edited by MyCatAlex Tuesday, November 26, 2019 10:00 PM
    Tuesday, November 26, 2019 8:02 PM
  • OK I now have a partial success, very funny. I can connect to master and tempdb but cannot connect to my regular databases, including AdventureWorks2017 and others. What the heck is this?

    - MyCatAlex

    Tuesday, November 26, 2019 10:12 PM
  • It is very possibly related to the account permission in SSMS. You could find help in SQL Server Engine for the last post.

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 29, 2019 9:22 AM
  • It is very possibly related to the account permission in SSMS. You could find help in SQL Server Engine for the last post.

    It could possibly be the case however, nothing works so far. As I said I had used this connection string:

    Data Source=DESKTOP-54NQG25\admin;Initial Catalog=AdventureWorks2017

    When I replaced AdventureWorks2017 with tempdb it instantly connected! I went to SQL Server and checked users under Security tabs for both databases. They have identical users! I added one more to AdventureWorks2017:

    DESKTOP-54NQG25\admin

    It still did not work. What else can I do?

    Although it may be rather clumsy, I may somehow copy parts of AdventureWorks2017 tables to tempdb and make reports from there. Is it a way to go?

    Thanks, - MyCatAlex

    Thursday, December 12, 2019 7:43 PM
  • Well after numerous attempts I finally connected to Sql Server Dev Ed. AdventureWorks2017 database from the Report Builder, a stand alone application. I am at the end of my wits. Look at how it worked.

    Thursday, December 12, 2019 10:47 PM
  • Hmmmm, it is not like what we normally see, it is like :

    Anyway, glad the issue was resolved.

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 13, 2019 7:58 AM
  • Lucas hi,

    Thank you for your help. It is amazing, there exist a hidden convention to use ONLY initial catalogue in the Connection String window. Who knew? However as I mentioned before I was able to use standard Connection string on tempdb. It is very confusing.

    I have million of other things to take care of in Microsoft forest.

    - MyCatAlex

    Friday, December 13, 2019 4:30 PM