Cannot create a connection to data source 'PolicyDW'. (rsErrorOpeningConnection)

Apr 11, 2012 at 8:55 PM

I can connect to the report server and see the "SQL Server Complianc" report but when my co-worker tries to do the same he gets the following errors:

> An error has occurred during report processing. (rsProcessingAborted)

>   Cannot create a connection to data source 'PolicyDW'. (rsErrorOpeningConnection)

>       For more information about this error navigate to the report server on the local server machine, or enable remote errors

This is the first time I've ever used SSRS and have no idea where to look to fix this.  Does anyone have a suggestion on how to resolve this?



Apr 16, 2012 at 6:31 PM

Hi Ken,

   Your coworker may not have adequate SQL Server-level rights to the MDW database, which the reports key off of. Remember, when you deploy the project the reports are using Windows authentication. You're able to see reports because (I'm assuming) you're the DBA and have full rights to that database/instance. Check your coworker's rights to the MDW database and grant permissions as needed. Hope this helps!

May 28, 2013 at 9:38 PM
I've had this working for a while but thought I'd reply just in case someone else has the same problem...

I actually had multiple problems with my deployment. This is the first report I deployed and thought everything could be configured through Management Studio. While I did need to add the Active Directory group for my teammates to the SQL database and grant them the "db_datareader" role, I also needed to use Report Manager to change the security for the report to add the same AD group with the "Browser" role. I had previously thought that Report Manager was just for viewing reports - I didn't realize hoe much of the administration needs to be performed using it.

Thanks for your help.