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 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]