Connector -- Integrating Dynamics ERP with Dynamics CRM

Connector for Microsoft Dynamics is an integration solution which is targeted specifically at the Microsoft Dynamics product family. Connector provides an out of the box integration between Dynamics CRM and Dynamics ERP solutions.

Moving the MSDI database to a new Microsoft SQL Server instance

Moving the MSDI database to a new Microsoft SQL Server instance

Rate This
  • Comments 24

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:

  1. Backup the MSDI database on the Microsoft SQL Server where it currently resides
  2. Restore the MSDI database to the new Microsoft SQL Server instance
  3. If the new Microsoft SQL Server instance is on a different domain than the original Microsoft SQL Server, it may be necessary to set the DB Owner
    1. This allows you to drop and add the security objects
  4. Drop the ConnectorServiceSymmetricKey
  5. Drop the ConnectorServiceCertificate
  6. Drop the database master key from MSDI
  7. Create a new database master key
    1. The password must meet Windows policy for length and complexity
    2. Make note of this password so next time the master key can be restored to a new Microsoft SQL Server instance instead of being recreated
  8. Recreate ConnectorServiceCertificate
  9. Recreate ConnectorServiceSymmetricKey
  10. Grant Service account access to new Certificate and key

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" 

USE MSDI

GO

--PRINT N'Change DBOwner to $(NewDBOLogin)...'

--GO

--EXEC SP_ChangeDbOwner '$(NewDBOLogin)'

--GO

PRINT N'Drop ConnectorServiceSymmetricKey...'

GO

DROP SYMMETRIC KEY ConnectorServiceSymmetricKey

GO

PRINT N'Drop ConnectorServiceCertificate...'

GO

DROP CERTIFICATE ConnectorServiceCertificate

GO

PRINT N'Drop MSDI Master Key...'

GO

DROP MASTER KEY

GO

PRINT N'Creating Master Key...'

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD= N'$(DatabaseMasterKey)'

GO

PRINT N'Creating [ConnectorServiceCertificate]...'

GO

CREATE CERTIFICATE [ConnectorServiceCertificate]

    AUTHORIZATION [dbo]

    WITH SUBJECT = N'Certificate for symmetric key encryption - for use by the connector service.';

GO

PRINT N'Creating [ConnectorServiceSymmetricKey]...'

GO

CREATE SYMMETRIC KEY [ConnectorServiceSymmetricKey]

    AUTHORIZATION [dbo]

    WITH ALGORITHM = AES_256

    ENCRYPTION BY CERTIFICATE [ConnectorServiceCertificate]

GO

GRANT VIEW DEFINITION ON SYMMETRIC KEY::ConnectorServiceSymmetricKey TO [$(ServiceLogin)]

GO

GRANT CONTROL ON CERTIFICATE::ConnectorServiceCertificate TO [$(ServiceLogin)]

GO

 

 

PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the link in the Related Links section (scroll up, on right hand side) to ask on the Dynamics Communities. If you ask on the Communities, others in the community can respond and the answers are available for everyone in the future.
Leave a Comment
  • Please add 4 and 7 and type the answer here:
  • Post
  • Hi thehetz,

    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)

    any advices?

    thanks,

    Alan

  • Alan,

    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.

    :setvar ServiceLogin "DOMAIN\username"

    GRANT VIEW DEFINITION ON SYMMETRIC KEY::ConnectorServiceSymmetricKey TO [$(ServiceLogin)]

    GO

    GRANT CONTROL ON CERTIFICATE::ConnectorServiceCertificate TO [$(ServiceLogin)]

    GO

  • Hi Skyaddict,

    thank you.

    I tried your updated script. Unfortunately I still got the same error.

    Could you please take a look again?

    thanks,

    Alan

  • Hi,

    any informations how to move DB to different server for the v2 of Connector for Microsoft Dynamics ??

    Thanks

    Best Regards.

  • Hi thehetz or Skyaddict,

    Could you please give a hand? I am stuck there.

    thanks,

    Alan

  • Hi Alan,

    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

    DROP SYMMETRIC KEY ConnectorServiceSymmetricKey

    GO

    PRINT N'Drop ConnectorServiceCertificate...'

    GO

    DROP CERTIFICATE ConnectorServiceCertificate

    GO

    PRINT N'Drop MSDI Master Key...'

    GO

    DROP MASTER KEY

  • Hi Skyaddict,

    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.

  • Alan,

    Alan,

    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.

  • Marco,

    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.

  • Hi,

    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 ?

    Thanks,

    Best Regards

  • 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 ?

    Thanks

  • 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.

Page 1 of 2 (24 items) 12