Share via

Moving the MSDI database to a new Microsoft SQL Server instance

  • Article

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="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="www.microsoft.com/2009/Dynamics/Integration"; replace value of (/SettingsCollection/x:ArrayOfSettingsValue/x:SettingsValue[x:Attributes="Password"]/x:Value/text())[1] with ""')

Comments

  • Anonymous
    March 02, 2014
    The comment has been removed

  • Anonymous
    March 03, 2014
    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

  • Anonymous
    March 03, 2014
    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

  • Anonymous
    March 04, 2014
    Hi, any informations how to move DB to different server for the v2 of Connector for Microsoft Dynamics ?? Thanks Best Regards.

  • Anonymous
    March 05, 2014
    Hi thehetz or Skyaddict, Could you please give a hand? I am stuck there. thanks, Alan

  • Anonymous
    March 06, 2014
    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

  • Anonymous
    March 06, 2014
    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.

  • Anonymous
    March 06, 2014
    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.

  • Anonymous
    March 06, 2014
    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.

  • Anonymous
    April 15, 2014
    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

  • Anonymous
    April 15, 2014
    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.

  • Anonymous
    April 15, 2014
    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

  • Anonymous
    April 15, 2014
    On my AppConnector, I have a Service "Connector for MDS". He is started..

  • Anonymous
    April 15, 2014
    The service is called "Connector for Microsoft Dynamics Service" it will be on your app server.

  • Anonymous
    April 16, 2014
    Okay, he is started with an Specific account. But I try with Network Service.. but it don't work.

  • Anonymous
    April 21, 2014
    @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.

  • Anonymous
    April 28, 2014
    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,

  • Anonymous
    April 28, 2014
    @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?

  • Anonymous
    April 28, 2014
    @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...

  • Anonymous
    April 28, 2014
    The comment has been removed

  • Anonymous
    April 28, 2014
    @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"/> I have changed the port on my Client Config files :  File : Microsoft.Dynamics.Integration.Client.exe.config  - <endpoint address="http://localhost:4742/configuration" 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

  • Anonymous
    April 28, 2014
    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?

  • Anonymous
    April 28, 2014
    @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.

  • Anonymous
    May 18, 2014
    Hi Thehetz, Could you please take a look at this question community.dynamics.com/.../127364.aspx ? Thank you, Feng

  • Anonymous
    July 31, 2014
    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.

  • Anonymous
    January 27, 2015
    The comment has been removed

  • Anonymous
    January 27, 2015
    @Mike - have you reset the passwords in the adapters' settings for the individual integrations? (Not just in the default adapter settings)

  • Anonymous
    January 27, 2015
    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

  • Anonymous
    January 27, 2015
    @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.

  • Anonymous
    January 27, 2015
    Thanks Thehetz, that did the trick.  Amazing how many hours I struggled with this and it was something so simple...

  • Anonymous
    January 27, 2015
    @Mike - glad this fixed your issue :)

  • Anonymous
    April 08, 2015
    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?

  • Anonymous
    April 08, 2015
    @Francis - is the Connector service running?

  • Anonymous
    April 08, 2015
    thehetz, Yes it is.

  • Anonymous
    April 08, 2015
    @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.

  • Anonymous
    April 08, 2015
    May I try to re-install it?

  • Anonymous
    April 08, 2015
    @Francis - that could be an option as well, but I do suggest contacting support first.

  • Anonymous
    April 09, 2015
    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.

  • Anonymous
    April 09, 2015
    @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.

  • Anonymous
    April 15, 2015
    @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.

  • Anonymous
    April 15, 2015
    @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.

  • Anonymous
    May 31, 2015
    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

  • Anonymous
    May 12, 2016
    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

    • Anonymous
      May 12, 2016
      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.