7

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?

  • so you are against having a shared, restricted account used for ssrs data source? that is, one for each data source? – scsimon May 18 '17 at 16:41
  • I did update my explanation but I am against a SQL Login with EXECUTE on all environments from development to production and everything in between. – Vic Work May 18 '17 at 16:47
  • You could have a post-deploy/promote script that runs and changes the shared datasource username and password based on the environment you deployed to. This link shows one method. powershell-with-dave.blogspot.com/2014/08/… – Jonathan Fite May 18 '17 at 21:13
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.