SSRS Impersonation for Datasource

Question

I am working to deploy a reporting solution using SSRS and PerformancePoint. I have 4 databases I have pull from. 3 I have full control over the other I only have an additional domain account that has access to SQL with read only permissions, this is the only option due to the security agreement for this database.

I cannot for the life of me figure out how to publish a report using the domain account protected data. I have tried every combination of authentication I could find on google for the dataset but it just will not work.

Ideas?

Answer 1

My understanding is that you are not combining data from 4 different servers as your data source in this report, correct?

Then, In ReportManager - go to report properties - i.e. right-click on your report and then left-click on Manage from the dropdown menu. Then choose Data Source tab - it will have the following option:

A custom data source

Data source type: Microsoft SQL Server  
Connection string: Data Source=YOUR.SERVER.IP.ADDRESS;Initial Catalog=YOUR_DATABASE_NAME        

Then choose: Connect using: Credentials stored securely in the report server

You can enter your domain username and password there: e.g. 
User name: SOMEDOMAIN\MyUser.Name
Password: MyPa$$wo%d

Then checkmark: Use as Windows credentials when connecting to the data source
You may need to checkmark this one too: Impersonate the authenticated user after a connection has been made to the data source

This will store your login/password combination securely on the server and will allow the report to connect to your datasource.

You can also do this for a shared datasource.

Otherwise, If you're trying to combine data from your 3 servers and 1 additional where you have read-only access only, I would recommend to:

1. create a linked server entry on one of your servers pointing to that 1 read-only server, and may be two more for the two other servers

2. create stored procedures that would provide datasets for your report gathering data from the other 3 servers as needed - this way you'd only need 1 datasource on the reportserver

Does this answer your question?