Reporting services 2000 security

Hi folks,
I have been charged with the task of providing some security measures for
reporting services 2000 and unfortuneately I have found myself a bit out of
my depth (usually I am found developing desktop applications), I am
comfortable with any c# coding required but I'm finding the whole thing
rather confusing.
Our setup consists of a webserver and reporting server (on different domains
at the moment) We have a website that works as we wish, displaying a list
of reports based on the users logon details. However we have found tht the
reporting server can be accessed directly and with a bit of playing around
anyone can view (often sensitive) information.
The current site is written in ASP (not .NET), is it possible to 'wrap' the
existing site in an asp.net form so that we can use forms authentication
providing the necessary security to plug the hole?
To maintian useability we require the dropdowns for parameters provided by
URL addressability, so would we need to move the reporting server onto the
webserver so that it would have the same domain?
Any tips or links would be very much appreciated - I feel like a fish out of
water at the moment :)
Regards
Darren SimCrossing domains is an issue.
Assuming on the same domain you should be able to assign domain users or
domain groups to appropriate roles in RS. If they have been assigned
appropriately then they should not be able to see or run reports they
shouldn't. The way it works is that security starts with whatever roles have
been assigned to the home directory/folder in report manager. All
report/folders inherit from this. You can override at either the folder
level or the report level. For instance, if there is folder that only
management should see the reports then in Report Manager remove the group
for browsing for that folder and add one that is just the managers. You can
also do this for individual reports. My guess is that no-one understood how
roles are used to secure the website. Regardless of whether you integrate
this in with your web app, you need to understand this.
Yes you can integrate in your own authentication, many people have. It is
non-trivial but doable. However, if you have it on the same server as your
web app then you can use integrated security, it will know who is connected
to your web app and they will only be able to view the appropriate reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Darren Sim" <darrens@.sirius.co.uk> wrote in message
news:%234FzKGhSHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Hi folks,
> I have been charged with the task of providing some security measures for
> reporting services 2000 and unfortuneately I have found myself a bit out
> of my depth (usually I am found developing desktop applications), I am
> comfortable with any c# coding required but I'm finding the whole thing
> rather confusing.
> Our setup consists of a webserver and reporting server (on different
> domains at the moment) We have a website that works as we wish,
> displaying a list of reports based on the users logon details. However we
> have found tht the reporting server can be accessed directly and with a
> bit of playing around anyone can view (often sensitive) information.
> The current site is written in ASP (not .NET), is it possible to 'wrap'
> the existing site in an asp.net form so that we can use forms
> authentication providing the necessary security to plug the hole?
> To maintian useability we require the dropdowns for parameters provided by
> URL addressability, so would we need to move the reporting server onto
> the webserver so that it would have the same domain?
> Any tips or links would be very much appreciated - I feel like a fish out
> of water at the moment :)
> Regards
> Darren Sim
>|||Thanks Bruce your help is much appreciated,
Just to clarify, at the moment our web app accepts a login from a user then
checks the details against a database, the returned values determine which
reports are made available and the links to these are then displayed on a
page.
If we were to move the reporting server onto our web server, would it just
be a case of turning on integrated security on the report server?
Thanks again
Darren
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%235q3hphSHHA.4632@.TK2MSFTNGP04.phx.gbl...
> Crossing domains is an issue.
> Assuming on the same domain you should be able to assign domain users or
> domain groups to appropriate roles in RS. If they have been assigned
> appropriately then they should not be able to see or run reports they
> shouldn't. The way it works is that security starts with whatever roles
> have been assigned to the home directory/folder in report manager. All
> report/folders inherit from this. You can override at either the folder
> level or the report level. For instance, if there is folder that only
> management should see the reports then in Report Manager remove the group
> for browsing for that folder and add one that is just the managers. You
> can also do this for individual reports. My guess is that no-one
> understood how roles are used to secure the website. Regardless of whether
> you integrate this in with your web app, you need to understand this.
> Yes you can integrate in your own authentication, many people have. It is
> non-trivial but doable. However, if you have it on the same server as your
> web app then you can use integrated security, it will know who is
> connected to your web app and they will only be able to view the
> appropriate reports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Darren Sim" <darrens@.sirius.co.uk> wrote in message
> news:%234FzKGhSHHA.4756@.TK2MSFTNGP06.phx.gbl...
>> Hi folks,
>> I have been charged with the task of providing some security measures for
>> reporting services 2000 and unfortuneately I have found myself a bit out
>> of my depth (usually I am found developing desktop applications), I am
>> comfortable with any c# coding required but I'm finding the whole thing
>> rather confusing.
>> Our setup consists of a webserver and reporting server (on different
>> domains at the moment) We have a website that works as we wish,
>> displaying a list of reports based on the users logon details. However
>> we have found tht the reporting server can be accessed directly and with
>> a bit of playing around anyone can view (often sensitive) information.
>> The current site is written in ASP (not .NET), is it possible to 'wrap'
>> the existing site in an asp.net form so that we can use forms
>> authentication providing the necessary security to plug the hole?
>> To maintian useability we require the dropdowns for parameters provided
>> by URL addressability, so would we need to move the reporting server
>> onto the webserver so that it would have the same domain?
>> Any tips or links would be very much appreciated - I feel like a fish out
>> of water at the moment :)
>> Regards
>> Darren Sim
>|||Well, it sounds like your web app is not using integrated security. There
are two steps to security. One is authenticating who the user is. That can
be done with forms authentication (in which case you integrate in the
authentication from your web app with RS). Then the other is roles. If you
don't do the roles then the user can go directly to Report Manager web site
and get whatever report they want. So regardless of how you decide to
authenticate you need to still understand and use roles in RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Darren Sim" <darrens@.sirius.co.uk> wrote in message
news:uPcxjxpSHHA.3948@.TK2MSFTNGP05.phx.gbl...
> Thanks Bruce your help is much appreciated,
> Just to clarify, at the moment our web app accepts a login from a user
> then checks the details against a database, the returned values determine
> which reports are made available and the links to these are then displayed
> on a page.
> If we were to move the reporting server onto our web server, would it just
> be a case of turning on integrated security on the report server?
> Thanks again
> Darren
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%235q3hphSHHA.4632@.TK2MSFTNGP04.phx.gbl...
>> Crossing domains is an issue.
>> Assuming on the same domain you should be able to assign domain users or
>> domain groups to appropriate roles in RS. If they have been assigned
>> appropriately then they should not be able to see or run reports they
>> shouldn't. The way it works is that security starts with whatever roles
>> have been assigned to the home directory/folder in report manager. All
>> report/folders inherit from this. You can override at either the folder
>> level or the report level. For instance, if there is folder that only
>> management should see the reports then in Report Manager remove the group
>> for browsing for that folder and add one that is just the managers. You
>> can also do this for individual reports. My guess is that no-one
>> understood how roles are used to secure the website. Regardless of
>> whether you integrate this in with your web app, you need to understand
>> this.
>> Yes you can integrate in your own authentication, many people have. It is
>> non-trivial but doable. However, if you have it on the same server as
>> your web app then you can use integrated security, it will know who is
>> connected to your web app and they will only be able to view the
>> appropriate reports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Darren Sim" <darrens@.sirius.co.uk> wrote in message
>> news:%234FzKGhSHHA.4756@.TK2MSFTNGP06.phx.gbl...
>> Hi folks,
>> I have been charged with the task of providing some security measures
>> for reporting services 2000 and unfortuneately I have found myself a bit
>> out of my depth (usually I am found developing desktop applications), I
>> am comfortable with any c# coding required but I'm finding the whole
>> thing rather confusing.
>> Our setup consists of a webserver and reporting server (on different
>> domains at the moment) We have a website that works as we wish,
>> displaying a list of reports based on the users logon details. However
>> we have found tht the reporting server can be accessed directly and with
>> a bit of playing around anyone can view (often sensitive) information.
>> The current site is written in ASP (not .NET), is it possible to 'wrap'
>> the existing site in an asp.net form so that we can use forms
>> authentication providing the necessary security to plug the hole?
>> To maintian useability we require the dropdowns for parameters provided
>> by URL addressability, so would we need to move the reporting server
>> onto the webserver so that it would have the same domain?
>> Any tips or links would be very much appreciated - I feel like a fish
>> out of water at the moment :)
>> Regards
>> Darren Sim
>>
>|||Thanks again Bruce
You are quite correct - the website set up for viewing reports does not use
integrated security. I have been looking at integrating forms
authentication, but hit a few snags - I think most of the problems are
simply down to a poor understanding of the RS configfiles and what needs to
be done.
We are also moving the reporting services on to the web server to address
the domain issue - I have been thinking of redoing the login page in .NET to
handle forms security, if the login has been successful then allow the user
to proceed to the current site.
At the moment I am using VS2005 and .NET 2.0 for desktop applications, for
RS200 do I neet to use .NET1.1 and VS2003? (I have access to both)
I will read up on roles to get a better understanding of what needs to be
done to enchance security.
Kind regards
Darren Sim
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e9k5iLuSHHA.1228@.TK2MSFTNGP06.phx.gbl...
> Well, it sounds like your web app is not using integrated security. There
> are two steps to security. One is authenticating who the user is. That can
> be done with forms authentication (in which case you integrate in the
> authentication from your web app with RS). Then the other is roles. If you
> don't do the roles then the user can go directly to Report Manager web
> site and get whatever report they want. So regardless of how you decide to
> authenticate you need to still understand and use roles in RS.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>|||Yes, RS 2000 is a 1.1 application. If your web site is 2.0 it can also run
1.1 for the RS websites.
As a 1.1 application you need to install VS 2003 and then install the report
designer.
If at all possible I would look at upgrading to RS 2005. It uses 2.0. It is
faster and has some important features (end user sorting, multi-select
parameters, calendar control to name a few).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Darren Sim" <darrens@.sirius.co.uk> wrote in message
news:%23caBKv3SHHA.1200@.TK2MSFTNGP04.phx.gbl...
> Thanks again Bruce
> You are quite correct - the website set up for viewing reports does not
> use integrated security. I have been looking at integrating forms
> authentication, but hit a few snags - I think most of the problems are
> simply down to a poor understanding of the RS configfiles and what needs
> to be done.
> We are also moving the reporting services on to the web server to address
> the domain issue - I have been thinking of redoing the login page in .NET
> to handle forms security, if the login has been successful then allow the
> user to proceed to the current site.
> At the moment I am using VS2005 and .NET 2.0 for desktop applications, for
> RS200 do I neet to use .NET1.1 and VS2003? (I have access to both)
> I will read up on roles to get a better understanding of what needs to be
> done to enchance security.
> Kind regards
> Darren Sim
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e9k5iLuSHHA.1228@.TK2MSFTNGP06.phx.gbl...
>> Well, it sounds like your web app is not using integrated security. There
>> are two steps to security. One is authenticating who the user is. That
>> can be done with forms authentication (in which case you integrate in the
>> authentication from your web app with RS). Then the other is roles. If
>> you don't do the roles then the user can go directly to Report Manager
>> web site and get whatever report they want. So regardless of how you
>> decide to authenticate you need to still understand and use roles in RS.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>|||We are upgrading to sql 2005 and RS2005. This is only to act as a stop-gap
until this can be properly planned and carried out as having the reporting
server accessible by anyone obviously poses a security threat.
Unfortuneately this security hole was only discovered at the last minute
before making the service available to our clients. I have only been here
for 6 months and I'm pushing for all developement to be on the .net 2.0
framework, as I use this for the desktop applications and some of the code I
am using could be reused in the web applications. However we are a very
small operation and getting everyone up to speed takes time, not to mention
the task of doing everything over...
Thanks very much for your assistance Bruce, I think I have an idea of at
least where to begin.
Regards
Darren
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OqLkrJ4SHHA.4632@.TK2MSFTNGP04.phx.gbl...
> Yes, RS 2000 is a 1.1 application. If your web site is 2.0 it can also run
> 1.1 for the RS websites.
> As a 1.1 application you need to install VS 2003 and then install the
> report designer.
> If at all possible I would look at upgrading to RS 2005. It uses 2.0. It
> is faster and has some important features (end user sorting, multi-select
> parameters, calendar control to name a few).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Darren Sim" <darrens@.sirius.co.uk> wrote in message
> news:%23caBKv3SHHA.1200@.TK2MSFTNGP04.phx.gbl...
>> Thanks again Bruce
>> You are quite correct - the website set up for viewing reports does not
>> use integrated security. I have been looking at integrating forms
>> authentication, but hit a few snags - I think most of the problems are
>> simply down to a poor understanding of the RS configfiles and what needs
>> to be done.
>> We are also moving the reporting services on to the web server to address
>> the domain issue - I have been thinking of redoing the login page in .NET
>> to handle forms security, if the login has been successful then allow the
>> user to proceed to the current site.
>> At the moment I am using VS2005 and .NET 2.0 for desktop applications,
>> for RS200 do I neet to use .NET1.1 and VS2003? (I have access to both)
>> I will read up on roles to get a better understanding of what needs to be
>> done to enchance security.
>> Kind regards
>> Darren Sim
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:e9k5iLuSHHA.1228@.TK2MSFTNGP06.phx.gbl...
>> Well, it sounds like your web app is not using integrated security.
>> There are two steps to security. One is authenticating who the user is.
>> That can be done with forms authentication (in which case you integrate
>> in the authentication from your web app with RS). Then the other is
>> roles. If you don't do the roles then the user can go directly to Report
>> Manager web site and get whatever report they want. So regardless of how
>> you decide to authenticate you need to still understand and use roles in
>> RS.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>sql

Reporting Services - Only Measures on Column axis

Hi,

I'm currently using AS 2005 and Reporting Services.

I have some results of a query split into age buckets (0-90 days, 91-180 days, etc). I would like to display the buckets, whether there are results or not as I am using them in the column . If I do not include "Non Empty", it returns a huge number of records because there is a cross join with another dimension which does not need to include all records.
For example, here is my query:

SELECT NON EMPTY { [Measures].[To Bill Amount] } ON COLUMNS,

NON EMPTY { (

[Product].[Product Name].[Product Name].ALLMEMBERS*

[Transaction Date].[Age Buckets].[Age Buckets].ALLMEMBERS ) }

ON ROWS

FROM ( SELECT ( { [Client].[Client Key].&[2] } ) ON COLUMNS

FROM [Cube])

WHERE ( [Client].[Client Key].&[2])

So, I would like to include all [Transaction Date].[Age Buckets].[Age Buckets] but only the Products that fall under the Client Key. Another thing to note is that Reporting Services seems to only allow measures on the Column axis.

Any help would be great.

Thanks.

Hi Dear

In SSRS try to make MDX in Such a way that result should look like Report output. By doing that U will save the design time as well as Report level calculation, I hope by doing this your report will be little bit faster. And see the functions like STRTOMEMBER, STRTOSET etc. You can use here Peramaters also. Using peramaters you will add a lot values in your reports. In your example I donnot know excatly how data U R storing in Age bukets. If there is only Age then make Calculated Members Or if there is age bucket in the desired format like [0-90 Days] etc. then use only U needed Buckets. Hope U will get some help.

WITH

MEMBER [Transaction Date].[Age Buckets].[0-90 days] AS

CASE WHEN

[Transaction Date].[Age Buckets].[Age Buckets] <= 90

THEN [Transaction Date].[Age Buckets].[Age Buckets]

ELSE NULL END

MEMBER [Transaction Date].[Age Buckets].[91-180 days] AS

CASE WHEN

[Transaction Date].[Age Buckets].[Age Buckets] > 90

AND [Transaction Date].[Age Buckets].[Age Buckets] <= 180

THEN [Transaction Date].[Age Buckets].[Age Buckets]

ELSE NULL END

SELECT

NON EMPTY

{[Measures].[To Bill Amount]} ON COLUMNS,

{

(

{[Product].[Product Name].[Product Name]}

*

{

[Transaction Date].[Age Buckets].[0-90 days],

[Transaction Date].[Age Buckets].[91-180 days]

}

)

}

ON ROWS

FROM (SELECT({[Client].[Client Key].&[2]}) ON COLUMNS

FROM [Cube])

WHERE ( [Client].[Client Key].&[2])

sql

Reporting Service MDX problem

Hi all,
I am using Analysis Services cubes as backend to my Reporting Service
reports currently. I have a situation where I select
measures in axis 0
and
a heirarchy members & a level(in another heirarchy) in axis 1.
Now the result set will look like...
heiarchymember1 heirarchymember2 heirarchymember3 level measure
-- -- --
-- --
but my report requires the values of level column above to be column names
and the measure value to be values for those columns. In simple words I want
to transpose a column to rows and measure values should appear as data under
those transposed columns.
I cannot make use of matrix dataregion to do this as I have other
restriction. I can use only table dataregion. Is there a way (say some
function) available in mdx to do this kind of transpose with cube data in
reporting services.
Adventure works query which is good to explain my situation is
SELECT [Measures].[Total Product Cost] ON COLUMNS,
{([Product].[Product Categories].MEMBERS,
[Geography].[Geography].[Country].MEMBERS)} ON ROWS
FROM [Adventure Works]
Pl run the above query from reporting service report builder because if you
run in management studio u will see in different format.
Now when you run the above query in RS you can see Category, SubCategory,
ProductName, Country,Total Product Cost columns. What I want is
Category, SubCategory, ProductName,country1,country2,... as columns and the
relevant Total Product Cost measure values under the country names as data.
If it is sql I can use Case statement to do this kind of transposing. But
here in reporting service MDX we can only use measures no other dimension in
axis 0, which actually leads to this kind of transposing requirements.
Pl let me know what is the best way to accomplish this.
ThanksHi Wei Lu,
Since our reporting requirements are such that we cannot use matrix and we
have to use table with different groups all placing one below the other, I
tried a different solution. I found somewhere that If I make use of OLEDB
provider instead of MS Analysis Services Provider so that I dont have
restriction to put only Measures on columns axis. Now my question is what is
flexibility that I loose if I use oledb provider for analysis services 9.0
than Microsoft Analysis services provider?
Thanks
"Wei Lu [MSFT]" wrote:
> Hi ringt,
> Thank you for your posting!
> From your description, my understanding of this issue is: You want to use
> the MDX in the report and you want to show the heirarchymember in the
> column. If I misunderstood your concern, please feel free to let me know.
> I suggest you use the Matrix control in your report to show the data.
> The attachment is the Report I designed using the Matrix. Please check and
> let me know whether this meet your requirement.
> The datasource is the Adventure Works and the MDX query is the one you
> posted.
> I put the Country in the column group and Total Product Costs in the
> Detail, others in the Row group.
> Please let me know the result so that I can provide further assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights|||Hi ringt,
Thank you for the update.
Based on my research, this issue is by design. Although the MDX you want to
use Multi Dimensions in the columns axis could run in the Analysis
Services, the data is extracted as a flattened row-set and can be used by
the report builder only as a row-set. So the Report Builder will not care
about certain things related to OLAP.
I suggest you use the OLEDB for AS 9.0 driver so that you could add Multi
Dimensions in the columns axis.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Ringt,
How are you doing on this issue, does Wei's last reply help clarify the
problem further or have you got further progress on this? If there is still
anything we can help, please feel free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.