An error was encountered during the DB Restoration process from T2 to T1 in D365FO, specifically identified by SQL error codes SQL72014 and SQL7204

I have been attempting to restore the database from T2 to T1 since yesterday. However, I encountered an error during the restoration process.

"*** Error importing database:Could not import package.Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 4630, Level 16, State 1, Line 1 The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server. Alternatively, use the server level 'ALTER ANY CONNECTION' permission.Error SQL72045: Script execution error. The executed script:GRANT KILL DATABASE CONNECTION TO [ms_db_configreader];"

• It was not surprising to encounter this issue, as a similar occurrence took place in 2023 regarding the auto-drop problem with the bacpac file in SSMS version.

Following my investigation, I have determined that the same resolution will be applied to address this issue. Please refer to the resolution provided below:

• convert your bacpac file to .zip (just edit the file extension and change .bacpac to .zip)
• Copy the model.xml from the zip folder to some location. Revert back the .zip to .bacpac Now edit the model.xml file and find the SqlPermissionStatement"Grant.KillDatabaseConnection" element and remove the whole <Element>...</Element> from the xml file and save it.
• While using the sqlpackage.exe import action, add /mfp:"YourCopiedModel.xmlPath" to the commands, this will allow you use the edited model.xml instead of original one in the bacpac.

Additionally, the elements to be removed from the model.xml file are listed below:

• I have identified two elements within our bacpac model file that I believe may resolve the error if they are removed.

<Element Type="SqlPermissionStatement" Name="[Grant.KillDatabaseConnection.Database].[ms_db_configreader].[dbo]">
<Property Name="Permission" Value="1114" />
<Relationship Name="Grantee">
<Entry>
<References Name="[ms_db_configreader]" />
</Entry>
</Relationship>
<Relationship Name="SecuredObject">
<Entry>
<References Disambiguator="1" />
</Entry>
</Relationship>
</Element>
<Element Type="SqlPermissionStatement" Name="[Grant.KillDatabaseConnection.Database].[ms_db_configwriter].[dbo]">
<Property Name="Permission" Value="1114" />
<Relationship Name="Grantee">
<Entry>
<References Name="[ms_db_configwriter]" />
</Entry>
</Relationship>
<Relationship Name="SecuredObject">
<Entry>
<References Disambiguator="1" />
</Entry>
</Relationship>
</Element>        

Furthermore, I have identified a simpler step than the one mentioned above, outlined below:

• The refresh process was completed after removing the ms_db_configreader and ms_db_configwriter roles from the Tier 2 environment.
• I have conducted tests on both scenarios, and I can affirm that they are functioning properly.

This issue has been acknowledged by Microsoft, and the Product Group is actively working on resolving it.

Please refer to this thread for visual guidance as well.

(6) Viva Engage : Dynamics 365 and Power Platform Preview Programs (yammer.com)