Fix "The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server" while importing a D365FO .bacpac (10.0.39)

Recently, I encountered an unusual error while importing a .bacpac file into my development environment using SqlPackage. This occurred during the process of copying our production database.

Solution

This solution will guide you on how to edit the model.xml file contained within the package to remove or replace the KILL ANY CONNECTION statement.

Step I:

First, make a backup of your original .bacpac package. Then, navigate to your package in File Explorer and change its file extension from .bacpac to .zip. The renamed file should appear as follows:

Step II:

Now open the zip file and copy the model.xml file into another folder

Step III:

1) Again renamed the UATbackup.zip file to UATbackup.bacpac.

2) Open the copy model.xml file with Notepad or another text editor and then search for "Grant.KillDatabaseConnection.Database".

3) Then delete all element node which include "Grant.KillDatabaseConnection.Database" in whole file

Step IV:

1) Save the model.xml file.

2) Now we need to add /ModelFilePath (/mfp) in the end of command.

/mfp option overrides the model.xml in the source file with the external modified model.xml.

Run command and successfully imported database.

Conclusion:

Rather than removing the SqlPermissionStatement elements, we can replace the permission "Grant.KillDatabaseConnection.Database" with "Grant.AlterAnyConnection.Database." This approach also addresses the "Operation Failed: File contains corrupted data" error that occurs when importing .bacpac package in SSMS.