Project Server 2007 and SSRS 2005 integration: issue withauthentication when running to the reports

[Vit]

Hi All,

my configuration is the following:

- Project Server 2007 SP2
- SQL 2005 SP2
- SSRS 2005 configured to work with SharePoint integration (http://
blogs.msdn.com/sharepoint/archive/2007/02/19/microsoft-sql-server-2005-
sp2-reporting-services-integration-with-wss-3-0-and-moss-2007.aspx )


I have opened BIDS, created a custom report based on the
ProjectServer_Reporting DB (the one that the SDK give us the schema),
created a Shared Data Source, deploy into Project Web Access (I have
created 3 different document libraries - one to store the data
connections, one to store the reports (the .rdl files) and one to
store the web part pages that host the report web parts...)

In BIDS I have deploy the report and as planned in the setting this
has been pushed the following:

- Data Connection in stored in the Data Connection Library
- Report is stored in the Report Library

I have manually build the web part page adding the report web part,
pointing the url to the reports library...

if I run this as farm/project server System Administration, I can run
it, if I run it as a standard Project Server Project Manager, I get
the following error:

"An Error occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'ProjectServer_Reporting'.
(rsErrorOpeningConnection)
Cannot open database 'ProjectServer_Reporting' requested by the login.
Login failed for user 'DOMAIN\bob'."

I have temporary fixed this issue by adding the domain user group to
SQL and give them 'write' permission to the 'ProjectServer_Reporting'
database....

this has fixed the problem, but I don't think it is the right way to
fix it.. I mean, giving everyone in the domain user group a login to
the database is not a good practice from a security point of view....

how can I restore the security???? in this way, every domain user will
see the report, instead I'd like to develop a report that has security
embedded....

I mean, if I'm a project manager at "my project level", I should be
able to see only my project in the report... instead if I'm a
resource, at "my task level", I should not to be able to see project
data... only resource date....

How can I apply the same security model of Project Server to a report
embedded into project server???

thanks all

Vit

 

[Stephen Sanderlin [MVP]]

You should be able to use stored credentials by doing the following:
1) Edit the report's data source.
2) In the Credentials section, click "Stored credentials" and enter the
credentials of a user with READ-ONLY access that you have specifically
created for this purpose. Do not use the Farm account or any other
service account.
3) Check "Use as Windows credentials" and "Set execution context to this
account".
4) Click OK and test.

I noticed you're giving the user "write" access -- why? User accounts
(read: accounts which are not service accounts) should only have read
access to the RDB.

As for filtering the data in your reports based upon the current user,
please refer to my recent MSDN article on the subject:
http://msdn.microsoft.com/en-us/library/ee862408.aspx. I believe you
should be able to follow the same procedure whether your SSRS is running
in native or SPI mode.

--
Stephen Sanderlin, Project MVP
VP of Technology
msProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read our blog at: http://www.projectserverhelp.com

 

[Vit]

You should be able to use stored credentials by doing the following:
1) Edit the report's data source.
2) In the Credentials section, click "Stored credentials" and enter the
credentials of a user with READ-ONLY access that you have specifically
created for this purpose. Do not use the Farm account or any other
service account.
3) Check "Use as Windows credentials" and "Set execution context to this
account".
4) Click OK and test.

I noticed you're giving the user "write" access -- why? User accounts
(read: accounts which are not service accounts) should only have read
access to the RDB.

As for filtering the data in your reports based upon the current user,
please refer to my recent MSDN article on the subject:http://msdn.microsoft.com/en-us/library/ee862408.aspx. I believe you
should be able to follow the same procedure whether your SSRS is running
in native or SPI mode.

--
Stephen Sanderlin, Project MVP
VP of Technology
msProjectExperts

For Project Server Consulting:http://www.msprojectexperts.com
For Project Server Training:http://www.projectservertraining.com

Read our blog at:http://www.projectserverhelp.com

Thanks!!! you are right, I should give read permission my bad !!!


the article is awesome!!! thank you so much... I will let you know hoe
I will go...

thanks

Vit

 

[Stephen Sanderlin [MVP]]

No worries. I'm glad that you are finding the article useful! Please let
me know if you need anything else.

--
Stephen Sanderlin, Project MVP
VP of Technology
msProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read our blog at: http://www.projectserverhelp.com