Dealing with Crypto ENCRYPTION error in Dynamics 365 FINANCE and supply chain Tier 1 developer Machine

Recently I faced an issue related to crypto encryption on a DEV BOX (Tier 1) Cloud hosted machine.

Below is the full error message

Encryption error occured with exception: Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionException: Encryption error occured with exception: Microsoft.Dynamics.AX.Configuration.CertificateHandler.NoCertificateFoundException: No certificate found for id 'EE19588CB6569148D75AB3096213505AEE312E85'. at Microsoft.Dynamics.AX.Configuration.CertificateHandler.CertificateHandlerBase.GetCertificateFromLocalStore(X509FindType findType, String findValue) at Microsoft.Dynamics.AX.Configuration.CertificateHandler.CertificateHandlerBase.GetFirstCertificateForId(String id) at Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionEngine.GetCryptoServiceProviderByThumbprintBuffer(String idList, Byte[] thumbprint) ---> Microsoft.Dynamics.AX.Configuration.CertificateHandler.NoCertificateFoundException: No certificate found for id 'EE19588CB6569148D75AB3096213505AEE312E85'. at Microsoft.Dynamics.AX.Configuration.CertificateHandler.CertificateHandlerBase.GetCertificateFromLocalStore(X509FindType findType, String findValue) at Microsoft.Dynamics.AX.Configuration.CertificateHandler.CertificateHandlerBase.GetFirstCertificateForId(String id) at Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionEngine.GetCryptoServiceProviderByThumbprintBuffer(String idList, Byte[] thumbprint) --- End of inner exception stack trace --- at Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionEngine.GetCryptoServiceProviderByThumbprintBuffer(String idList, Byte[] thumbprint) at Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionEngine.DecryptionInternal(Byte[] cipher, Boolean validateSignature, String purpose)

The error used to come on several forms like when editing customer address and customer groups. Upon initial research on internet, it looked like the certificates on the VM have expired and I did the rotate certificates from LCS but that did not helped.

Then we raise a case with Microsoft and we started tracing back recent changes done to environment and we encountered that we did a Database restore from another Tier 1 VM which had dual write mappings enabled on it.

Microsoft support team shared the below SQL script with us to clear the environment specific data so we ran the below script and then did IIS reset and restarted the VM. This solved the problem. Sharing it here in case it helps you. P.S –> Take your DB backups before running this script.

--Tidy up the batch server config from the previous environment
DELETE FROM SYSSERVERCONFIG

--Tidy up server sessions from the previous environment.
DELETE FROM SYSSERVERSESSIONS

--Tidy up printers from the previous environment
DELETE FROM SYSCORPNETPRINTERS

--Tidy up client sessions from the previous environment.
DELETE FROM SYSCLIENTSESSIONS

--Tidy up batch sessions from the previous environment.
DELETE FROM BATCHSERVERCONFIG

--Tidy up batch server to batch group relation table
DELETE FROM BATCHSERVERGROUP

--Set any waiting, executing, ready or canceling batches to withhold
UPDATE BatchJob
SET STATUS = 0
WHERE STATUS  IN (1,2,5,7)
GO

--Disable Change Data Capture / CDC from Microservices team as it isn't compatible on another server
IF(1=(SELECT is_cdc_enabled FROM sys.databases WHERE name = DB_NAME()))
BEGIN
	EXEC sys.sp_cdc_disable_db
END

IF '$(EnvSku)' = 'prod'
BEGIN
  -- Batchjob History Cleanup from source environment
  BEGIN TRAN
  DELETE BATCHJOBHISTORY WHERE CREATEDDATETIME < GETDATE()
  COMMIT TRAN
  BEGIN TRAN
  DELETE BATCHHISTORY WHERE NOT EXISTS (SELECT RECID FROM BATCHJOBHISTORY JOB  WHERE JOB.RECID = BATCHJOBHISTORYID)
  COMMIT TRAN
  BEGIN TRAN
  DELETE BATCHCONSTRAINTSHISTORY WHERE NOT EXISTS (SELECT RECID FROM BATCHHISTORY WHERE BATCHHISTORY.RECID = BATCHCONSTRAINTSHISTORY.BATCHID)
  COMMIT TRAN 
  
  --Update SYSCONFIGURATION table 
  UPDATE SYSGLOBALCONFIGURATION SET [VALUE] = 1 WHERE NAME IN ( 'DATAAREAIDLITERAL', 'PARTITIONLITERAL ')
  --Tidy up printers from the previous environment
  UPDATE SYSGLOBALCONFIGURATION SET VALUE = 'SQLAZURE' WHERE NAME = 'BACKENDDB'

  --Tidy up printers from the previous environment
  UPDATE SYSGLOBALCONFIGURATION SET VALUE = 1 WHERE NAME = 'TEMPTABLEINAXDB' 
END

IF '$(EnvSku)' = 'sandbox'
BEGIN
  --disable all users except Admin, Microsoft account and Dynamics-alias users (they should go and re-enable as they see fit),
  DECLARE @sqlDisableUserCmd nvarchar(256) = 'UPDATE UserInfo
    SET ENABLE = 0
    WHERE ID <> ''Admin'' AND NETWORKALIAS not like ''%dynamics.com%'''

  IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'UserInfo' 
    AND COLUMN_NAME = 'IsMicrosoftAccount')
  BEGIN
    SET @sqlDisableUserCmd = @sqlDisableUserCmd + ' AND IsMicrosoftAccount <> 1'
  END

  exec sp_executesql @sqlDisableUserCmd

  --Proactively enable API Service Account.  This is optional account customer can create, and will not be enabled in the source.
  --Microsoft does not create this app user, but customers optionally can and they can choose which security role to give it.
  --Enabling it during database refresh ensures that their app user is able to run X++ cleanup scripts immediately after
  --the refresh is completed via Database Movement API from LCS.
  UPDATE UserInfo
  SET ENABLE = 1
  WHERE ID = 'DBMovementAPI'

  --Remove the SMTP server configuration to prevent sandbox from sending mails
  UPDATE SysEmailParameters
  SET SMTPRELAYSERVERNAME = '', MAILERNONINTERACTIVE = 'SMTP'

  --Blank out all email addresses - in the case someone adds back SMTP config this will prevent accidentally sending mails
  UPDATE LogisticsElectronicAddress
  SET LOCATOR = '' 
  WHERE Locator LIKE '%@%'
  
  --Remove all print management settings - there is email addresses stored in a container field here - again we want to prevent accidentally sending a vendor/customer a PO/SO email.
  DELETE FROM PrintMgmtSettings 

  DELETE FROM PrintMgmtDocInstance 
END

-- Change Maintenance Mode setting to disabled, in case it was enabled on the source environment
IF EXISTS (SELECT * FROM Information_Schema.Tables WHERE Table_Name = 'SQLSYSTEMVARIABLES')
BEGIN
	IF EXISTS (SELECT * FROM SQLSYSTEMVARIABLES WHERE PARM = 'CONFIGURATIONMODE' AND VALUE = 1)
	BEGIN
		UPDATE SQLSYSTEMVARIABLES
		SET VALUE = 0
		WHERE PARM = 'CONFIGURATIONMODE'
	END
END

-- Clean up tables where data is encrypted.
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'SysEMailSMTPPassword')
BEGIN
	TRUNCATE TABLE SysEMailSMTPPassword
END

IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'SYSOAUTHUSERTOKENS')
BEGIN
	TRUNCATE TABLE SYSOAUTHUSERTOKENS
END

IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'B2BInvitationConfig')
BEGIN
	TRUNCATE TABLE B2BInvitationConfig
END

IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'PersonnelIntegrationConfiguration')
BEGIN
	TRUNCATE TABLE PersonnelIntegrationConfiguration 
END

IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'CatExternalCatalogProperties')
BEGIN
	TRUNCATE TABLE CatExternalCatalogProperties
END

--CDS Unlink Various Tables BEGIN
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'DualWriteProjectConfiguration')
BEGIN
  TRUNCATE TABLE DualWriteProjectConfiguration;
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'DualWriteProjectFieldConfiguration')
BEGIN
  TRUNCATE TABLE DualWriteProjectFieldConfiguration;
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'DualWriteErrorLog')
BEGIN
  TRUNCATE TABLE DualWriteErrorLog;
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'DualWriteProjectConfigurationStaging')
BEGIN
  TRUNCATE TABLE DualWriteProjectConfigurationStaging;
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'BusinessEventsDefinition')
BEGIN
  TRUNCATE TABLE BusinessEventsDefinition;
END
--CDS Unlink Various Tables END


--Remove all attachment references as storage account is not copied. Approved by Tariq Bell.
UPDATE t1
SET t1.storageproviderid = 0
     , t1.accessinformation = ''
     , t1.modifiedby = 'NonProdRestore'
     , t1.modifieddatetime = getdate()
FROM docuvalue t1 
WHERE t1.storageproviderid = 1

-- RETAILTRANSACTIONSERVICEPROFILE
DECLARE @MIGRATIONVALUE NVARCHAR(50)
SET @MIGRATIONVALUE = N'https://MIGRATION_VALUE'

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'RETAILTRANSACTIONSERVICEPROFILE' 
  AND COLUMN_NAME = 'AzureResource')
BEGIN
  EXECUTE ('UPDATE dbo.[RETAILTRANSACTIONSERVICEPROFILE] SET ServiceHostUrl = ''' + @MIGRATIONVALUE + ''' , AzureResource = ''' + @MIGRATIONVALUE + ''' ')
END
ELSE
BEGIN
  EXECUTE ('UPDATE dbo.[RETAILTRANSACTIONSERVICEPROFILE] SET ServiceHostUrl = ''' + @MIGRATIONVALUE + '''  ')
END

-- RETAILCHANNELPROFILEPROPERTY
UPDATE 
  dbo.[RETAILCHANNELPROFILEPROPERTY]
SET 
  [VALUE] = N'https://MIGRATION_VALUE'
WHERE
  [VALUE] LIKE '%dynamics.com'


UPDATE 
  dbo.[RETAILCHANNELPROFILEPROPERTY]
SET 
  [VALUE] = N'https://MIGRATION_VALUE/Commerce'
WHERE
  [VALUE] LIKE '%dynamics.com/Commerce'


UPDATE 
  dbo.[RETAILCHANNELPROFILEPROPERTY]
SET 
  [VALUE] = N'https://MIGRATION_VALUE/MediaServer'
WHERE
  [VALUE] LIKE '%dynamics.com/MediaServer'


-- RETAILCONNDATABASEPROFILE (all rows)
UPDATE
  dbo.[RETAILCONNDATABASEPROFILE]
SET
  [CONNECTIONSTRING] = NULL

    -- RETAILIDENTITYPROVIDER 
UPDATE
    dbo.[RETAILIDENTITYPROVIDER]
SET
    [ISSUER] = N'https://sts.windows-ppe.net/MIGRATION_VALUE_' + SUBSTRING(CONVERT(nvarchar(50), NEWID()), 1, 8) + '/'
WHERE
    [NAME] = N'Azure AD'

UPDATE
    dbo.[RETAILIDENTITYPROVIDER]
SET
    [ISSUER] = N'https://MIGRATION_VALUE_' + SUBSTRING(CONVERT(nvarchar(50), NEWID()), 1, 8) + '/auth'
WHERE
    [NAME] = N'Commerce Identity Provider'

UPDATE 
  dbo.[RETAILHARDWAREPROFILE]
SET
  [SECUREMERCHANTPROPERTIES] = NULL

UPDATE 
  dbo.[CREDITCARDACCOUNTSETUP]
SET
  [SECUREMERCHANTPROPERTIES] = NULL

UPDATE 
  dbo.[RETAILCHANNELPAYMENTCONNECTORLINE]
SET
  [SECUREMERCHANTPROPERTIES] = NULL

--FULLTEXT STOP LIST REMOVAL
-------------------------------------------------------------------------------------

-- ALTER FULLTEXT INDEX ON [TableName] SET STOPLIST = SYSTEM'
DECLARE @_SQLFullTextStopList NVARCHAR(4000)
IF object_id('tempdb..#tmpsetstoplist') IS NOT NULL
DROP TABLE #tmpsetstoplist;

CREATE TABLE #tmpsetstoplist (
TableName [nvarchar] (250)
);

DECLARE cur CURSOR
FOR select object_NAME(sys.fulltext_indexes.object_id) as TableName from sys.fulltext_indexes where stoplist_id != 0

OPEN cur;
DECLARE @TableName [nvarchar](250);
FETCH NEXT FROM cur INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #tmpsetstoplist (TableName)
	VALUES (@TableName);                
	FETCH NEXT
	FROM cur
	INTO @TableName;
END;
CLOSE cur;
DEALLOCATE cur;

DECLARE cur CURSOR
FOR SELECT TableName FROM #tmpsetstoplist;

OPEN cur;
FETCH NEXT FROM cur INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @_SQLFullTextStopList = N'ALTER FULLTEXT INDEX ON ' + QUOTENAME(@TableName) + ' SET STOPLIST = SYSTEM'
	PRINT (@_SQLFullTextStopList)
	EXEC SP_EXECUTESQL @_SQLFullTextStopList
	FETCH NEXT
	FROM cur
	INTO @TableName;
END;
CLOSE cur;
DEALLOCATE cur;

-------------------------------------------------------------------------------------

-- DROP FULLTEXT STOPLIST [FullTextStopListName];

IF object_id('tempdb..#dropfulltextstoplist') IS NOT NULL
DROP TABLE #dropfulltextstoplist;

CREATE TABLE #dropfulltextstoplist (
StopListName [nvarchar] (250)
);

DECLARE cur CURSOR
FOR select name from sys.fulltext_stoplists

OPEN cur;
DECLARE @StopListName [nvarchar](250);
FETCH NEXT FROM cur INTO @StopListName;

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #dropfulltextstoplist (StopListName)
	VALUES (@StopListName);                

	FETCH NEXT
	FROM cur
	INTO @StopListName;
END;
CLOSE cur;
DEALLOCATE cur;

DECLARE cur CURSOR
FOR SELECT StopListName FROM #dropfulltextstoplist;

OPEN cur;
FETCH NEXT FROM cur INTO @StopListName;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @_SQLFullTextStopList = N'DROP FULLTEXT STOPLIST ' + QUOTENAME(@StopListName) + ';'
	PRINT (@_SQLFullTextStopList)
	EXEC SP_EXECUTESQL @_SQLFullTextStopList
	FETCH NEXT
	FROM cur
	INTO @StopListName;
END;
CLOSE cur;
DEALLOCATE cur;
--END FULLTEXT STOPLIST REMOVAL



-- Start script: Turn off DMF change tracking for all entities

BEGIN TRY

-- 1. Find and drop triggers
DECLARE TriggerCursor CURSOR
FOR SELECT TriggerName FROM AifSqlCtTriggers

OPEN TriggerCursor

DECLARE @TriggerName NVARCHAR(200)
DECLARE @SqlStmt NVARCHAR(1000)

FETCH NEXT FROM TriggerCursor INTO @TriggerName
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SqlStmt = 'DROP TRIGGER ' + @TriggerName
	PRINT @SqlStmt
	EXECUTE sp_executesql @SqlStmt
	FETCH NEXT FROM TriggerCursor INTO @TriggerName
END

CLOSE TriggerCursor
DEALLOCATE TriggerCursor

-- 2. Delete rows from AifSqlCtTriggers
DELETE AifSqlCtTriggers WHERE Scope LIKE '%Export'


-- 3. Turn off table level change tracking
DECLARE TableCursor CURSOR
FOR SELECT TableName FROM AifSqlChangeTrackingEnabledTables 

OPEN TableCursor

DECLARE @TableName1 NVARCHAR(162)

FETCH NEXT FROM TableCursor INTO @TableName1
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SqlStmt = 'ALTER TABLE ' + @TableName1 + ' DISABLE CHANGE_TRACKING'
	PRINT @SqlStmt
	EXECUTE sp_executesql @SqlStmt
	FETCH NEXT FROM TableCursor INTO @TableName1
END

CLOSE TableCursor
DEALLOCATE TableCursor

-- 4. Clean up tables
DELETE FROM AIFSqlChangeTrackingEnabledTables

DELETE FROM AIFSqlCdcEnabledTables WHERE Scope LIKE '%Export'

UPDATE DMFEntity SET DMFChangeTrackingType = 0

END TRY
BEGIN CATCH
	PRINT 'exception happened'
    SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage; 
END CATCH

-- End script: Turn off DMF change tracking for all entities

It is important to do IIS reset and restart the virtual machine after running the script.

Advertisement