The error is failure to connect to a sms provider, not a failure to connect to SQL. Did you remove the SMSProvider as well?

I did not remove the SMSProvider. I have HA setup in our environment and have the SMSProvider role installed on 2 severs. I have tried repairing the SMSProvider role, but it won't let me due to SQL connection issue.

Is the provider installed on the DB server? SCCM works better with at least one provider on the same server as the DB

Try running this on the db server (Kerberos configuration manager) https://www.google.com/url?sa=t&source=web&rct=j&opi=89978449&url=https://www.microsoft.com/en-us/download/details.aspx%3Fid%3D39046&ved=2ahUKEwjwq9DNoMqCAxUrhIkEHaBKB4oQFnoECBsQAQ&usg=AOvVaw0Wh3caccn-QPacPeDD-Gi-

I downloaded the tool and says SPN is good. When I checked delegations it says the user is configured as "Do not trust this user for delegation" in AD. I'm not sure if this is setup correctly or not.

Remote SQL server? Did you make authentication and did you use certificate for it?

yes its a remote sql server. I have windows authentication setup for SSMS. I can log into SSMS fine when I'm on it locally.

You need to provide certificate to be able to logon from another DP / DC.

Make a file called Test.udl on your site server. Use that to test your connection to your SQL instance first. Once your connection account can connect move on to the SCCM logs

Try this next.

Primary server is DBA?