locked
Integrating Active Directory with SQL Server RRS feed

  • Question

  • Hi

    I have reports in reporting services which are currently controlled at report level using active directory groups.  Currently users are allowed to see each others areas, but I have been asked to change this so that they cannot do this any more. If I had a generic report (e.g. the area report in the diagram below) currently the area can click on the figure to drill down and see each others breakdown.  The security model I would like to implement would include the North Area only being able to see the North Areas area's data.  I would like to do this using Active directory and somehow integrating it within my SQL and reports.

    I am able to take a download of active drirectory and import it into SQL to use it in query if required, but ideally I would like to link to AD somehow if possible??

    Am I able to write something that would allow me to integrate the specific security group in my report e.g. SELECT * FROM areas WHERE area = 'north' and uid = SUSER_NAME()  or SELECT * FROM areas  WHERE uid = CURRENT_USER and area = 'north'

    I could of course write 4 instances of SQL to pull back a north report, a south report etc, but I do not want to have multiples of the same report as it becomes difficult to manage and maintain.

    Is there a way to paramaterise these reports e.g. have one main area report that you can pass an active directory report parameter to?

    Thank you,

    Thursday, September 27, 2012 9:24 PM

Answers

  • You can use the is_member function to test whether a user is a member of a certain Windows group.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Amy Peng Friday, September 28, 2012 9:27 AM
    • Marked as answer by Maggie Luo Friday, October 5, 2012 1:17 AM
    Thursday, September 27, 2012 9:42 PM
  • is_member is an SQL function. For instance:

    SELECT *
    FROM   areas
    WHERE  area = @area
      and  is_member('DOMAIN\NORTHAREAUSERS') = 1

    or maybe rather

    SELECT *
    FROM   areas
    WHERE  area = @area
      and  is_member(@adgroup_for_area) = 1


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Friday, October 5, 2012 1:17 AM
    Friday, September 28, 2012 9:54 PM
  • Yes, you want need some form of mapping between area and AD group. You could use a CASE expression:

       is_member(CASE @area WHEN 'North area' THEN 'DOMAIN\NORTHAREAUSERS'
                        WHEN 'East area' THEN 'DOMAIN\EASTAREAUSERS'
                         ...
                 END) = 1

    But if there are changes, you need to change the code. So a mapping table is better in the long run.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Friday, October 5, 2012 1:17 AM
    Thursday, October 4, 2012 9:51 PM

All replies

  • You can use the is_member function to test whether a user is a member of a certain Windows group.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Amy Peng Friday, September 28, 2012 9:27 AM
    • Marked as answer by Maggie Luo Friday, October 5, 2012 1:17 AM
    Thursday, September 27, 2012 9:42 PM
  • Hi Erland, thank you for your reply.  How do you integrate the is_member function into your sql?

    e.g. SELECT * FROM areas WHERE area = @area   Do you need to write a User defined function for this that looks up active directory?  Not sure how this fits all fits in.

    Friday, September 28, 2012 9:59 AM
  • is_member is an SQL function. For instance:

    SELECT *
    FROM   areas
    WHERE  area = @area
      and  is_member('DOMAIN\NORTHAREAUSERS') = 1

    or maybe rather

    SELECT *
    FROM   areas
    WHERE  area = @area
      and  is_member(@adgroup_for_area) = 1


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Friday, October 5, 2012 1:17 AM
    Friday, September 28, 2012 9:54 PM
  • Hi Erland, many thanks for your reply.  This is working for me:

    SELECT *
    FROM   areas
    WHERE  area = @area and  is_member('DOMAIN\NORTHAREAUSERS') = 1

    How do I parameterise the is_member section? Basically I would like the user to pick the area, and I can have the domain group automatically populated via a parameter rather than me hard code the domain\nortareausers etc? 

    Do I need to have a seperate table like the following to specify the ad group and have a look up or something to allow me to do this?

    Area Name       adgroup
    North Area       north-data
    East Area         east-data
    West Area        west-data
    South Area       south-data

    Thank you for your expert advice.

    Monday, October 1, 2012 5:12 PM
  • Yes, you want need some form of mapping between area and AD group. You could use a CASE expression:

       is_member(CASE @area WHEN 'North area' THEN 'DOMAIN\NORTHAREAUSERS'
                        WHEN 'East area' THEN 'DOMAIN\EASTAREAUSERS'
                         ...
                 END) = 1

    But if there are changes, you need to change the code. So a mapping table is better in the long run.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Friday, October 5, 2012 1:17 AM
    Thursday, October 4, 2012 9:51 PM