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?

Perhaps Erland Sommarskog's article on permissions will help you . . . sommarskog.se/grantperm.html. — Gordon Linoff
SSRS only requires read permission to datasources so that's not an issue. If you look at the datasource on your report manager website, what options have you got in there? Is it using a sql account or windows authentication? Does it work in your development environment and not on the server? — Davos
It is windows authentication. I cannot figure out how to pass the Windows credentials to the SQL server from the report. It always wants to run as the user viewing the report. — JD Roberson

Paul Shiryaev Paul Shiryaev · Accepted Answer · 2013-02-25T14:15:05

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:

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
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?

SSRS Impersonation for Datasource

1 Answers