Windows Authentication

This is a result of how Windows security (NTLM) works. You can do 1 hops with a given credential. So if you login locally to your report server, you will go 1 hop to the data source. If you access your report server remotely, you'll go 1 hop to the report server, and when you try to make your second hop to the data source, you will fail.

Solutions are to enable kerberos in your environment - then your credentials will successfully make the second hop, to use stored credentials, or to use prompt credentials.

-Lukasz

Store the credentials (windows or SQL standard) inside the datsource.

Hi,

I have read an article describing in high level fashion how DogFood is being eaten as far as security AND access control are concerned. SQL Server mag instant id 48743

This article does not provide much insights about technical implementation of best practice. The picture looks good but there is a gap. How can we really benefit from that great DogFood practice beyond Executive summary type of article.

In my shop, I was never able to get integrated security to work at the Report server level, regardless if it is on the same box than the data or if on a dedicated report server that takes the data from another SQL box. Because of that, I am using a SQL login in the datasource.

I pass the windows User id of whoever runs the report as a parameter to my SP's and it will do many things like, build values list according to the user's cumulative Windows Group membership and then check again the user membership when executing the main procedure, just to make sure the user receive only the data his membership is allowing him to see. As an example, if the userid is disabled in AD, the report will just return nothing. Then there are tricky cases like The user can see all customers in only one region and one or several specific customers worldwide. The list of intricated cases goes on, some people have dual responsibilities. you yet have to give only the data (and the detail) they are allowed to see.

The weackness of my implementation reside in this very issue, using Windows Integrated security instead of SQL Login in the connection string.

This SQL Mag article also spoke about the same case in the Cube side. Here again, details would be appreciated. On my side, I have a complex list of custom dimension MDX expressions that evaluate the user's Windows Group Membership to enable dimensions levels and/or members. I had a custom DSO application in SQL 2000 that was loading the entire dictionary of custom MDX into the cubes, however it is no longer working in SQL2005.

Then comes the logging. I am covered in that area as far as RS is concerned. I yet have to figure out how I can get there with SSAS. it was impossible with SQL2000 cubes.

I would really love to have access to a technical doc specific to SQL2005, Report Server and SSAS dedicated to the entire security and access food chain, from a real world implementation perspective, a DogFood eat by example if you like :-)

Forums are extremely helpfull, however you have to be very specific, one area at the time, one question at the time. A wide open question like "How do I implement access control in the entire SQL 2005 stack for my business need..." cannot be asked.

I could find plenty of resources for specific technical areas however I could never find any techincal resources devoted to a specific business need like security and Access control accross the ENTIRE stack

Such HORIZONTAL view of a business problem would be just great.

Thanks,

Philippe