With the deprecation of Application Impersonation, Sage CRM has moved to using Microsoft Graph for Exchange integration. However, many users have encountered issues where Exchange synchronization fails. This blog post outlines the causes, troubleshooting steps, and necessary database adjustments to restore sync functionality.

Why Sync May Fail: Token Refresh Issues

A common cause for sync failure is outdated tokens. In particular, refresh tokens required for reauthentication were not being updated in some environments.

Step-by-Step: Checking Token State in the Database

Run the following query to assess token-related settings for all user. To select a particuler user, uncomment (--)  the user_logon selector (e.g., ‘admin’):

SELECT USet_SettingId, UserSettings.USet_UserId, Users.User_Logon, USet_Key, USet_Value, 
       USet_UpdatedDate, USet_TimeStamp, USet_Deleted
FROM UserSettings
JOIN Users ON Users.User_UserId = UserSettings.USet_UserId
WHERE 
  USet_Key IN ('EMC_AuthAccessExpires', 'EMC_AuthRefreshToken', 'EMC_AuthCode', 'EMC_AuthTokenType', 'EMC_EWS_AuthCode')
-- and User_Logon = 'admin'
ORDER BY USet_UserId, USet_Key;

Understanding Token Behavior

For Non-Synced Users

If the user is not included in Exchange Sync, the EMC_AuthAccessExpires value may be in the past. To initiate a token refresh:

1. Attempt to use the 'import Contact' or 'Import Email' for the user
2. If it fails, reset the Setting in Preferences tab - ....  with their respective credentials.
3. If a successful connection is made, tokens are refreshed.
4. Re-check the UserSettings table to confirm updates.

For Synced Users

Tokens should refresh automatically as expiration nears:

• Ensure EMC_AuthAccessExpires is later than the current time.
• Confirm this by rechecking UserSettings after the expected refresh interval.

Resetting a User Connection

When a user resets their connection:

• The system assigns a new EMC_AuthCode.
• Simultaneously, EMC_AuthRefreshToken is cleared to avoid reuse of old tokens.
• The Auth Code must then be converted into:
  • Auth Token
  • Refresh Token

Once converted, the Auth Code must be cleared as it’s no longer valid.

Before conversion, the table should reflect:

• EMC_AuthCode populated
• Other token fields (EMC_AuthAccessExpires, EMC_AuthAccessToken, EMC_AuthRefreshToken) should be null

Use this SQL statement to ensure a clean state:

UPDATE UserSettings
SET USet_Value = NULL
WHERE USET_UserId = <userid>
  AND Uset_key IN ('EMC_AuthAccessExpires','EMC_AuthAccessToken','EMC_AuthRefreshToken');

Glossary of Terms

Conclusion

Properly managing and verifying token state is essential for reliable Exchange synchronization in Sage CRM using Microsoft Graph. Regularly review and refresh token values in the UserSettings table, and follow the documented procedures when users reset their connection or encounter sync issues.