January 27, 2011 at 5:30 am
Good Morning all.
So the company now likes SSRS and wants to move it forward. (3 years of battle and I won! -- or did I?)
BackGround:
Users are set up in a windows group.
The windows group controls access to the reports.
Windows Authentication is used.
But now these users can build an ODBC driver to the database and start pulling all kinds of data. I don't want to have a thousand groups that have access to specific tables (That gives me a headache just thinking about it.)
Is there no way for Billy McUser to view reports via SSRS on the web but not be able to use an odbc to connect to the database?
Any articles or thoughts would be greatly appreciated.
Thanks.
Doug (Cold in Florida)
January 27, 2011 at 6:29 am
In our implementation of SSRS we use a separate, hard-coded sql server login in the data source for reports to run through, that regular uses dont have access to, and then manage user access to the reports inside SSRS using AD. We have had very much success with this implementation method.
If you want more description on the implementation I'll try to provide you with more examples.
January 27, 2011 at 6:44 am
Yes please. Any examples or best practices would be great.
Thanks so much.
Doug
January 27, 2011 at 8:30 am
I don't know if our implementation would necessarily fall under "Best Practices", but it does prevent us from managing hundreds of individual sql server logins based off of people's individual logins, and through this method, no end-user has the ability to even write a query, but merely view reports.
SSRS is set up to allow Windows Authentication. When they first visit the site, they are prompted for their windows login. All the domain users have "Browser" roles by default.
I created a datasource pointing at the sql server that uses a sql server login. Under the properties of the datasource, I checked "Credentials stored securely in the report server" and punched in the userid and password there. I do not check "Use as windows credentials" or "Impersonate authenticated user". All reports are based off of this datasource or a datasource set up similarly. When reports run, they run under that sql server login.
Users are then granted permissions to the individual folders in ssrs. Permissions for all domain users are removed from important folders like "Data Sources", and "Models", and other such folders that would give the user access to the sql server login info.
A small group of the users are "report writers". Although the report writers need elevated permissions so they can write queries and upload reports to ssrs, their reports are limited to a "test reports" folder. From there, once the report is authored, I the DBA "ok" the query/report and move the report out of the test reports folder and into its correct location. Doing this method has an added benefit. When I go to any given report and look at the "created by" property, it still shows the original creator who made the report in "test reports".
Another handy "practice" we utilize is since our company has a sort of "case" or "bug" tracking system, I require the author to enter the case# of the original request into the description property of the report. This allows me to track back the original use case for a given report in the case it needs to be modified.
At first the end users and report authors were very opposed to this implementation and all of its "annoying extras" i made them do. But when a report's query brings the sql server to a grinding halt, i can pull up the report responsible (with the help of the executionlog2 view in the "ReportServer" database), sit down with the author and work toward a solution that doesn't kill the database and still satisfies the user's original request.
This SSRS implementation has been live for 2 years now, with great success.
Are there any specific questions you have about this?
January 27, 2011 at 9:06 am
This sounds like it could be my ticket out of troublesville.
How do they log on to the web site with their windows account? Is this something that I can use with the authentication or do you have this pointing to some other table?
Right now Billy User is in windows group USERS\USERS.
So lets say I create a log in and password and only that has access to the database. All queries run with that loggon.
How do I get USERS\USERS to access the website only and not the Database. Does USERS\USERS not need to be listed anywhere in the security part of SQL Server? I have played but maybe i am missing some property somewhere.
Does this make sense? Sorry to be so slow but security is not my strong point and to be honest it was never an issue as only 5 of us ever used this.
Doug
February 9, 2011 at 1:58 pm
Hey, sorry for the *very* late response, i was out of town...
You grant the domain user group (in this case USERS\USERS) permission to the "Home" folder inside SSRS. when a given user hits the SSRS web interface (typically http://<ssrs_server_name>/reports), they will be prompted to enter their windows credentials. once they do that they will be allowed access to the home folder. you set up folders inside SSRS with varying levels of permission per user from there.
You can fine-tune the permissions of a given windows login from there by logging into the report server using SSMS, and looking at "Roles". check it out when you get a chance. In my case, I adjusted the "Browser" role to only be able to run a published report. they can't even open the properties of the report. I grant additional access where necessary.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply