DB Restore error "KILL DATABASE CONNECTION' is not supported in this version of SQL Server".

on

Error message : Receive the following error while importing a bacpac to a Dev environment

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


Steps to resolve the issue:

Step 1: 

Rename the 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.

Step 2 : Open zip file ,  


Step 3: Copy the model.xml file into the different location

Step 4: Open it in notepad editor.






Step 5.  Delete the entire Element tag which contains " Grant.KillDatabaseConnection " 



 Step 6. After deletion of the element type mentioned in Step 5. it look like below.


Step 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



Step 7:  Rename the the file modelCopy.xml  and paste it in the SqlPackage folder.




Step 8: Rename the zipfile to bacpac file  (reverting back the changes from step 1)





Step 8: Login to command prompt as administrator. 

Step 9:  Navigate to the downloaded SQLPackage folder and run the below command.



1)cd C:\Users\Adminecaa6e11d9\Downloads\sqlpackage-win-x64-en-162.2.111.2

2) SqlPackage.exe /a:import /sf:"J:\MSSQL_BACKUP\AxDB.bacpac" /tsn:localhost /tdn:AXDB_New /p:CommandTimeout=1200 /TargetTrustServerCertificate:True /mfp:"J:\Backup\model.xml"

0r

Use dev environment sql db username and password and add in below command

"C:\Users\Adminecaa6e11d9\Downloads\sqlpackage-win-x64-en-162.2.111.2\sqlpackage.exe" /a:import /sf:"‪J:\Backup\ERPUATbackup.bacpac" /tsn:localhost /tdn:AxDB1652024 /TargetUser:axdbadmin /TargetPassword:***=TQZ@cvI@an!**BS4TGkI!*Oec7Xdl /TargetEncryptConnection:False /TargetTrustServerCertificate:True /p:CommandTimeout=30000 /mfp:‪"‪J:\Backup\model.xml"

Comments

Post a Comment