Skip to main content

Notifications

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)

editSubscribe (3) ShareShare
ReportReport
Posted on by Rubalingam S 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
Categories:
Administration and Setup
Development / Customization / SDK
Install and Upgrade
  • 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 109 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 4,311 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 109 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 
  • Suggested answer
    Jan M. Profile Picture
    Jan M. 3,217 on at
    Facing error on converting bacpac to bak in D365 f&o (10.0.39)
    I also changed the model according Willson but got the same error.
     
    The Tag <Property Name="Permission" Value="1001" /> had the value 1114 in my model.
    Above the same commands exits for ms_db_configreader/ms_db_configwriter with value 1001.

Helpful resources

Quick Links

Take the Community feedback survey!

Answer this brief 15-question survey about your Community experience…

Demystifying Copilot: Service Edition with Sundar Raghavan

Sundar answers more questions about Copilot for Service...

Dynamics 365 Business Central vs Finance and SCM

Take a look at the key differences between Business Central and…

more Community News

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,375 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 223,308 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,140

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans