When Connector for Microsoft Dynamics (version 3.x) is installed a database master key is created (for encryption of passwords saved with adapter settings) using a random password. The downside of using a random password is the owner of the database will be unable to back up the master key allowing the key to be restored to a different Microsoft SQL Server. In order to restore the MSDI database to a different Microsoft SQL Server instance you will need to drop and add all the database security objects necessary to encrypt passwords on the new instance of the MSDI database. This will cause all password data in the adapters to be lost and need to be reentered before integrations can be run successfully after the move. The steps needed to move the MSDI database to a different Microsoft SQL Server are as follows:
Below is the SQLCMD script necessary to complete all of the steps listed above. In order to run this in Microsoft SQL Server Management Studio you will need to use SQLCMD mode, which is found by clicking the Query menu and selecting SQLCMD Mode. If the database owner needs to be changed, uncomment the currently commented out lines of the script below.
:setvar NewDBOLogin "DOMAIN\username"
:setvar ServiceLogin "DOMAIN\username"
:setvar DatabaseMasterKey "your master key password"
--PRINT N'Change DBOwner to $(NewDBOLogin)...'
--EXEC SP_ChangeDbOwner '$(NewDBOLogin)'
PRINT N'Drop ConnectorServiceSymmetricKey...'
DROP SYMMETRIC KEY ConnectorServiceSymmetricKey
PRINT N'Drop ConnectorServiceCertificate...'
DROP CERTIFICATE ConnectorServiceCertificate
PRINT N'Drop MSDI Master Key...'
DROP MASTER KEY
PRINT N'Creating Master Key...'
CREATE MASTER KEY ENCRYPTION BY PASSWORD= N'$(DatabaseMasterKey)'
PRINT N'Creating [ConnectorServiceCertificate]...'
CREATE CERTIFICATE [ConnectorServiceCertificate]
WITH SUBJECT = N'Certificate for symmetric key encryption - for use by the connector service.';
PRINT N'Creating [ConnectorServiceSymmetricKey]...'
CREATE SYMMETRIC KEY [ConnectorServiceSymmetricKey]
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE [ConnectorServiceCertificate]
GRANT VIEW DEFINITION ON SYMMETRIC KEY::ConnectorServiceSymmetricKey TO [$(ServiceLogin)]
GRANT CONTROL ON CERTIFICATE::ConnectorServiceCertificate TO [$(ServiceLogin)]
The following two statements will remove any previously encrypted passwords from the database. Doing this will keep the service from logging errors in the event viewer while you update the passwords in the client
UPDATE Connector.Adapter SET Settings.modify('declare namespace x="http://www.microsoft.com/2009/Dynamics/Integration"; replace value of (/SettingsCollection/x:ArrayOfSettingsValue/x:SettingsValue[x:Attributes="Password"]/x:Value/text()) with ""')
GO UPDATE Connector.MapCategoryAdapterSettings SET Settings.modify('declare namespace x="http://www.microsoft.com/2009/Dynamics/Integration"; replace value of (/SettingsCollection/x:ArrayOfSettingsValue/x:SettingsValue[x:Attributes="Password"]/x:Value/text()) with ""')
My client's IT upgrade their SQL server and moved the MSDI db to another instance.
I tried your script to re-create master key.
But I still cannot open the Connector client. from event viewer, I see below error info.
The encrypted setting, User password, could not be decrypted. --- Exception Dump --- Caught Exception: [System.Security.Cryptography.CryptographicException] Encrypted data was invalid and cannot be decrypted. Stack trace: at Microsoft.Dynamics.Integration.DataAccessLayer.DataProtection.Decrypt(Byte data) at Microsoft.Dynamics.Integration.DataAccessLayer.DataProtection.DecryptSettings(XElement settings)
The service account needs to be granted access to the newly created keys run the following to move past this issue. I will work with TheHetz to get the script on the post updated.
I tried your updated script. Unfortunately I still got the same error.
Could you please take a look again?
any informations how to move DB to different server for the v2 of Connector for Microsoft Dynamics ??
Hi thehetz or Skyaddict,
Could you please give a hand? I am stuck there.
Sorry for the delayed response, we have been working at a conference this week.
Another option to try would be:
1. Uninstall Connector
2. Drop the master key using the script below
3. Install Connector pointing at the msdi database that you just removed the keys from. The install should recreate the missing information and update the permissions on those objects
Thank you for your response.
My systems are AX 2009 and CRM 2013 online. Dynamics Connector V3.1 (upgraded from V2).
And made some changes on some maps in the connector before.
Are you sure that uninstalling,re-installing connector would not lose those map configurations?
And I see Connector V3.2 is released. Is it stable? how about if I upgrade V3.1 to V3.2? would that re-create the Master Key?
Thanks in advance.
Uninstalling the connector does not delete the database so you won't loose your maps. Upgrading to 3.2 would be simpler and would recreate the keys if they don't already exist.
So run the script from my last post and then run the upgrade.
The client from Connector V2 handled the encryption instead of the database, so Moving the database is a simple backup and restore. Once moved you will need to update the connection strings in the client and service config files to point to the new server.
I use a Dynamics Connector V2.
My application is installed on a Serveur "AppConnector" and the SQL (MSDI Base) in on the otrher Server "SQLConnector".
With a V2 Version, all is OK. But when I install the V3, impossible to open the Connector. I have an error message : "The connector for MS Dyn. service could not be contacted. The client will be closed".
And on the Log, i have this message : "There was no endpoint listening at http://localhost:4740/configuration that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details. No connection could be made because the target machine actively refused it 127.0.0.1:4740."
An idea to resolvt my problem ?
Thomas it sounds like you v3 service did not start up after install. Go to Services and make sure your connector service is running. Also look in the event log for errors from the connector service.
Thanks for this informations.
But I think that the service isn't installed.
Because he is not on the AppConnector and not in the SQLConnector.
Maybe, I don't found the good name of this services..
The services must be on the Server App or on the Server SQL ?
What must be the name of this service ?
On my AppConnector, I have a Service "Connector for MDS". He is started..
The service is called "Connector for Microsoft Dynamics Service" it will be on your app server.
Okay, he is started with an Specific account.
But I try with Network Service.. but it don't work.