Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Report builder 3 can connect to a model created from a data source but not a data source Expand / Collapse
Author
Message
Posted Saturday, June 21, 2014 7:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:37 AM
Points: 25, Visits: 66
Group,

I apologize if this is already in here. I'm to my wits end trying to resolve this. We use IBM Notes and dump data to SS 2008R2. I think there's another issue there, but not the point for today.
Users are able to successfully open up reporting services and run the reports we build for them via VS and upload to RS. All access the server from externally except our report writers who work on the server locally. When we make a data source our in Reporting Services, reports connect to it no problem. When we try to use report builder to connect to it we get a myriad of error messages, rarely consistent. When we make a model from that same data source then users can navigate to THAT and make simple reports but we can't get SQL statements to run in the query designer from Report Builder, only point and click and it turns it to XML. My main goal is to be able to let users write their own SQL against the data source. Bread crumbs tell me that if they can connect to the data source that is possible, but models cannot. I might be wrong there and would appreciate any help. Our DBA left and nobody can figure this out. We're looking for a new DBA but those are not easy to find. In the mean time it's my problem to figure out and I only have 2 weeks before I have to demo this to a client. Help please?!?!
I have gone through and made sure the ports are open to the SS (1433 and 443), users of any level cannot connect. I am a full dbo and even I can't connect from outside of the server or even on the server using report builder. I'm sure there's something dumb that I'm over looking. Here are some of the errors we get, in seemingly random order:
Most Common:
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
{Named pipes is on and remote connections ARE allowed.}

Login Failed for user <user name>.
This is one persistent bugger that comes up even for full dbo's.

Again, what I can't figure out is why if I make a model of the same source it is perfectly happy connecting to it. Any help that anyone might able to provide would be greatly appreciated!
Post #1584748
Posted Tuesday, July 1, 2014 10:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:37 AM
Points: 25, Visits: 66
Can anyone help out? I'm really stuck and don't even know where to turn to look for advice on how to make this happen. I would appreciate it an immense amount!
Post #1588137
Posted Tuesday, July 1, 2014 1:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:53 PM
Points: 1,780, Visits: 5,750
What authentication method are you using?

Have you tried forcing TCP rather than named pipes ? (try putting "tcp:" in front of the server name)



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1588172
    Posted Wednesday, July 2, 2014 9:24 AM
    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: Monday, July 21, 2014 4:37 AM
    Points: 25, Visits: 66
    MM,

    Thanks for the reply.

    Let me explain the whole scenario and maybe it can help. I think I have the answers to your questions, but I'm not sure. Our DBA left and I'm learning as I go! Problem is the demo I have to give on Wednesday next week

    The same server is the active directory, SQL server and Reporting server. All users are in groups that access the server with access given via groups rather than individual security level. Each user can successfully launch and run report builder with MODEL's created from the data source but when trying to connect to the data source, they can't.

    Reporting Services runs perfectly fine inside and out with "normal" connection strings (data source=BlueJay;initial catalog=DBName) from the internet or intranet.

    When I make a MODEL of the report from reporting services (Open data source, "create model" button) it will connect to those tables with report builder, but only one.

    If I make a connection from report builder while on the inTRAnet, report builder connects to the server fine with "normal" strings. When I go outside that network and connect from the inTERnet, I can't get it to connect with one of many errors. They aren't consistent.

    The firewall is open for * to 1433 and 1433 to *.

    Here is our "authentication" section of the config
    	<Authentication>
    <AuthenticationTypes>
    <RSWindowsBasic/>
    </AuthenticationTypes>
    <RSWindowsExtendedProtectionLevel>Off</RSWindowsExtendedProtectionLevel>
    <RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>
    <EnableAuthPersistence>true</EnableAuthPersistence>
    </Authentication>

    The data source authentication "normally" has "Credentials stored securely in the report server" with our generic reader access to the database with the password stored but that doesn't seem to work because it always asks the users for the password, which they don't have. I've tried changing to "Credentials supplied by the user running the report" and even entering my dbo access, I get the following errors.

    I tried changing the string to "data source=tcp:BlueJay;initial catalog=DBName" or "data source=tcp:{IP};initial catalog=DBName" or "data source=tcp:{FQDN};initial catalog=DBName" or "data source=tcp:{FQDN}/BlueJay;initial catalog=DBName".

    First ("data source=tcp:BlueJay;initial catalog=DBName") gave me this, though name and password are correct
    Log on failed. Ensure the user name and password are correct.


    Second ("data source=tcp:{IP};initial catalog=DBName") gave me
    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 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)


    Third ("data source=tcp:{FQDN};initial catalog=DBName") gave me this, though name and password are correct
    Logon failure: unknown user name or bad password
    ----------------------------
    Log on failed. Ensure the user name and password are correct.



    Fourth ("data source=tcp:{FQDN}/BlueJay;initial catalog=DBName") gave me this:
    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.)


    Googling of each of these errors gave me a few articles and I read each and followed all instructions I could find without luck.

    I think my most confusing thing is why can I make a model of the same source and it's OK but I can't access the data source itself?

    Any help will be much appreciated. If anyone knows what they are doing and are willing to help for a fee, PM me for that! I just don't know where to get the needed knowledge.

    I hope I have provided the needed answers.
    Post #1588524
    Posted Wednesday, July 2, 2014 4:03 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 3:53 PM
    Points: 1,780, Visits: 5,750
    Can you explain, with screen shots, how you are trying to connect to a data source, (not a model), and include the exact errors?

    It is pretty hard to troubleshoot general problems, but specific steps and errors are much easier.


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1588652
    Posted Wednesday, July 2, 2014 7:27 PM
    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: Monday, July 21, 2014 4:37 AM
    Points: 25, Visits: 66
    MM,

    I don't see how to post screen shots here, so I've made a video here to show me able to do the same steps on the server but not on the internet. Does this help?
    http://hsnow.co/internal/sql/BlueJayReportBuilderIssue.wmv
    Post #1588710
    Posted Thursday, July 3, 2014 3:27 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 3:53 PM
    Points: 1,780, Visits: 5,750
    I will take a look later, but maybe someone else will jump in before then.

    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1588765
    Posted Monday, July 7, 2014 3:53 AM
    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: Monday, July 21, 2014 4:37 AM
    Points: 25, Visits: 66
    Does anyone have any ideas to help on this? Your help is greatly appreciated!
    Post #1589788
    Posted Monday, July 7, 2014 4:40 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 3:53 PM
    Points: 1,780, Visits: 5,750
    Hi, so I had a look at the video and noticed one difference.

    At about 1:30 into the video, you set up the credentials for the data source and "Use as windows credentials" is ticked.

    At about 3:30 in, you do the same again for the data source from your local machine, but "use as windows credentials" is not ticked.

    So, are the credentials that you are using for the SQL connection Windows credentials or SQL credentials?


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1590151
    Posted Monday, July 7, 2014 4:58 PM
    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: Monday, July 21, 2014 4:37 AM
    Points: 25, Visits: 66
    Sorry MM, I normally do check that box and just tried again with it ticked, same outcome and same error... Is there something that needs to be done to open the windows authentication through the web to the SS? I can't find anything stating specific requirements, so I'm not sure...
    Post #1590154
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse