Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / Facing error on conver...
Finance forum
Suggested answer

Facing error on converting bacpac to bak in D365 f&o (10.0.39)

Posted on by 69
Hi, 

I need to import sandbox environment db backup file (bacpac.) to cloud hosted environment (bak.). While converting it through sql.net tool using command prompt facing below error.
 
*** 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];


sandbox environment version - 10.0.39
Cloud hosted environment version - 10.0.39

Can someone guide me to resolve it.

Thanks and Regards,
Rubalingam.S
  • LH-23052009-0 Profile Picture
    LH-23052009-0 2 on at
    Facing error on converting bacpac to bak in D365 f&o (10.0.39)
    I'm running into this issue as well. I've ran the PowerShell cmdlet that Florian has suggested to repair the model file but now it's saying the calculated checksum for model.xml in the package is different from the saved checksum. How do I repackage the new model.xml into the bacpac? 
  • Suggested answer
    Florian Hopfner Profile Picture
    Florian Hopfner 2,440 on at
    Facing error on converting bacpac to bak in D365 f&o (10.0.39)
    Since yesterday, you can also use version 0.7.11 (or later) of d365fo.tools to run the Repair-D365BacpacModelFile command instead of using the script I suggested.
  • Migi Profile Picture
    Migi 124 on at
    Facing error on converting bacpac to bak in D365 f&o (10.0.39)
    Finally import worked for me too. I had to remove all the GRANT KILL DATABASE -elements. 
    Then full build and sync.
  • CU15052006-0 Profile Picture
    CU15052006-0 5 on at
    Facing error on converting bacpac to bak in D365 f&o (10.0.39)
    I tried Florian Hopfner's suggestion which seemed to work correctly for me. I was able to import the .bacpac file after multiple attempts. I did download the latest version of SqlPackage, but was still getting the same error.

    "You can also try PowerShell script to remove AutoDrop and Grant.KillDatabaseConnection.Database from Bacpac model file (github.com), which automates the removal of the tags in the .bacpac model.xml. "
  • Suggested answer
    Mohamed Amine Mahmoudi Profile Picture
    Mohamed Amine Mahmoudi 7,659 User Group Leader on at
    Facing error on converting bacpac to bak in D365 f&o (10.0.39)
    Hi,
     
    To resolve this issue you must download the last version of SqlPackage.
     
    Best regards,
    Mohamed Amine MAHMOUDI
  • Migi Profile Picture
    Migi 124 on at
    Facing error on converting bacpac to bak in D365 f&o (10.0.39)
    I have done all the steps to fix the problem, but for some reason I still get the error: 
    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];

    Any ideas? I even exported and downloaded the bacpac again yesterday from the LCS, but certainly it wasn't fixed like someone mentioned. 
  • Suggested answer
    Florian Hopfner Profile Picture
    Florian Hopfner 2,440 on at
    Facing error on converting bacpac to bak in D365 f&o (10.0.39)
    You can also try PowerShell script to remove AutoDrop and Grant.KillDatabaseConnection.Database from Bacpac model file (github.com), which automates the removal of the tags in the .bacpac model.xml.
  • ThiyaguTechz Profile Picture
    ThiyaguTechz 9 on at
    Facing error on converting bacpac to bak in D365 f&o (10.0.39)
    This will take a lot of time to fix this issue.

    1) Rename the DB ( .bacpac to .zip)
    2) Extract ZIP File to a Folder.
    3) Open model.xml in notepad.
    4) Delete the entire Element tag which contains " Grant.KillDatabaseConnection " 
     

            <Element Type="SqlPermissionStatement" Name="[Grant.ViewDatabaseState.Database].[ms_db_configreader].[dbo]">
            </Element>
     


    5) Save Model.xml file
    6) Create new file " Hashvalue.ps1 " , inside "Hashvalue.ps1" paste this script. 

    Hashvalue.ps1 file path c:\temp\Hashvalue

    Script :
     
    $modelXmlPath = Read-Host "model.xml file path"
    $hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")
    $fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)
    $hash = $hasher.ComputeHash($fileStream)
    $hashString = ""
    Foreach ($b in $hash) { $hashString += $b.ToString("X2") }
    $fileStream.Close()
    $hashString

    7) Run the " Hashvalue.ps1 " Script & enter the model.xml file path (Ex: c:\temp\DB\model.xml).
    8) You'll get a hashvale, Copy that hashvalue.
    9) Open " origin.xml " , you'll find hashvalue at the bottom. Change the hashvalue with the one you have copied before.
    10) Then save " origin.xml " 
    11) Select all files add it to .zip , Then change extension to .bacpac .
  • Suggested answer
    ThiyaguTechz Profile Picture
    ThiyaguTechz 9 on at
    Facing error on converting bacpac to bak in D365 f&o (10.0.39)
    This will take a lot of time to fix this issue.

    1) Rename the DB ( .bacpac to .zip)
    2) Extract ZIP File to a Folder.
    3) Open model.xml in notepad.
    4) Delete the entire Element tag which contains " Grant.KillDatabaseConnection " 
     

     


    5) Save Model.xml file
    6) Create new file " Hashvalue.ps1 " , inside "Hashvalue.ps1" paste this script. 

    Hashvalue.ps1 file path c:\temp\Hashvalue

    Script :
     
    $modelXmlPath = Read-Host "model.xml file path"
    $hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")
    $fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)
    $hash = $hasher.ComputeHash($fileStream)
    $hashString = ""
    Foreach ($b in $hash) { $hashString += $b.ToString("X2") }
    $fileStream.Close()
    $hashString

    7) Run the " Hashvalue.ps1 " Script & enter the model.xml file path (Ex: c:\temp\DB\model.xml).
    8) You'll get a hashvale, Copy that hashvalue.
    9) Open " origin.xml " , you'll find hashvalue at the bottom. Change the hashvalue with the one you have copied before.
    10) Then save " origin.xml " 
    11) Select all files add it to .zip , Then change extension to .bacpac .


    Reference Link : https://techcommunity.microsoft.com/t5/azure-database-support-blog/editing-a-bacpac-file/ba-p/368931
    ​​​​​​​
  • AbhishekB Profile Picture
    AbhishekB on at
    Facing error on converting bacpac to bak in D365 f&o (10.0.39)
    thank you everyone sharing  my experience 
     
    1) while importing bacpac to tier1 ,   both tier1 and  sandbox are on 10.0.39, i face the following two issues 
     
    a)  finTAG_INSTEADOFINSERTTRIGGER,  for this  i  modified the model.xml , searched for this removed the element for this , <Element Type="SqlDmlTrigger" Name="[dbo].[FINTAG_INSTEADOFINSERTTRIGGER]">, saved the model file , the other approach i planned to tag is to drop this trigger in the sandbox , and create a bacpac,  then recreate that trigger in sandbox 
     
    b) after this  started the bacpacimport using the new model file , and i ran into the grant issue , and removed the elements as highlighted in the following post 
     
    current status , the bacimport has been running for  about 14 hours,  will update if it finishes 

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,459 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,783 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans