Moving the MSDI database to a new Microsoft SQL Server instance


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 

 

/*

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())[1]
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())[1]
with ""')

 

Comments (44)

  1. Alan says:

    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

  2. Skyaddict says:

    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 "DOMAINusername"

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

    GO

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

    GO

  3. Alan says:

    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

  4. Marco Marzola says:

    Hi,

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

    Thanks

    Best Regards.

  5. Alan says:

    Hi thehetz or Skyaddict,

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

    thanks,

    Alan

  6. Skyaddict says:

    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

  7. Alan says:

    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.

  8. Skyaddict says:

    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.

  9. Skyaddict says:

    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.

  10. Tomas says:

    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

  11. Skyaddict says:

    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.

  12. Tomas says:

    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

  13. Tomas says:

    On my AppConnector, I have a Service "Connector for MDS". He is started..

  14. Skyaddict says:

    The service is called "Connector for Microsoft Dynamics Service" it will be on your app server.

  15. Tomas says:

    Okay, he is started with an Specific account.

    But I try with Network Service.. but it don't work.

  16. thehetz says:

    @Tomas – the Connector for Microsoft Dynamics service must be run as a specific user, it does not support Network Service account.  The permissions required for this service in SQL server are listed in the install guide.

  17. Tomas says:

    Hi,

    Sorry, I don't see that this webpage have 2 page of comments..

    So, I reboot my AppSever where is my Connector Application.

    I run my Connector Service with a specific user.

    My Connector Service running.. and after I have a message : "The Connector Service on Local Machine have running and then stopped.Some services stop automatically if they are not used by other services or programs"

    And my Connector Service has stopped.

    An idea ?

    Thanks

    B.R,

  18. thehetz says:

    @Tomas – does the user that is running the service have the proper rights to the MSDI database?  And also to the file system?  These permissions are listed in the Connector Install Guide.  Is there anything in the event log for the service?

  19. Tomas says:

    @thehetz – Thanks for your answers.

    My user is DBO in the SQL Server and in the SQL Database MSDI.

    I have look ont the Connector Install Guide(page 14) and I have the good requirments.

    In the Log, never error but

    In the Observer Event, I have an error :

    ————————————————–

    The service don't be started.

    System.ServiceModel.AddressAlreadyInUseException:

    HTTP n'a pas pu inscrire l'URL http://+:4740/logging/.

    A other application has already registered this URL with HTTP.SYS.

    —> System.Net.HttpListenerException:

    Listening prefix 'http://+:4740/logging/' failed because the prefix is in conflict with an existing record on the computer.

      à System.Net.HttpListener.AddAllPrefixes()

      à System.Net.HttpListener.Start()

      à System.ServiceModel.Channels.SharedHttpTransportManager.OnOpen()

      — Fin de la trace de la pile d'exception interne —

      à System.ServiceModel.Channels.SharedHttpTransportManager.OnOpen()

      à System.ServiceModel.Channels.TransportManager.Open(TransportChannelListener channelListener)

      à System.ServiceModel.Channels.TransportManagerContainer.Open(SelectTransportManagersCallback selectTransportManagerCallback)

      à System.ServiceModel.Channels.HttpChannelListener.OnOpen(TimeSpan timeout)

      à System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeou…

    ————————————————–

  20. thehetz says:

    @Tomas – I would suggest opening a support incident for this.  Is it possible that you have two instances of the Connector service on that box?  There *might* be another service using that port as well and support could help you with that I think.

  21. Master Escuelito says:

    @thehetz :

    In fact, I have to service that all the port 4740 : The Connector Service and the TCP Service.

    I have changed the port on my Connector Config Files :

     File : ConnectorServiceHost.exe.config

     – <add key="DefaultBaseAddress" value="http://localhost:4742"/&gt;

    I have changed the port on my Client Config files :

     File : Microsoft.Dynamics.Integration.Client.exe.config

     – <endpoint address="http://localhost:4742/configuration&quot;

    And on my Oberserv Event, I have an Other Error :

    Caught Exception: [System.ServiceModel.EndpointNotFoundException]

    It was no endpoint listening on http://localhost:4742/configuration can accept the message. This is often due to an incorrect address or SOAP action. If present, see InnerException element for more information.

    An idea ?

    Thanks,

    B.R

  22. Master Escuelito says:

    Following error message: Inner Exception: [System.Net.Sockets.SocketException] No connection Could Be Made Because The target machines Actively Refused it 127.0.0.1:4742

    Deleguation problem?

  23. thehetz says:

    @Master – to update the port that the connector service is using, you not only need to update the client and service configuration files but you will also need to update the port that is registered with WCF by running the following command:

    netsh http add urlacl url=http://+:0000/ user="domainuser"

    Where 0000 is your port number that you wish to change to and the "domainuser" is the account to run the service as.

    I would strongly encourage you to contact support to assist with this process.

  24. Alan says:

    Hi Thehetz,

    Could you please take a look at this question community.dynamics.com/…/127364.aspx ?

    Thank you,

    Feng

  25. Skyaddict says:

    I have updated the query in this blog post to remove the previously encrypted passwords from the MSDI Database. This will keep the service from writing decryption errors to the event log, while you update the passwords in the client.

  26. Mike says:

    I've followed these instructions without success.  I'm getting an authentication error against the GP web service from the connector.  When I test the connection in the client I receive a success message, but when the service tries to authenticate it fails.  Here is the error: Either the client credential was invalid or there was an error collecting the client credentials by the SSPI.  Web services (2013 R2 latest version) is installed on the same computer as the connector service (version 3.4).  The MSDI database was moved from Windows server 2008 running SQL 2008 to Server 2012 R2 running SQL 2014.  Any assistance would be much appreciated.

    Thank you,

    Mike

  27. thehetz says:

    @Mike – have you reset the passwords in the adapters' settings for the individual integrations? (Not just in the default adapter settings)

  28. Mike says:

    Hi Thehetz,

    Thanks for the quick response.  I ran the script you posted and then updated the passwords in the adapter settings on the client.  Am I still missing something?

    Thanks,

    Mike

  29. thehetz says:

    @Mike – when you say in the client, did you click on the adapter settings menu item on the top menu and update them there?  You will also need to go to the settings node for each integration that you have and update the adapter settings there as well.

  30. Mike says:

    Thanks Thehetz, that did the trick.  Amazing how many hours I struggled with this and it was something so simple…

  31. thehetz says:

    @Mike – glad this fixed your issue 🙂

  32. Francis says:

    Hi thehetz,

    I did the process as described, but now the client dont open, I get a not responding error from Windows. Anyway to fix that?

  33. thehetz says:

    @Francis – is the Connector service running?

  34. Francis says:

    thehetz,

    Yes it is.

  35. thehetz says:

    @Francis – check the Windows event log for error coming from the client.  I would suggest stopping the Client task in Task Manager and then re-launching.  if that does not work you might need to open a support incident.

  36. Francis says:

    May I try to re-install it?

  37. thehetz says:

    @Francis – that could be an option as well, but I do suggest contacting support first.

  38. Fritz says:

    Hi,

    Is there a way to configure the MSDI database for SQL mirroring? Our database is setup for mirroring, but whenever the database falls from one server to the other, the connector stops working unless we take the MSDI back to the main SQL server.

  39. thehetz says:

    @Fritz – I suspect that this is happening because the adapter passwords would need to be updated in the replicated database.  You'll need to open the Connector client and set the passwords for the adapter users for each integration.  This scenario has not been explicitly tested however.

  40. Fritz says:

    @thehetz

    I cannot open the connector client once the database is on the mirrored server. I get a message Access to Server Denied. Ensure that db_datawriter and db_datareader role in the MSDI database.

    We have looked at this and the user running the connector application already has the roles for the MSDI database.

    I see no documentation on SQL mirroring for the connector.

  41. thehetz says:

    @Fritz – does the user that the Connector service is running as have these permissions?  I would encourage you to open a support request as we do not have documentation on the mirroring topic.

  42. JanGD says:

    Hi Hetz,

    you forgot something:

    In a Disaster Recovery scenario you can't only remove the passwords. Since the maps are still active, they hammer the AD with false credentials, so the AD locks the account.

    Kind regards,

    Jan

  43. Urs Wedershoven says:

    Hello altogether,

    in a customer’s szenario we would like to build a new test environment from the existing live systems. We have a NAV Database on one side and a SharePoint on the other (via a custom adapter).
    What we are trying is to copy both databases (NAV/SP) so that we have the existing information including the integrationIDs of all of our information.

    Would it be possible to simply copy the MSDI database to another databasename on the same SQL-Server, too? Do we need use your script to update the keys or shoud the simple copy with configuring the service do the trick? Installing a new Connector with a new database will result in massive synchronization time and effort.

    Thanks in advance.

    Urs

    1. thehetz says:

      You will need to update the keys as far as I understand it. Since you are not moving servers, you might not have to, so it may be worth trying the move out first before updating the keys.