SharePoint 2010 sites experience ‘An unexpected error has occurred’ upon connecting to a User Profile Service Application published from another farm

Issue

Upon establishing a User Profile Service Application Proxy connection to a User Profile Service Application published from another farm, your SharePoint 2010 sites crash with the dreaded unexpected error message:

Upon reviewing the ULS logs, you will notice there is also a login failed error related to the database associated with the User Profile Service Application on the publishing farm:

System.Data.SqlClient.SqlException: Cannot open database "" requested by the login. The login failed. Login failed for user ''.

SqlError: 'Login failed for user ''.' Source: '.Net SqlClient Data Provider' Number: 18456 State: 1 Class: 14 Procedure: '' LineNumber: 65536 Server: ','

I have noticed this issue occurs primarily when using SQL aliasing.

Solution

1)      Create a SQL alias on the consuming farm that matches the alias on the publishing farm. For example, if you have an alias name of SQLDev on the publishing farm, create an alias with the name of SQLDev on the consuming farm, both pointing to database server for the publishing farm.

2)      Restart IIS on each server in the consuming farm. iisreset /noforce.

3)      Restart the Timer service on each server in the consuming farm. net stop sptimerv4 and net start sptimerv4.