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="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 ""')