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

Question

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

kinderdesign

Right there with Babe

Points: 759
More actions
June 21, 2014 at 7:48 pm

#290322

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!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

kinderdesign Right there with Babe Points: 759 More actions · Answer 1

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 SSC-Forever Points: 47068 More actions · Answer 2

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

kinderdesign Right there with Babe Points: 759 More actions · Answer 3

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.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 4

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

kinderdesign Right there with Babe Points: 759 More actions · Answer 5

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 SSC-Forever Points: 47068 More actions · Answer 6

I will take a look later, but maybe someone else will jump in before then.

MM

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

kinderdesign Right there with Babe Points: 759 More actions · Answer 7

Does anyone have any ideas to help on this? Your help is greatly appreciated!

mister.magoo SSC-Forever Points: 47068 More actions · Answer 8

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

kinderdesign Right there with Babe Points: 759 More actions · Answer 9

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...

mister.magoo SSC-Forever Points: 47068 More actions · Answer 10

That's fine, just checking that wasn't the problem.

Now the other question is based on me noticing that you had a login prompt for connecting to the report server right at the start of the video and appeared to cancel it.

When you switched to your local desktop, you were already past this point, but did you also get a login prompt for the report server and did you successfully log in (I think this will be for connecting to the Report Server web services that are used by the Report Builder) ?

Is the Report Server on the same domain and local network as your PC, or is it truly remote, connecting through the internet?

MM

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

kinderdesign Right there with Babe Points: 759 More actions · Answer 11

Yes, report builder asks for password on connect and on the wizard.

It is truly on the Internet: we have about 2000 users who connect remotely on separate domains. The computer I'm using is just a stand alone windows pro.

kinderdesign Right there with Babe Points: 759 More actions · Answer 12

Any help from anyone who knows what I might have set wrong would be greatly appreciated...