Error message: Receive the following error while importing a BACPAC to a Dev environment
Error:
*** Error importing database: Could not import package.
Error SQL72014: Framework 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];
Some hints are helping us to resolve the issue.
1. Rename your Downloaded .bacpac file to .zip
Go to the folder where you have saved your BACPAC file and rename the BACPAC file with a .zip extension
Press "Yes" to convert the .bacpac file to zip file.
2. open the zip file and find the model.xml file inside,
3. copy the model.xml it out to somewhere you like
4. Open the model.xml file in Notepad editor, which is pretty big.
5. Find string "Grant.KillDatabaseConnection"
6. Delete the entire Element tag which contains " Grant.KillDatabaseConnection "
7. After the deletion of the element type mentioned in 6. it looks like the below.
8. Download the latest SqlPackage file from the below link:
https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16
9. Rename the file modelCopy.xml and paste it into the SqlPackage folder.
10. Rename the zip file to bacpac file (reverting back the changes from 1)
11. Login to the command prompt as administrator.
12. Navigate to the downloaded SQLPackage folder and run the below command.
Package Execution Command Script :
"J:\DBRestore\sqlpackage-win-x64-en-162.2.111.2\sqlpackage.exe" /a:import /sf:"J:\DBRestore\UATbackup.bacpac" /tsn:localhost /tdn:AxDB16052024 /TargetUser:axdbadmin /TargetPassword:LCSSQLPassword /TargetEncryptConnection:False /TargetTrustServerCertificate:True /p:CommandTimeout=30000 /mfp:"J:\DBRestore\ModelCopy.xml"
or
1)cd J:\DBRestore\\sqlpackage-win-x64-en-162.2.111.2
2) SqlPackage.exe /a:import /sf:"J:\DBRestore\UATbackup.bacpac" /tsn:localhost /tdn:AxDB16052024 /TargetUser:axdbadmin /TargetPassword:LCS SQL DB Password /TargetEncryptConnection:False /TargetTrustServerCertificate:True /p:CommandTimeout=30000 /mfp:"J:\DBRestore\ModelCopy.xml"
This steps resolve the issue of Importing the Bacpac file into D365 F&O Tier 1 environments
