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.