Analysis Services Connection Issue
-
From what I have read, it seems that Analysis services can only be connected to via NT Authentication. While this might serve well for most people, I am attempting to develop a reporting portal that my client's employees will access from home.
The security reuirements being what they are with SOX (Sarbanes Oxely) - when the people from home connect - they connect to a separate domain - let's call this client.net. ClientExt.net has no trusted permissions with the actual lan - call this one ClientInt.net. Of course - reporting services is on the ClientExt.net, with Analysis services and SQL Server on the ClientInt.net.
Therefor, we cannot use integrated security, or even designate a specific user because no ClientExt.net users are recognized by the ClientInt.net domain - or vice versa. Moving analysis services is a no-no due to the security rules, besides - Analysis services connects to SQL Databases using integrated secuity also. So we would actually have to move the whole kit and kaboodle onto the ClientExt.net domain - which would violate just about every security rule known to mankind...
So where does this leave me? A little confused - perplexed - and a bit annoyed that Microsoft would completely leave out any other option of using another method of connecting to the Analysis Services Data Store other than Integrated security. Other than that - the client wants me to fix this - and fix it now.
Please keep in mind that I was not the person that suggested we take this route, that person who suggested that to the client no longer works here. I would not have suggested this solution knowing their architecture and after reading about this product - but I am stuck with it as the client has purchased the products and already installed them.
Any suggestions BESIDES adding a trust between the domains - which they cannot due without violating their own security rules.
Thanks!!!
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com -
Hey L2L,
Any more details on your portal? If you're using asp.net you could try using ADOMD.net (or raw low level xmla) which will let you specify connection details, passes through the FW on port 80 which you'd want to SSL pretty quickly. Using this method you could get the user to enter their internal domain uid/pwd and use these, but as i said before, make sure you ssl the connection for your app as well otherwise they're passing these as clear text over the wire.
Are you using RS solely for the reports or some other OALP browsing component/tool? It would make for crap security and won't allow for user/role based security but if using only RS you could store a single set of win credentials in the RS server and then get it to use these as windows credentials.
cheers,
steve.
Steve.
-
SteveFromOz,
FollowUp: I am using reporting services to attach to a SQL Analysis Server. The reporting services server is in a domain that does not have permissions to the domain with the Analysis services server on it. The domains do not have any sort of trust - meaning that they cannot see each other or resolve user info from the other.
I used your second suggestion - but had to carry this one step further. Since the reporting services server domain didn't trust the databse domain - I had to create two NT accounts on the two different machines (reporting server and analysis server) with the same name and password. I then added the local account on the analysis server into the Group that contained the cubes I needed to accesss.
As far as user based security - the data is being filtered on the reporting server end by using the user info that they logged into the system with. While this is NOT ideal, I inherited this project because it was above the other's head who had been handling it and the structure of the servers has already been approved and built.
Funny thing about this all - once I got it working and posted the report, the big-boos from the IT department of our client came back and said - why aren't you using the SQL Relational Star Schema Database to return the data to reporting services... Trying to teach a client to understand that time is a dimension and all the functionality you would either have to build - pushing out the deploy date, or drop the capabilities to by using a relational database, is very difficult at best.
The clients statement to me was - well why don't you just build a summary table. Can we say - ok - let's build our own data cube by hand so I can understand what it does...
No matter what - the cubes will not go away as the other half of the reports reside in BI Portal. But I await his final decision as to whether we will create a summary process to create virtual cubes in SQL Server, or to use the cubes we already have built for reporting services.
One other comment he made intrigued me. He stated that reporting services works better with relational data vs. data cubes. Could this have some basis in fact - or is this just a myth propagated by those who fear Data Cubes?
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com -
Good ol' pass-through security
At leats you have a working solution!re: RS is better on relational vs olap, yes and no.
I've been doing a bit of RS against As and have been lucky enough to have the time to create two copies of each report, 1 against the AS and the other against SQL (same star schema that feeds the cubes, n.b. star schema was designed solely for this cube). The speed difference is quite big in favour of AS, but I have come across a (currently insurmountable) problem, where I am getting an error (rather than cellset/rowset) returned from AS due to using 'non empty' and therefore one of the axes is empty (ie the user selected parms that returned no data). While the SQL query is *much* slower, if there are no valid records, it returns an empty rowset, which means you can then use the 'empty message' property on the grid to let the user know.
Where he may have been coming from is that for you (the developer) working with AS versus a relational DB is harder as you don't get the nice GUI query designer.
I would e sticking with what you've got, especially if you're (the client is) looking to go to SQL2K5 (when it gets released, prob my 60th birthday
) as if you've got enterprise edition, you get the new report builder product (old active views) that let's your users build reports on the fly, which works equally well against both relational and olap.Steve
Steve.
-
Thanks SteveFromOz - That's about what I thought...
Now the fun part - trying to get the guy to leave well enough alone and let the working product be.
Wish me luck!
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply