2

I am developing SQL Server Reporting Services (SSRS) reports on SQL Server 2008 R2 and using Report Manager as a method to demonstrate and test reports. I am looking for a way to allow users of the same domain to connect to the Report Manager and run reports via a browser (not SharePoint) without letting the user have too much access to the data source. I currently have each user listed as db_owner for the database that the datasets and data source are associated with. I would like to limit this access and I have tried db_datareader but this level does not allow the user to run the reports and gives the user this error: “Cannot create a connection to data source 'DBname'. (rsErrorOpeningConnection)”.

My method of adding a user to The Report Manager site: I select the ‘Security’ tab under ‘Site Settings’ and then select ‘New Role Assignment’ adding the user as a ‘System User’. I then select ‘Folder Settings’ on the toolbar and again select ‘New Role Assignment’ adding the user as a ‘Browser’. I have tried adding a user as a ‘Content Manager’ but they still have the same error when it comes to the data source.

My method of adding a user to the data source: select new login from the Security tab for the server, add domain\username to ‘Login name:’, use Windows authentication and change the default database from master to the database that is the reports data source. I then select ‘User Mapping’ and put a check next to the database that is the data source. In the ‘Database role membership for: DBname’ section I choose db_owner and public is already selected. I have included screenshots below. My question is what ‘Database role membership’ can I use for SSRS and Report Manager that would not be as broad as db_owner and would have the best security? I have tried db_datareader but then the user cannot connect to the data source when they run the report.

I have researched this question but I have not found any details accept for adding the user as a db_owner as I described. MSDN acts as if the settings in Report Manager are all that you need to set for the user/report to have access to the data source. I have tried only using the Report Manager settings with both settings for a data source, shared and imbedded with no luck.

Thank you in advance

PerPlexSystem
  • 704
  • 1
  • 15
  • 30

1 Answers1

2

Typically, the data sources in SSRS will be set to use a fixed account, either a Windows account or SQL authentication. This account should be given minimal privileges to the database: db_datareader is common.

Then security to the report is controlled through Report Manager as you describe above. this avoids the need for changing security on the database itself with changes in user permissions.

But the approach you describe above should work as well. The error you see when the user has db_datareader access is surprising if your query is a standard SQL query selecting from tables. If you are using Stored Procedures, you need to grant access to those as well. Use a test user account that is set to db_datareader; see if you can connect and execute your query through SQL Server Management Studio.

Depending on your security requirements, I would use a dedicated account for database access from the reports, say "ReportReader." Develop and test your reports accessing the databases as this user, and make sure the user has minimal access, read-only and/or limited to only the tables or procedures they need to execute.

The credentials used to access the database are set in the properties of the datasource. This is one reason that Shared datasources are often used, and the reports are linked to the shared datasources: Data source properties

The screenshot shows a SQL server authenticated account in use. This could just as easily be a fixed Active Directory account; check the "Use as Windows credentials when..." in that case.

Jamie F
  • 23,189
  • 5
  • 61
  • 77
  • How do you enable a dedicated account to be used by Report Manager on the SQL Server DB? I do have stored procedures for each report and based on your information here I have changed user access level from db_owner to db_executor for the data source but this still gives the user too much access not to mention the hassle of adding each user to the data source. – PerPlexSystem Mar 07 '12 at 02:16
  • Edited with data source details. – Jamie F Mar 07 '12 at 03:23
  • Your information is great but I’m still having difficulties. Most notable with having the ReportReader user pass the Test Connection. The Steps I followed to add the user via SQL Server Management Studio: under Security I create a new login, enter the name, select SQL Server authentication and enter a simple password that matches Report Manager, set Default database to master or the datasourceDB (tried both). Under User mapping: I mapped the user to the datasourceDB, ReportDB and ReportDBTemp, I have tried different combos of db_datareader, db_owner and db_executor. I'm not sure what's wrong. – PerPlexSystem Mar 08 '12 at 19:37
  • I'm having the same problem. No matter what I do short of making a user `db_owner` he is unable to get past *Test Connection* while adding an SSRS data source. – Yuck Sep 18 '13 at 14:41
  • 1
    I'm not a huge fan of the "Test Connection" button. I've seen a few cases where it will fail, but the data source is fine. I believe a better test is to use SSMS and login as that user, if using SQL Server Authentication. – Jamie F Sep 18 '13 at 15:47