SCCM 1702 - Reporting services data source

[VRDRF]

We have recently reinstalled our SCCM environment and migrated from the old environment to the new one.

The problem is that it seems some of the reporting services datasources were also migrated to the new server and is now causing some issues in reporting services.
reports sometimes fail because they are looking at a broken datasource that doesn't connect.
Stupid thing is, the old environment has the exact same data sources and the same one is also broken there but isnt causing issues.

The one starting with 5C works fine but the one with 39 just doesn't connect.

We have tried:

• changing connection string (it works but sccm just replaces the string again causing it to break again)
• Removing data source (sccm will just recreate it and break all the reports)
• Reinstalling report point (sccm will just recreate all the reports and the broken data source)
• reinstalling ssrs (threw database and files away but result is the same)

Now I'm guessing that SCCM just creates the datasources based on something that is in his database but how do we get rid of this?

Thanks in advance guys!

 

[GarthMJ]

Ok I will bite, why are you change the MS DS at all?

Are you trying to share one SSRS server for two different CM environments? (BTW that isn't supported)

 

 

[VRDRF]

Well I'm not trying to change anything, these 2 data sources are created by sccm every time the reporting point is installed by SCCM, one of them works and the other one doesn't.
This is a fresh installation of SSRS on the same machine that is running the new SCCM installation, it is in no way connected to the old environment. 

Basically what I'm trying to do is to get rid of the broken Data source so that my reports will stop failing 50% of the time. 

[GarthMJ]

Which reports are failing? Is it the Data warehouse reports?

[VRDRF]

all reports 50% of the time, you then have to refresh the page to get them to work. 

the datawarehouse reports don't work at all, not even after a refresh and these are the only reports that this broken data source has dependent items for.
You can also see that the connection string for this data source is not correct, I can change it but sccm just changes it back after I press apply.

[GarthMJ]

You need to solve you SSRS timeout issues. Once that is fixed, you can come back to this issue.

[wefixedit]

I faced a similar issue when trying to change the service account used for SQL Service Reporting Services (SSRS). This might help future readers who come across this post:

Scenario: In my situation, we were trying to change the service account being used for SSRS. I changed the account used in ConfigMgr and tried changing the account used in Reporting Services Config Manager 2016 (RSCM). The account change in ConfigMgr stuck while the account change in Reporting Services Configuration Manager 2016 didn't and all our ConfigMgr Reports broke.

Encryption Keys: Backup the Encryption Keys before doing anything. Then, once you change the account - restore the Encryption Keys you backed up earlier. Because we didn't do this, we had to select the Delete option and assigned the new service account. As a result, we lost all of our custom reports (.RDL files) which could have been backed up easily.

Permissions:

- Ensure that the Reporting Services service account (sa_sccm_rs) you're referencing is in the Service Account tab of the Reporting Services Configuration Manager. You will get errors if you are trying to assign a service account while running the Reporting Services Config Manager without appropriate SQL permissions. Ask your DBA to help or give yourself SysAdmin permissions in SQL if it's a LAB environment.

- Ensure that the account used in ConfigMgr for the Reporting Services Point is using the same account (sa_sccm_rs) you've configured in Reporting Services Configuration Manager.

- Ensure that your service account (sa_sccm_rs) has RSExecRole permissions on the ReportServer and ReportServerTempDB databases.

What's funny is my issue led me to Garth's post to verify I had setup everything correctly:
https://www.enhansoft.com/how-to-install-sql-server-reporting-services-2017/

And to the following link which described exactly what I was doing and where I went wrong:
https://www.mssqltips.com/sqlservertip/5787/change-the-sql-server-reporting-services-service-account/