Authentication only allowed by IP address 1

[force5]

Hello,

we have been using Reporting Services for several months. It is installed on a Microsoft Server 2003 Standard, SQL 2005 <SP3> server. Everything has been running fine until the past month. I haven't been able to pinpoint what caused this issue, but we can no longer authenticate into the Reporting Services using the FQDN (

http://server.domain/reports).

After several attempts with my domain credentials, it gives us an authentication error. Logging in with the IP Address works fine (

http://172.16.XX.XXX/reports).

Like I mentioned, this used to work with no issues. The only thing that has changed is maybe a few Microsoft updates to the server. Looking at our DNS servers, there are all appropriate records for this server showing the correct IP and FQDN. I could probably create a CNAME record to map a name to the IP, but I really would prefer to get it going the old way for now until we finish getting all of the data in place.
At this point, I am not too concerned at what caused it, but any advice to help me get it working again by FQDN would be greatly appreciated.

Thanks in advance!

 

[jymm]

so - when you get to reporting services (through the IP address) you can actually open the report and see the data, right appropriate to your user ID?

only reason I ask is that not long ago we had a similar problem, BUT could not get the REPORTS to load and it was a permissions problem in IIS.

if you can go through the IP and get to the reports and your user authentication works - then I am going to bet your problem is IIS as well... and I have the MS solution (they have seen this a BUNCH) if this is your situation.

 

[force5]

Thanks for the reply jymm,
yes...when using the ip address in the URL, I can log in with my credentials and then view anything within there. However, until we get all of the other users' data and reports built for each individual, we only have a few sample reports in there for now, but I can view them. If you have a solution, I would love to take a look at it, if I haven't seen it already. I have found a few things on microsoft.com regarding this and also a few other forums, but so far, no luck.
Let me know,

Thanks,

Thanks,

Chad
Network Administrator

 

[jymm]

ok chad - try this (hopefully imgaeduck will let you get there) - this is what we ran and it reset the IIS permissions to what it should be for windows authentications.. ymmv... we actually used one of our free calls to MS because of being on technet... let me know if this helps

http://imageduck.com/images/sz1t7163c6vqokvcjb8d.jpg

 

[force5]

jymm...thanks alot, that is awesome. It did reset the authentication and now it is working via FQDN. SWEET!
Unfortunatley though, now it has broken my data connection:

"An error has occurred during report processing.
Cannot create a connection to data source 'xxxxxxxx'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors"

If you can help with this also, I would be greatful...

Thanks,

Chad

Thanks,

Chad
Network Administrator

 

[jymm]

I have seen that happen once -

couple of things to look at / try.
1) try going in and redeploy that report. SOME times I have seen the connection go kaflooey (a technical term from here in the midwest) - not very often though.
2) Make sure that you can actually run the report through studio - check the connections to see if they are windows authentication (you know how?)
3) make sure that YOU have access to the data in the database. Now that the report is on windows authentication make sure that the domain user has access.

reply back if you need more

 

[force5]

Hi jymm....we found out that when we changed the authentication type within "System Settings" in the Report Server (via the reporting services interface), the data connection works. It actually works for every type of authentication EXCEPT for the one we want to use, LOL. We would like to use the "Integrated Security" method, but when we change it, nothing works.
Any ideas on this issue??

Thanks for your time.
chad

Thanks,

Chad
Network Administrator

 

[jymm]

by "integrated security" you are talking Windows Authentication, right?

a friend had SQL authentication (or mixed mode) set up then switched. he was not aware of the difference on the SQL side NOR on the RS side (since they are different).

so - sql had JYMM as a user when he was doing SQL auth - THEN he needed to switch and use domain/JYMM. Same on the reporting services page. domain admins could see and run all of the reports - others could no longer see the reports. SO - he also needed to go http:\\server\reports (as an admin) and look at the properties tab to see who had access to the reports. He needed to switch from JYMM to domain\jymm ... so two switched - on the DB server AND RS.

make sense or am I rambling and grasping at straws?

 

[force5]

yes, I am talking about the "Windows Integrated Security" option, sorry about that. This used to work! And you are correct, even when it did work, we always (even admins<I am the network admin here>) had to type our domain in front of the username, ie domain\chad.p
The way it USED to work was we only had to authorize 1 time...and that was to authorize INTO the

http://server/reports.

Once logged into the reports page, we were able to view reports without re-authorizing. However, after playing with the options to try to get our "old method" to work again, I DID see how it worked as you stated - - -we would have to log in first to get to the page, and then log in a second time when you tried to view a report. Im lost, LOL. And PS....I am from the midwest to, so you can say that I am Kaflooeyed!!

Thanks

Thanks,

Chad
Network Administrator

 

[jymm]

hey chad - sorry - had some network/telecom problems of my own to solve - tell me how things are going now and I will see if I can point you in another direction.

 

[force5]

Hi jymm,

no need to apologize. I understand! Due to the economy, we have laid off nearly 50% of our staff and now I am the only IT guy here. So I am staying quite busy myself. I appreciate your time in checking back with me and helping me out.
Well, this is where we are at now: all of our reports are working fine. The webpages are being served properly, but we are still using an authentication method that we don't really like and it is working strange. If we type the report URL as simply

http://server/reports,

it will send the user to the reports page right away and everything seems to be locked down to what the user needs as far as priveleges. And to view a report, he will have to log in via a generic report login (its a light brown bar across the top of the reports page with a generic username and password field) when he clicks on the report.
However, if we type report URL as the FQDN

http://server.domain.local/reports,

a generic windows authentication box will pop up where he will have to enter domain\user.name and password. And then the rest applies as above (log in for reports, etc)
Ideally, it would be great if we could use 1 single "web" login that authenticated with our domain. And then, once logged in, they could view everything user specific for the remainder of their session. Kind of like my CDW.com login. I log in one time and can view everything like quotes, recent orders, etc, all through that single sign on at the initial web page.
I know I am rambling on, hopefully it all makes sense.
I also would love to somehow set a 10 minute timeout on the reports

Thanks,

Chad
Network Administrator

 

[jymm]

ok - that helps

can you get into Visual Studio and edit one of those reports? My bet is that the security for the data is set up 'differently' than you are expecting. IF you can get in I can 'talk' you through what needs to be done. Easiest way is to get into the server, start Visual Studio and open the project... sounds worse than it is.

ppl getting laid off all over the place - my company is in the ag industry and the past few weeks orders are picking back up... so there IS hope - except the news this morning brought more closings and lay-offs to the area (sigh).

 

[force5]

Ill try this tomorrow jymm...ran into some issues today that took me away from trying this.

Thanks,

chad

Thanks,

Chad
Network Administrator

 

[force5]

hey jymm...my manager ahs been out for a week. Im a lone ranger here in IT. I really still appreciate your help. I will try to get some of this info to you so you can hopefully help me figure this out.

Thanks again!

Thanks,

Chad
Network Administrator

 

[force5]

Hi jymm...these reports were all pretty much built within Reporting Services. And our Reporting Services were all configured via SQL Server 2005 > Reporting Services Configuration. I can still open them within Visual Studio?

Thanks,



Thanks,

Chad
Network Administrator

 

[jymm]

ah - so they were built using 'Report Builder' within reporting services, right? For future reference - it is an 'ok' tool, but very easy to mess other users up depending on security. example - if you redo the data source sometimes it breaks the report. Studio is MUCH easier to control and easier to program the 'fancy' stuff - but then most programmers are control freaks regarding the data (yup - that would be me). ok - I am off the soap box.

Pretty much we do not use 'Report Builder' unless a user needs to do adhoc stuff - then they are on their own.

I am flying a bit blind here, but let's see - if you go 'Home' on http:\\server\reports. Hit the Properties and vaerify that your users are domain\user and not just 'user' - these are your security groups in an ideal world. Once that is verified - If it is a 'vanilla' install you should be able to go back to home again and there is prolly a folder called 'Data Sources'. Just like the name implies - you know what is in there. Go into that folder - again - verify the users on the property tab. USUALLY this is inherited from the root... from there you should be able to click on each data source in that folder. You should see a connection string something similar to

Data Source=MyServer;Initial Catalog=MyDB;Integrated Security=True

AND you should have the appropriate 'connect using' radio button checked. I use 'Windows Integrated Security'. Hit APPLY - Be careful of hitting the 'Generate Model' button - sometimes it breaks the reports downstream for other users. You should also verify the domain/user on the property tab for each data source. Rinse and repeat for each data source.

at some point you may also need to check the SQL security on the DB - but I am betting that is set up ok... but I am not much of a betting man.

btw - I thought I was the only one who said "I am playing lone ranger in the IT department today" when all of the others are gone... wow... small world kemo sabe

 

[force5]

Thanks jymm....
well, this is actually a project that the IT Manager here started. And when he "broke it" he ended up getting me involved to fix it. LOL. I did fix it for him last fall, and then due to other higher priority projects, we kind of let this reports project go. Now, he started dinking with it again and broke the authentication...which is where you came in to save the day! So, I am just kind of "helping" along, but I surely will forward your information to him regarding using report builder vs visual studio!

Well, I verified that going into Properties using either method:

http://server/reports

OR

http://server.domain.local/reports

both give you the same results, domain\users

I also verified all of your data source questions. We have the same as you do above, except there isn't a "IntegratedSecurity=true" after the DB.
Also, I can see he has 2 data sources, probably for testing. the first one is set up as:
1.)Radio button is set to "Credentials supplied by the user runnning the report"
1a.) with the checkbox selected to "Use Windows credentials when connecting to the datasource"

2.) Radio button is set to "Credentials stored securely in Report Server
2a.) username = domain\poweruser + pswd
2b.) with the checkbox selected to "Use windwows credentials when connecting to the datasource"
2c.) with the checkbox selected to "Impersonate the authenticated user after a connection has been made to datasource.

I can see that he is missing the type of authentication in the string. Please keep in mind jymm that 99% of our users that will need access to these reports will be checking their reports from their home computers (not joined to our domain) over a PPTP VPN connection...so we need authentication that works both internally and externally.

Thank you so much!



Thanks,

Chad
Network Administrator

 

[jymm]

do we have the same boss (just kidding)?

I 'farm' a BUNCH of reports internationally as well as domestically and also to users who are always connected to the network - the 'external' (not always connected) users are connected by the VPN so as of the point of authenticating to the network they are authenticated to the domain - hence I am letting AD security do all of the work. Easy way to check that is to put a report out there that has the variable User!UserID in it. I usually put this and other info in the footer of the reports so that if they print it and leave it around - I know who to turn in to the IT security Nazi (my boss).

I have not messed a bunch with that data source in RS for just the reason that they can get messed up - working a bit on memory, but if someone re-generates the model - sometimes things get really corn-fused with security - it was something to do with Enterprise vs Standard or Developper, but wow - I do not find my notes on that one... dont tell OUR boss that.

 

[force5]

Yes, but we use PPTP hosted via a 2801 router, not from a Windows Server, so our users authentication takes place on the router via a pre-programmed list of usernames and passwords. And then, I have the access lists set up to allow access by these VPN IP addresses. So, in my case, I don't think that the router will pass this type of authentication to allow it to integrate well with reporting services. I may be wrong.
Have you ever heard of a way to migrate reporting serverices reports over to visual studio? I did a search on it on the msdn forums but could not find anything. i'll keep looking.
Thanks...

Thanks,

Chad
Network Administrator

 

[force5]

So jymm, with your understanding of RS, and even VS, IS it possible to have an initial web-based login page on the front end (that would integrate with AD) and the users could log in just that 1 time at that initial page and the credentials would be used throughout the remainder of the session, even when viewing reports?
I know this is possible if we create a list of users via RS and point the authentication to that list, but would really like to use the method stated above.

Thanks

Thanks,

Chad
Network Administrator