Item-Level Security for Reports

Hello,

Can somebody explain the process for implementing Item-Level security for reports? My requirement is as follows:

I have some reports pertaining to a specific department, where in I deployed them to a specific folder on Reporting Services. Now I need to give exclusive read access to those reports for the users in that department. They should not be able to create any folders / new reports etc.

The steps I have followed:

1. Deployed the reports from VS 2005 to a specific folder called "TheirReports" and in this I placed the Data source also as a seperate folder.

2. I clicked on Site Settings in Report Manager and then clicked on Configure Item-Level role definitions.

3. Clicked on New Role and gave a name as ReportsBrowser and checked the options --> "View Data sources / View folders / View reports / View resources"

4. Now I went back to the specific folder and clicked on Properties -> Security -> New Role Assignment.

5. Added the necessary users binding them to ReportsBroser role.

When I'm checking from the user machine, they are able to create new folders etc.

Appreciate your ideas.

Thanks

Nobody has dealt so far with Item-Level security for reports.sql

Issues with LEFT join on four tables

Hi Folks,
Lets assume I have three tables. Their layout is as follows. Please
note that tblPeople does not have an entry for Denver (this is my
problem)

tblCity
_________________
CityName OCID
LA 1
Denver 2

tblCars
_________________
OCID CarVolume
1 300,000
2 200,000

tblPeople
_________________
OCID PeopleVolume
1 1,200,345

tblDogs
_________________
OCID DogVolume
1 234,987
2 445,987

I'd like to run a quiery that returns the following data set:

CityName OCID CarVolume PeopleVolume DogVolume
LA 1 300000 1200345 234987
Denver 2 200000 null or 0 445997

My problem is the people table. Since there not entry for Denver, my
query is returning only a row for LA, nothing for Denver. Here's wht
my query looks like

Select tblCity.CityName, tblCity.OCID, tblCars.CarVolume,
tblPeople.PeopleVolume, tblDogs.DogVolume
FROM tblCity
LEFT JOIN tblCars on tblCity.OCID = tblCars.OCID
JOIN tblPeople on tblCars.OCID = tblPeople.OCID
JOIN tblDogs on tblPeople.OCID = tblDogs.OCID

This returns the following:
CityName OCID CarVolume PeopleVolume DogVolume
LA 1 300000 1200345 234987

I need this query to return a row for Denver as well. Any thoughts
anyone?
Your insight is greatly appreciated. ericlangland at hotmail.comEric,

If tblCity is your base (or primary) table, then you should be able to do
the following without problems. It appears you are very close to this
already.

select ci.ocid, ci.cityname, ca.carvolumn, p.peoplevolumn, d.dogvolumn
from tblcity ci
left join tblcars ca on ci.ocid = ca.ocid
left join tblpeople p on ci.ocid = p.ocid
left join tbldogs d on ci.ocid = d.ocid

HTH,

Greg
"Eric" <ericlangland@.hotmail.com> wrote in message
news:5372437.0403041742.6259dfad@.posting.google.co m...
> Hi Folks,
> Lets assume I have three tables. Their layout is as follows. Please
> note that tblPeople does not have an entry for Denver (this is my
> problem)
> tblCity
> _________________
> CityName OCID
> LA 1
> Denver 2
> tblCars
> _________________
> OCID CarVolume
> 1 300,000
> 2 200,000
> tblPeople
> _________________
> OCID PeopleVolume
> 1 1,200,345
> tblDogs
> _________________
> OCID DogVolume
> 1 234,987
> 2 445,987
> I'd like to run a quiery that returns the following data set:
> CityName OCID CarVolume PeopleVolume DogVolume
> LA 1 300000 1200345 234987
> Denver 2 200000 null or 0 445997
> My problem is the people table. Since there not entry for Denver, my
> query is returning only a row for LA, nothing for Denver. Here's wht
> my query looks like
> Select tblCity.CityName, tblCity.OCID, tblCars.CarVolume,
> tblPeople.PeopleVolume, tblDogs.DogVolume
> FROM tblCity
> LEFT JOIN tblCars on tblCity.OCID = tblCars.OCID
> JOIN tblPeople on tblCars.OCID = tblPeople.OCID
> JOIN tblDogs on tblPeople.OCID = tblDogs.OCID
> This returns the following:
> CityName OCID CarVolume PeopleVolume DogVolume
> LA 1 300000 1200345 234987
> I need this query to return a row for Denver as well. Any thoughts
> anyone?
> Your insight is greatly appreciated. ericlangland at hotmail.com

Issue with SqlCeParameter (SqlCE 3.1)

Hi,

I have a simple query as follows:

SELECT COUNT(ID) FROM AI_DTREE DT WHERE PARENT =@.pPID AND

CARTRIDGE_ID = @.pCID AND COMMAND =@.pCMD AND OBJECT =@.pObj

Where @.pPID=16700130,@.pCID=43000000,@.pCMD=”=”, and

@.pObj=”the cecum, identified by appendiceal orifice & IC valve”

The filed OBJECT in AI_DTREE is of nvarchar(30)(of course the length of @.pObj is more than 30 in my current query).

I have build the SqlCeCommand sccmd object with the above sql text and the parameters.

returnval = sccmd.ExecuteScalar();

When I execute the above statement I am getting the following error:

ex.Message = "@.pObj : String truncation: max=30, len=55, value='the cecum, identified by appendiceal orifice & IC valve'."

But when I execute the same in Sql Server Management Studio against SqlCE db, it works fine and the result returnval =0.

How to overcome this SqlCeParameter issue?, for me it is difficult to messure the length of the filed before I exeuting the command.

Thanks

G Sreenaiah

You must ensure that the length of @.pObj is no more than 30 chars, or make the field bigger!|||

Hi Erik,
Thanks for your prompt reply.

I am just executing the above command from my C# code. If I want to ensure the length of the parameter is less than the length of field, then I should go for one more database hit to fetch field’s length first.

This is not the way happening with i) OleDbCommand, OleDbParameter, ii) OracleParameter, OracleCommand

Please let me know if anybody else has an idea how this can be resolved with workout having one more db hit to fetch fields’ length.

Why it is happening only with SqlCeCommand, SqlCeParameter? why not with OracleParameter, OracleCommand ?

Thanks
G Sreenaiah

Issue in Custom Security Extension

Hi,

The custom security extension generates the following error, when try to access Report Manager. The specifications are as follows.
1. Its 64-bit SQL Server (Reporting Services) and installed as default instance.
2. The Report Server is accessible programmatically. I can publish, view reports without problems, but Report Manager gives an error.
3. Another instance (named instance) of Reporting Services with the same custom security extension on same server is working fine. Report Manager opens up without problems.
4. Both the instances working fine since 3 installation and configuration (3 months back). Now, only the Report Manager of the default instance is not openeing up.
ReportServerWebApp__08_22_2006_08_52_20.log
-
w3wp!ui!1!8/22/2006-08:52:56:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: The report server is not responding. Verify that the report server is running and can be accessed from this computer.
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: HTTP status code --> 500
-Details--
Microsoft.ReportingServices.UI.Global+RSWebServiceWrapper+CantCommunicateWithReportServerException: The report server is not responding. Verify that the report server is running and can be accessed from this computer.

at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()

at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)

at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()

at Microsoft.ReportingServices.UI.Global.SecureAllAPI()

at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)

at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)

at System.EventHandler.Invoke(Object sender, EventArgs e)

at System.Web.UI.Control.OnInit(EventArgs e)

at System.Web.UI.Page.OnInit(EventArgs e)

at System.Web.UI.Control.InitRecursive(Control namingContainer)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!1!8/22/2006-08:52:57:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:58:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

The service is running fine. Any suggestions/pointers would be greatly appreciated.

THanks in advance

Is there anyway to trace and find the error. Its URGENT!!! Can anyone suggest ?|||

Does anyone has the solution of above problem ? It's urgent.

|||

The error indicates report manager has problem talking to report server soap endpoint. Please check RSWebApplication.config under report manager to make sure either ReportServerUrl or ReportServerVirtualDirectory contains the correct value.

If you think you have the right value in config file, you can install a http tracer between report manager and report server so you can really see the traffic between them.

Issue in Custom Security Extension

Hi,

The custom security extension generates the following error, when try to access Report Manager. The specifications are as follows.
1. Its 64-bit SQL Server (Reporting Services) and installed as default instance.
2. The Report Server is accessible programmatically. I can publish, view reports without problems, but Report Manager gives an error.
3. Another instance (named instance) of Reporting Services with the same custom security extension on same server is working fine. Report Manager opens up without problems.
4. Both the instances working fine since 3 installation and configuration (3 months back). Now, only the Report Manager of the default instance is not openeing up.
ReportServerWebApp__08_22_2006_08_52_20.log
-
w3wp!ui!1!8/22/2006-08:52:56:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: The report server is not responding. Verify that the report server is running and can be accessed from this computer.
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: HTTP status code --> 500
-Details--
Microsoft.ReportingServices.UI.Global+RSWebServiceWrapper+CantCommunicateWithReportServerException: The report server is not responding. Verify that the report server is running and can be accessed from this computer.

at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()

at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)

at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()

at Microsoft.ReportingServices.UI.Global.SecureAllAPI()

at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)

at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)

at System.EventHandler.Invoke(Object sender, EventArgs e)

at System.Web.UI.Control.OnInit(EventArgs e)

at System.Web.UI.Page.OnInit(EventArgs e)

at System.Web.UI.Control.InitRecursive(Control namingContainer)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!1!8/22/2006-08:52:57:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:58:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

The service is running fine. Any suggestions/pointers would be greatly appreciated.

THanks in advance

Is there anyway to trace and find the error. Its URGENT!!! Can anyone suggest ?|||

Does anyone has the solution of above problem ? It's urgent.

|||

The error indicates report manager has problem talking to report server soap endpoint. Please check RSWebApplication.config under report manager to make sure either ReportServerUrl or ReportServerVirtualDirectory contains the correct value.

If you think you have the right value in config file, you can install a http tracer between report manager and report server so you can really see the traffic between them.