Fix "The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server" while importing a D365FO .bacpac
Tags: sql, d365fo, sqlpackage, erp
Published: 2024-05-27 02:58:30

Recently, while making a copy of production database to my development environment, I came across an unusual error when importing the .bacpac file using [SqlPackage](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16):

```
*** 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];
```

After some digging, I was abble to resolve the problem following the solution below.

## Solution

This solution will help you to edit **model.xml** file contained inside the package and remove/replace the KILL ANY CONNECTION statement.

- Make a backup of your original .bacpac package.
- Go to your package in File Explorer and change his file extension from .bacpac to .zip. The renamed file should look like this:

![Renamed .bacpac package.](https://media.licdn.com/dms/image/D4D12AQG7lxJZh90pOQ/article-inline_image-shrink_1500_2232/0/1715184158161?e=1722470400&v=beta&t=qOOFwgVtGAzn3-MnJ_AoRpwDPeasV5UwIx_RWDNOLO4)

- Now you can open the renamed package and extract the model.xml file into another folder:

![Modify this model.xml file.](https://media.licdn.com/dms/image/D4D12AQF2KrNqlNRUrQ/article-inline_image-shrink_1500_2232/0/1715184194484?e=1722470400&v=beta&t=8J5TmitRNPUmcD69Es_dsxvBNwB81ZwxbBQO9UAptDQ)

- Open the **model.xml** file with Notepad or another text editor and search for "**Grant.KillDatabaseConnection.Database**".

![Search for these SQL permission statement elements.](https://media.licdn.com/dms/image/D4D12AQFKmSRs8c6qIA/article-inline_image-shrink_1500_2232/0/1715184349304?e=1722470400&v=beta&t=fO3x6sBIbmh1-XjdF9Rz1JKG-AV70EkKFnRrUxlYry4)

- The search should return one or more **SqlPermissionStatement** elements. You can remove¹ these elements from the **model.xml** file.
- After this, save and close the modified **model.xml** file.
- Then copy and replace the modified **model.xml** into the zipped package and change the package file extension from .zip to .bacpac again. Your files should look like this:

![The .bacpac package and modified model.xml we'll use.](https://media.licdn.com/dms/image/D4D12AQGQkyFLUzt_vA/article-inline_image-shrink_1500_2232/0/1715184392375?e=1722470400&v=beta&t=s_vFlW_q4HAnuaTdd-gQYFwkHKVaisW4dKTbCHA6tKQ)

Here comes the fun part, usually you would have to extract the package completely, modify the model.xml file, generate a new checksum, update the **Origin.xml** file with the new checksum value and then compress the package again.

Instead, you can skip all this work by using the [SqlPackage import](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-import?view=sql-server-ver16) parameter **/ModelFilePath** (/mfp):

```
.\sqlpackage.exe /a:import /sf:"C:\Users\localadmin\Desktop\Goldenbackup.bacpac" /mfp:"C:\Users\localadmin\Desktop\model.xml" /tsn:localhost /tdn:AxDB_fromGolden  /ttsc:true /p:CommandTimeout=0
```

Parameters description:
- **/sf**: put your .bacpac package file path here.
- **/mfp**: overrides the model.xml in the source file with the modified model.xml.

Now the .bacpac package will be imported successfully. 😊

## Final considerations

¹ Instead of removing the **SqlPermissionStatement** elements, you can also replace the permission "**Grant.KillDatabaseConnection.Database**" with "**Grant.AlterAnyConnection.Database**".
² This solution also solves the error "**Operation Failed: File contains corrupted data**" while importing a .bacpac package in SSMS.