Today a quick note, as I am surprised the Internet is silent about this topic.
After I have moved the SCCM database server to a brand new server, one of SCCM components (DATA_WAREHOUSE_SERVICE) started having issues – „Cannot open database CM_XYZ requested by the login. Login failed. Login failed for user DOMAIN\MachineName$”.
While you review the log (\SMS\Logs\Microsoft.ConfigMgrDataWarehouse.log) you may notice the Data Warehouse service still tries to pull the data from the old Database Server – without success.
To solve the issue, please do following:
Step 1: Assign the DOMAIN\MachineName$ permissions to master database on the new Database Server (SSMS: User Mapping -> master -> assign public role membership).
Step 2: Run following query on the master database on the new Database Server:
use master;
go
grant view server state to [DOMAIN\MachineName$];
go
Step 3: Run regedit on the Data Warehouse server. Proceed to HKLM\Software\Microsoft\SMS\DWSS. Change the value in the key „ConfigMgr Server” to the new Database server. Yes, this is the key for the Database Server, not for the Site Server – no worries!
Step 4: Open the Services on the Data Warehouse server and restart the service called ConfigMgr Data Warehouse synchronization service.
Step 5: Open the log and review the status.
I am quite suprised why this is not being done automatically when the Site Database Server changes and why it cannot be changed in the Data Warehouse role settings – but it is as it is.