SQL SSRS Security

[JV_011_]

Hi guys, is it possible to implement SSRS security using data in a specific table. Example: i have a list of user account on a specific table that i manage and the users are authenticated against that table.

 

[Necropolis]

I don't think you can out of the box.

I've only ever used AD for SSRS authentication...

 

[Fuma]

I suppose you can use a script task as a first step of your workflow to run some c#/vb.net code to access the db table. Hopefully your userid in the table is the same as the AD's

So your script task will have something like this:
var users = SomeDBMethod().Where(u=>u.UserId = new WindowsPrincipal(WindowsIdentity.GetCurrent()).Identity.Name);
This could be a crappy idea. Only used SSIS for a month now.

 

[Kosmik]

Sql enterprise allows data driven subscriptions, might have something for you but its expensive.

 

[Pho3nix]

http://sqlmag.com/sql-server-reporting-services/ssrs-how-create-reports-user-protect-sensitive-data

Google is your friend.

 

[Pho3nix]

I suppose you can use a script task as a first step of your workflow to run some c#/vb.net code to access the db table. Hopefully your userid in the table is the same as the AD's

So your script task will have something like this:
var users = SomeDBMethod().Where(u=>u.UserId = new WindowsPrincipal(WindowsIdentity.GetCurrent()).Identity.Name);
This could be a crappy idea. Only used SSIS for a month now.

SSRS.
You can add code to reports but you will make them a lot more clunky.

OT : How you finding SSIS?

 

[Fuma]

SSRS.
You can add code to reports but you will make them a lot more clunky.

OT : How you finding SSIS?

Lol. Eish. I had a brain fart there.

I can't say I like or hate it. It is just that there are too many projects that I'm working o that are using SSIS for almost everything.

 

[prod]

The way we did it was to let SSRS create its default security objects for a user (check out dbo.ReportServer) and then 'map' the SSRS UserId to a UserId in the tables we have full control over (for our applications etc.) Not ideal but with around 60 users that run 80-100 different reports it'll be a ****show re-coding the security.

 

[Pho3nix]

The way we did it was to let SSRS create its default security objects for a user (check out dbo.ReportServer) and then 'map' the SSRS UserId to a UserId in the tables we have full control over (for our applications etc.) Not ideal but with around 60 users that run 80-100 different reports it'll be a ****show re-coding the security.

Question.. How would you get the users to only see parameters for their relevant department, division etc.

 

[prod]

Question.. How would you get the users to only see parameters for their relevant department, division etc.

We didn't have to do that thankfully - reports are grouped into Department specific folders and permissions were then set per item.

 

[Pho3nix]

We didn't have to do that thankfully - reports are grouped into Department specific folders and permissions were then set per item.

Boo! Saw a request for that on Jira earlier, thought you'd be giving me a cheat sheet

 

[prod]

Boo! Saw a request for that on Jira earlier, thought you'd be giving me a cheat sheet

Sorry

On the other hand it's fun bending SSRS to heed your will using some obscure technique, have fun

 

[halfmoonforever]

You can, I did it at my previous employer. It's a drop in DLL on the SSRS server, and a change in the .config to make it register the file, it then "hijacks" (my word, lol) the http request to SSRS, where I then do a quick authentication and if the user does have access to that report, is allowed to see it, otherwise I redirect to another report that's an "application" for authentication.

I used this as the basis of what I wanted to achieve:

There are several ways to link it to Report Manager. To keep it simple, I built the code as a .signed NET 2.0 Framework assembly (it must not use a newer version than Reporting Services), copied it to the GAC, and added the following to the beginning of the <httpModules> section of the Report Manager web.config:

<add name="RSRedirect" type="RSRedirect.SslSwitchModule, RSRedirect, Version=1.0.0.0, Culture=neutral, PublicKeyToken=06a567492fe7ae71"/>

You would have to change PublicKeyToken to the new value for your build. If you drag the DLL to the GAC window (a quick way to open it is Start->Run, and type "assembly"), the correct version and PublicKey Token will be shown. My web.conifg is at C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager. The _50 is SQL Server 2008 R2.

The advantages of placing the code in the GAC are that it doesn't seem to be necessary to modify the RM security configuration when it is loaded from there, and the same copy can be used with Report Server if you need to do that. I haven't tried changing the RS web.config to install the module, but the process would be the same.

I was having a problem with WIndows prompting for my user name and password. Not always, but often enough to be annoying. When I placed the new module at the beginning of <httpModules> the problem seemed to go away, but it will need more testing.

using System;
using System.Collections.Generic;
using System.Web;

namespace RSRedirect
{
    /// <summary>
    /// Adapted from code published on Nikhil's Blog at blogs.msdn.com
    /// </summary>
    public class SslSwitchModule : IHttpModule
    {
        //store your secure pages in a hastable for fast retrieval.
        //this can be populated when the application starts up so that repeated 
        // overhead is avoided.
        private static System.Collections.Generic.Dictionary<String, Object> SecurePages = null;

        public void ProcessRequest(HttpContext context)
        {
            //if the request is for HTTP, check if HTTPS is needed
            if (!context.Request.IsSecureConnection)
            {
                string urlRequested = HttpUtility.UrlDecode(context.Request.Path.Substring(context.Request.ApplicationPath.Length));    //Remove application from path

                if (SecurePages.ContainsKey(urlRequested.ToLower()))
                {
                    //switch to HTTPS
                    UriBuilder ub = new UriBuilder(context.Request.Url);
                    ub.Scheme = "https";
                    ub.Port = -1;
                    context.Response.Redirect(ub.ToString());
                }
            }
        }

        #region IHttpModule Members

        public void Dispose()
        {
            ;
        }

        public void Init(HttpApplication context)
        {
            SecurePages = new Dictionary<String, Object>();
            SecurePages.Add("/pages/folder.aspx", null);
            SecurePages.Add("/", null);
            SecurePages.Add("", null);
            // wireup the event for processing
            context.PreRequestHandlerExecute += new EventHandler(context_PreRequestHandlerExecute);
        }

        void context_PreRequestHandlerExecute(object sender, EventArgs e)
        {
            HttpApplication httpApp = (HttpApplication)sender;
            //process the request
            this.ProcessRequest(httpApp.Context);
        }

        #endregion
    }
}

That way you never worry about user security in each and every report

 

[halfmoonforever]

Question.. How would you get the users to only see parameters for their relevant department, division etc.

Microsoft CRM has an interesting way of doing it which we adopted in our own stuff. We wrote views that has user security built into it, and whenever the SSRS report queries the view, it passes through the AD user as well.

select * from stuff
inner join user_security...
where user = 'xyz'

We do that for everything, even look-ups, and our security model can give security to most everything, so we can restrict user x to only 1 department, or several. As long as the SSRS report, by default, sends through the AD user as a parameter to the view (and the views follow a set standard when developed) everything runs nice and smooth

 

[Pho3nix]

Microsoft CRM has an interesting way of doing it which we adopted in our own stuff. We wrote views that has user security built into it, and whenever the SSRS report queries the view, it passes through the AD user as well.

select * from stuff
inner join user_security...
where user = 'xyz'

We do that for everything, even look-ups, and our security model can give security to most everything, so we can restrict user x to only 1 department, or several. As long as the SSRS report, by default, sends through the AD user as a parameter to the view (and the views follow a set standard when developed) everything runs nice and smooth

Thanks mate. Thought about it and your logic is sound.
Difference is application security is based on Domain name ie: "Doe, John J" so will be hitting a SharePoint web-service to get the users domain\id details and join the views.