Lessons from a Google cloud data migration journey with on-premise infrastructure - Resolving a refresh token issue with Google API.

For all references and definition, see the page calledReferences & Glossary for lessons from a Google cloud…

Summary

The Dynamics CRM (on-premise) data migration failed following an update to Windows Server 2003 (security patches). Precisely, the refresh  token process of Google Service (DFP Publishers) failed.

 Resolving this authentication issue consisted of a 3-step approach:

Preliminary validation process: Windows Service (listener) and data migration Servers (event viewer) logs.

Problem isolation process: Windows Service application as an executable, Debugging of the .Net Library of Google API , Physical environment of the Windows Service and finally, the users and the domain controller.

Resolution process related to Windows Service physical environment: It was necessary to develop a new service (as a listener) to transfer the Dynamics CRM data to the new physical environment (Windows Server 2008/2012) and in a new queue. Once the data is transferred, the service (listener) deployed in the new physical server will be able to retrieve data from the queue and will be able to migrate data to Google Cloud.

Confidentiality

The white paper does not provide any confidential client data related to data migration. All the code in this white paper is only related to the CRM SDK365 code

Introduction : CRM Services & Google Web Services

The architecture is composed of Dynamics CRM (On-Premise), Windows Services (Listener), Microsoft Message Queue, Google Premium (Google Cloud Service) and Third-Party (DFP Publishers).

Context : Google API CRM and OAuth2 Provider

Accounts and contacts data had to be retrieved from CRM and placed in the Microsoft Message Queue (MSMQ) data storage directory. Data was exported in XML format and stored in a directory created for this purpose. The service (Windows Service - .NET application developed in C#, deployed in Windows Server 2003) behaved as a "Listener" in order to retrieve the data stored in the MSMQ directory. Once retrieved, the "Listener" had to authenticate to Google Premium Web Services in order to import the data (CRM accounts and contacts) into the online service directories (Google Premium).

Once the data was extracted from the MSMQ directory to the Google Premium directories, the data was cleaned from the MSMQ directory. However, in the event of an error or exception generated by Google’s Web services, the data was stored in an MSMQ directory created for this type of situation.

Finally, the "Listener" had to establish communication with Google’s Web services and for that purpose, a security token ("Token") had to be generated and its refresh was also generated during the transaction. For further information, Google API Web service uses the OAuth 2.0 "Framework" which specifies several types of authorization (Refresh Token, Client Credentials, etc.).

1. Problem : Refreshing the token during data migration

1.1 How does the authentication process work ?

Step 1 : The listener retrieves or connects to Google's Web service without problem.

Step 2 : Once the service is retrieved, the listener executes a CRUD query (CREATE, READ, UPDATE, DELETE) i.e. runs a read on the DFP Publishers to determine if the newly entered contact already exists in DFP Publishers (Google Cloud).

Step 3 : If this is the case, it will then execute a request of type "creation" of the contact, otherwise it will be a request of type "update" of the contact.

Hence, the problem of refreshing the security token occurs in all environments (DEV, TEST, PROD) when reading the listener in DFP Publishers since authentication is required at this stage.

1.2 Impact of updating security patches in the data migration server

The upgrade of security "patches" in the data migration server (Windows Server 2003) had a negative impact on the authentication process of the Google Web service related to the token refresh (security token).

Listener Log

The existing log file describes the problem as follows: "Failed to refresh access token". This is an "application" type error that is launched by the "3rd-Party" (DFP) used by Google’s Web service to authenticate and execute "CRUD" type queries (CREATE, READ, UPDATE, DELETE). This error occurs precisely in the authentication provider of this "Thrid-Party" (DFP Publishers).

The security token could no longer be refreshed according to the contents of the error generated by the .NET library "Google.Api.Ads.dll": See the stacktrace below.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 « at Google.Api.Ads.Common.Lib.OAuth2ProviderForApplications.RefreshAccessTokenInOfflineMode () in z:\Git\google3\third_party\dotnet_src\adsapi\compile\Lib\OAuth2ProviderForApplications.cs:line 165 at Google.Api.Ads.Common.Lib.OAuth2ProviderForApplications.RefreshAccessToken() in z:\Git\google3\third_party\dotnet_src\adsapi\compile\Lib\OAuth2ProviderForApplications.cs:line 200 at Google.Api.Ads.Common.Lib.OAuth2ProviderBase.RefreshAccessTokenIfExpiring() in z:\Git\google3\third_party\dotnet_src\adsapi\compile\Lib\OAuth2ProviderBase.cs:line 249 at Google.Api.Ads.Common.Lib.OAuth2ProviderBase.GetAuthHeader() in z:\Git\google3\third_party\dotnet_src\adsapi\compile\Lib\OAuth2ProviderBase.cs:line 240 at Google.Api.Ads.Dfp.Lib.DfpSoapClient.InitForCall(String methodName, Object[] parameters) in z:\Git\google3\third_party\dotnet_src\adsapi\compile\Lib\DfpSoapClient.cs:line 112 at Google.Api.Ads.Common.Lib.AdsSoapClient.MakeApiCall(String methodName, Object[] parameters) in z:\Git\google3\third_party\dotnet_src\adsapi\compile\Lib\AdsSoapClient.cs:line 211 At Google.Api.Ads.Common.Lib.AdsSoapClient.Invoke(String methodName, Object[] parameters) in z:\Git\google3\third_party\dotnet_src\adsapi\compile\Lib\AdsSoapClient.cs:line 127 at Google.Api.Ads.Dfp.v201408.CompanyService.getCompaniesByStatement(Statement filterStatement) in z:\Git\google3\third_party\dotnet_src\adsapi\compile\v201408\DfpApi.cs:line 7004 at ...GooglePremium.Sync(BaseCompanie item, String cie, StreamWriter swFileTrace) in C:\Users\...\Documents\Visual Studio 2010\Projects\...\......cs:line 113 ».

Data Migration server and log

The Event Viewer of the Data Migration Server (Windows Server 2003) showed an error related to the execution of the Listener and the INFRA domain in which the Listener was running: See the content of the event viewer below.

1 2 “The Message Queuing service will not join the INFRA domain. An MSMQ Configuration (msmq) object exists in the new domain with an ID differing from the service ID. Please delete the MSMQ Configuration object in the new domain, restart the Message Queuing service, and log on again (Event ID = 2164)”

There are also 3 points that should be considered in the "Troubleshooting" process of the problem: the lack of space in the server, the blocking of the server's "Firewall" and "McAfee" anti-virus.

2. Preliminary Solution : Validation Process

2.1 Listener : log file

▪ First, the necessary information for the authentication process had to be regenerated from the tool "OAuth2TokenGenerator.exe[1]. I therefore regenerated the value of the “refresh_token”.

▪ Then, assuming that the authentication settings had changed on the Google web services side, we ran a series of tests by extracting and adding the settings mentioned on the Google website. However, according to Google’s website, the parameter that seemed to be missing was "grant_type", but adding it didn’t work.

Note: These parameters are added in the "Third Party" code used by Google's Web service and therefore all the parameters needed for the process seemed to be already present. The Third-Party code manages it in the "RefreshAccessTokenInOfflineMode()" method (line 163 of the OAuth2ProviderForApplications.cs file) that is, it fills it with the value of the "refresh_token".

2.2 Event viewer : data migration server

After much research, this error 2164 (associated with the listener) led us to apply the solution proposed by Microsoft [2], but that did not work. However, error 2164 no longer appeared.

Stale objects can prevent the MSMQ Service from operating properly. Deleting stale objects may solve this problem. However, deleting a computer object in Active Directory Domain Services (AD DS) can cause problems on the client computer. Before deleting the computer object, make sure that no services running on the client computer will be affected. In this case, deleting the Message Queuing Active Directory object will delete public queues on that computer. You must have the Active Directory services tools installed in Role Administration tools under Remote Server Administration. To perform these procedures, you must have membership in Administrators, or you must have been delegated the appropriate authority.

 

2.3 Other issues

▪ The lack of space on the server's hard drive has led us to increase its space;

▪ The "Firewall" of the 2003 server was disabled;

McAfee anti-virus software was disabled.

Moreover, the server was restarted repeatedly. However, the refresh token failed again.

3. Next step : Authentication problem isolation process

3.1 Windows Service (.exe)

The purpose is to validate if the problem was directly related to the Windows service (listener). In the production environment (00.000.000.00), two validation tests were carried out:

First isolation test

An application that was not a "Windows Service" (Listener) type was deployed in this environment and run from the same environment. However, the validation test generates the same error as the above mentioned one "Failed to refresh access token".

Second isolation test

The application ("Windows Service"/Listener) was deployed in and runs from the same environment. However, the second validation test gets the same error as the above mentioned one "Failed to refresh access token".

First isolation test and second isolation test…

The process couldn’t get data from the third-party (Google) and it’s directly linked to thefailed authentication. First and second isolation tests are not enough, so what’s next ?

3.2 Third-Party and Google Web Services

The purpose is to know if the issue was directly related to the .NET libraries of the Google API. To do so, two validation methods were possible: Unit Testing and Remote Debugging.

Unit Testing

Unit tests were performed on the "Third-Party" used by the Google Web service that the lsitener used when requesting DFP Publisher. The aim was to test the methods of the DLL "Google.Dfp.dll" in practice. In concrete terms, I retrieved the code from the "GitHub" site and performed unit tests on this solution (Visual Studio).

This test allowed me to assess the classes and methods that were mentioned in the error (see the appendix page which presents the details of the exception). Also, the annex presents a series of images showing the execution of unit tests (recovery of web services, initialization of services, generation of security token and refresh of security token).

Remote debugging

With the "Remote debugger" of Visual Studio 2010, I debugged the listener code to recover more information. I have only retrieved the information already mentioned.

Phases of testing and debugging process

 

→ Get the Web Services (DFP Publishers)

→ Initialize the services

→ Generate the security token

→ Refresh the security token

3.3 Physical environment of the Listener

The purpose is to run the Listener as a .NET executable in different Windows Server operating systems, considering the different « Domain Controller ». The latter is a server responsible for network security and acts as a guardian for user authentication and authorization. The results were mixed, both positive and negative.

ENVIRONMENT

SERVER

DOMAIN CONTROLLER (DC)

RESULT

DEV/LOCAL

WINDOWS SERVER 2012 R2

N/A

AUTHENTICATION/REFRESH TOKEN IS A SUCCESS

DEV

WINDOWS SERVER 2008/2012

DC INFRA

AUTHENTICATION/REFRESH TOKEN IS A SUCCESS

TEST

WINDOWS SERVER 2008/2012

DC INFRA II

AUTHENTICATION/REFRESH TOKEN IS A SUCCESS

DEV

WINDOWS SERVER 2003

DC INFRA

AUTHENTICATION/REFRESH TOKEN IS A FAILURE

TEST

WINDOWS SERVER 2003

DC INFRA II

AUTHENTICATION/REFRESH TOKEN IS A FAILURE

The "pattern" occurred naturally, and the error was therefore related to the servers in which the latest security updates or patches were executed. That said, in order to confirm our validations, i had to determine whether the security token refresh problem was not also related to the logged-in user.

Windows 2008/2012R2 are the key to move forward. So, what’s next ?

3.4 Logged-in user in servers and Domain Controller

As before, the goal is to run the Listener as a .NET executable in different Windows Server operating systems, considering the different Domain Controllers. However, this time we take into account the user connected via the "Domain Controller".

 

USER LOGGED

ENVIRONMENT

SERVER

DOMAIN CONTROLLER (DC)

RESULT

Super Admin Réseau in DC, Admin in DC,

Admin in Local Computer[3]

DEV/LOCAL

WINDOWS SERVER 2012 R2

N/A

AUTHENTICATION/REFRESH TOKEN IS A SUCCESS

DEV

WINDOWS SERVER 2008/2012

DC INFRA

AUTHENTICATION/REFRESH TOKEN IS A SUCCESS

TEST

WINDOWS SERVER 2008/2012

DC INFRA II

AUTHENTICATION/REFRESH TOKEN IS A SUCCESS

DEV

WINDOWS SERVER 2003

DC INFRA

AUTHENTICATION/REFRESH TOKEN IS A FAILURE

TEST

WINDOWS SERVER 2003

DC INFRA II

AUTHENTICATION/REFRESH TOKEN IS A FAILURE

4. Final Step : Resolution process : Provision a new listener in the Architecture

 

Following the numerous tests carried out in the 2003 and 2008/2012 servers, we established that a "pattern" seemed obvious and that it became undeniable that the "Listener" (as a Windows Service) should be moved to another server (2008/2012).

The idea was to create a bridge between the two "Message Queues" of 2003 and 2008/2012 servers while maintaining the Dynamics CRM platform as it is and the current "Listener" code. Once the new listener (Listener A) has been developed and deployed in the environment (source), it will be able to transfer the data to the other listener (Listener B). The last one will be able to transfer the data to the Cloud (Google Web Service).

Here’s below the steps of the resolution process :

STEP 1 : The CRM data is transferred in XML format to a private queue of the MSMQ of the 2003 server (Windows Server 2003).

STEP 2 : The new bridge that is a Windows "ListenerBridge" service (Listener A) retrieves the message from the MSMQ server 2003.

STEP 3 : The new "ListenerBridge" bridge transfers to the MSMQ private queue of another server (Windows Server 2008/2012).

STEP 4 : Once the transfer is complete, the "ListenerBridge" clears the message sent from the MSMQ on the 2003 server and processes the other messages in the queue.

STEP 5 : The old Windows service "MSMQ Listener" (Listener B), which is in the 2008/2012 server, retrieves the new message from the private queue of the same name.

STEP 6 : The "MSMQ Listener" service connects to the Google[4] service and retrieves all objects (CompanyService, etc.).

STEP 7 : Once the specific service is retrieved (CompanyService, etc.), the Listener executes a “GET” request to validate if the contact and account mentioned in the message already exist in the "Cloud" directory.

STEP 8 : Once the validation of step 7 has been completed, the service creates or modifies the contact or account in Google's "Cloud" universe (a “POST” type of query).

STEP 9 : Then, the "Listener" deletes the message from the private queue and moves to the next message. If an error occurs in step 8, the message is transferred to a private queue created for that purpose.

CONCLUSION

Finally, the problem of refreshing the authentication token via the .NET library "Google.API" was solved in 3 linear steps: a process of validating preliminary solutions, a process of isolating components that play a role in refreshing the security token, and finally, the resolution process itself by creating a new "listener" as a "bridge" between CRM data and Google’s Web Service directory.