Sql-server – get prompted for “Data Source Credentials” when editing a SSRS report for SCCM

sccmsccm-2012sql serverssrs

When attempting to add a dataset in Report Builder I am prompted to "Enter Data Source Credentials."

enter image description here

Choosing "Use the current Windows user" fails, and I don't have the password for the specified user.

  • The environment is SCCM 2012.
  • What permissions need to be changed so that I may access the SCCM datasource?
  • What are best practices for giving users RW access to reports in SCCM/SSRS?

Best Answer

The solution was two-fold:

  1. I exported the certificate from the primary site sever under "Personal\Certificates" the friendly name is "ConfigMgr SQL Server Identification Certificate." I then imported the certificate to my local workstation under "Trusted Root Certificate Authorities\Certificates." This allowed me to connect to SQL server when I choose "Use the current Windows user." Before I made this change I would get a certificate error when choosing this option.
  2. I had the DBA enable db_datareader access explicitly for my user in SQL Server.

When creating a dataset I still was prompted for credentials but I can now select current user and create queries.

Related Topic