While trying to automate deployments that include SSRS reports and SSRS shared data sources/data sets we have been trying to configure security appropriately. Particularly the problem is with shared data sources/data sets and automating their deployment across all environments. The only solution that has come up is to create a single SQL Server Login and use the same username password as the data source for the shared data sources in SSRS. The account would be the same from Dev to Prod.
The newly created SQL Login will need EXECUTE on stored procedures in all these environments. I am against having one SQL login with EXECUTE permissions in ALL environments.
Prior to a more automated deployment, we would have an account in all 3 environments but a different password in Production. The DBA team would manually change the shared data source/data set's password after deployment. The goal is to move away from the manually intervention while also not creating a large security hole.
SSRS config file is encrypted nonetheless I would like to not create a SQL Login with so many privileges. Are there any other options?
Best Answer
We are in the exact same scenario.
What you could do here is to deploy the projects into different environments / targets on the report server. For example you could create different "folders" in the reportserver and deploy it multiple times. Then configure the sql login with different passwords there once and prevent to overwrite the datasources during deployment to protect it from getting overwritten. This can be configured in the project's settings.