SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Report builder 3 can connect to a model created from a data source but not a data source


Report builder 3 can connect to a model created from a data source but not a data source

Author
Message
kinderdesign
kinderdesign
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 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!
kinderdesign
kinderdesign
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 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!
mister.magoo
mister.magoo
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5408 Visits: 7871
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • kinderdesign
    kinderdesign
    SSC Journeyman
    SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

    Group: General Forum Members
    Points: 77 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 Sad

    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.
    mister.magoo
    mister.magoo
    SSCertifiable
    SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

    Group: General Forum Members
    Points: 5408 Visits: 7871
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • kinderdesign
    kinderdesign
    SSC Journeyman
    SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

    Group: General Forum Members
    Points: 77 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
    mister.magoo
    mister.magoo
    SSCertifiable
    SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

    Group: General Forum Members
    Points: 5408 Visits: 7871
    I will take a look later, but maybe someone else will jump in before then.

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • kinderdesign
    kinderdesign
    SSC Journeyman
    SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

    Group: General Forum Members
    Points: 77 Visits: 66
    Does anyone have any ideas to help on this? Your help is greatly appreciated!
    mister.magoo
    mister.magoo
    SSCertifiable
    SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

    Group: General Forum Members
    Points: 5408 Visits: 7871
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • kinderdesign
    kinderdesign
    SSC Journeyman
    SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

    Group: General Forum Members
    Points: 77 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...
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search