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:
*** 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:
- Now you can open the renamed package and extract the model.xml file into another folder:
- Open the model.xml file with Notepad or another text editor and search for "Grant.KillDatabaseConnection.Database".
- 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:
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 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.
Top comments (0)